ACCESS FEED: Re: [MS_AccessPros] Re: Report based on Crosstab query

John,

First of sorry to say that i don't know the proper way to make you clear, as i myself don't know the proper way to answer your questions. Let me tell you step wise what i did.

There is Folder in drive D: named PCTL, I made PCTL database first, then from Tools->Databas Utilities->Database Splitter made its file PCTL_be (i assume this is called Back end.

PCTL.mdb is linked with PCTL_be

PCTL_be has only tables, and Relationships are defined in it.

All Modules, Queries, Forms & Reports are in PCTL.mdb

This is why I do all coding in PCTL.mdb, each time when new Form or Report is made or edit coding I make PCTL.mde file from PCTL.mdb

I am the only user for it. I have made a folder in Drive D:\PCTL of my boss and copied there PCTL_be and PCTL.mde. I do overwrite PCTL_be on my boss computer so that he can view updated reports.

This is all what i do. I don't know if i have answered your all questions.

Regards,

Khalid

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

I thought I answered all your questions. But you didn't answer mine:

1) Is PCTL.mdb a "front end" that links to PCTL_be.mdb ??

2) Is all your data in PCTL_be.mdb ??

You sent us your data to allow us to come up with solutions. I assume your actual data may have changed since then. I listed all the objects that Duane and I built in the uploaded database - you simply need to import those into whatever database your user opens to run the application.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Feb 26, 2015, at 2:02 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
What about my other concerns and questions?

Regards,
Khalid

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Forget the comment about mis-aligned labels and text boxes. They're fine.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Feb 26, 2015, at 1:54 PM, John Viescas JohnV@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Khalid-

I assume _be has all your data in it. Is PCTL.mdb a front-end using linked tables to PCTL_be?? If so, you should not mess with your existing back end. Rather, you should import these objects into PCTL.mdb:

ztblKhalidReport
qryKhalid
qryKhalid_DH
frmRunCrosstab
frmRunCrosstabJV
rptConsignmentCrosstab
rptKhalidMT
srptConsignmentNumberHeadings
Module1

Yes, you should be able to add totals for all the columns.

Note that I didn't do a good job lining up the Fldxx text boxes under their labels in rptKhalidMT, but that should be easy to fix.

In Access 2003, choose Tools / Options / General and clear all the boxes under Name AutoCorrect.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Feb 26, 2015, at 1:24 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
Thank you very much!

I have checked both reports. Few more questions before proceeding further.

1- Should i delete my existing PCTL_be ?
2- Rename your PCTL_be_JV to PCTL_be and put in the folder PCTL for path ? (During this period i've made few new entries in my db, which i have saved exporting those tables in Excel and shall re-enter them.)
3- What to do with my existing PCTL.mdb ?
4- I will be using Form "frmRunCrosstabJV" and its report, and i am sure i would be able to modify Report Header and Report Footer adding text boxes for Sum under Fld01 to Fld10 ?

For future where can i see and check whether "Track Name AutoCorrect and Perform Name AutoCorrect turned" are turned off.

Regards,
Khalid

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

The sample file was badly corrupted as a result of leaving Track Name AutoCorrect and Perform Name AutoCorrect turned on. I rebuilt the database and fixed both my and Duane's samples per your latest requirements.

Watch for the upload notification.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Feb 26, 2015, at 9:28 AM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
This has become hectic for me, again when i put a text box on the report header and set its Control Source to:

=[Forms]![frmRunCrosstab]![cboYear]

On opening the report same thing happened i run into a bug in Access 2003, which i mentioned earlier as CRASHED.

I am now thinking to delete this report and all its relevant code tables/query and form. I want to have a go with your report "rptKhalidMT".

Now you may please check that it was not showing ConsignmentNo 2014-B-08 for ExportDocs 15/01/15, which you rectified in Duane's query.

I'm not going to re-name any thing from your db PCTL_be_John. Also i am sure in your report i will not face any problems changing the widths of columns & rows. You will please also tell the Control Source for the text box on Report Header to get Year of report.

While reading open event of your report, does the following line of code means that input box is generated automatically with this code, as i can not see any form in your PCTL_be_John?
' Prompt for the year filter
strYear = InputBox("Enter year (4 digits) to filter:", "2012")

Hope you would take the pain again to resolve my issue, as i have already told my boss and shown him earlier version of Duane's report, in which he said to add some controls.

Regards,
Khalid

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

Add a text box to the page header of the main report and set its Control Source to:

=[Forms]![frmRunCrosstab]![cboYear]

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Feb 25, 2015, at 5:08 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
I've deleted both sub report and report. Then re-named their copies to original report names.
I kept the original settings of sub report and report. Only changed/reduced the widths of other controls:
ClientCIN
ClientName
Total Of CartonNo
Total Weight

I am not getting any error up to now.

Now if you could see to get Report Year on Report Header, the Year which is selected from the form "frmRunCrosstab"

Once again thanks for being in touch giving your kind advise.

Regards,
Khalid

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

Sounds like you've run into a bug in Access 2003. You could try fixing the Column Size in the subreport to see if it still occurs. If it is still breaking, create a new database and import all the objects from the old one.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Feb 25, 2015, at 2:31 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
When i run the form "frmRunCrossTab" and clicking on the command button "cmdRunXTabReport" to open the report, i get Microsoft Access Office message "Microsoft Access Office has Stopped working and below that the typical lines
-> Check online for a solution and close the program
-> Close the program

In details following are the lines:
Problem event name: APPCRASH
Application Name: MSACCESS.EXE
Application Version: 11.0.5614.0
Application Timestamp: 3f3c8e3c
Fault Module Name: MSACCESS.EXE
Fault Module Version: 11.0.5614.0
Fault Module Timestamp: 3f3c8e3c
Exception Code: c0000005
Exception Offset: 0002e657
OS Version: 6.1.7601.2.1.0.256.48
Locale ID: 2057

Additional information about the program
LCID 1033
Brand: Office11Crash
skulcid 1033

I dont know i had to give this information or not, but this is all. I have not re-named any form, query and report name.

Regards,
Khalid

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

Please explain what you mean by "crashed". What was the error message? Was there an error in VBA?

If you change the width of the text boxes in the subreport, then you have to also change the Column Size in Page Setup / Columns to match your new width.