Report Only generates 2 pages when should do >3

[djb79]

Hi All,
I've got a Recordset that stores a summary (no duplicates) of a field in a query.



This particulary field gives the index for the query/report that needs to be called, ie:
1. I have a number of records that are of interest (pulled from the dB from a query)
2. Each record in dB has a type, this type is the trigger for the query/report that needs to be run for it
3. There are 16 types
4. The recordset may not contain all 16 types.
5. I created a summary of which types are in the recordset
6. I only want to call the relevant query/reports
7. I have done this with a case statement

The issue is where the call is made to open the query and then open the report(in printpreview), I ony get the first 2 records from the query appearing in the report:


iDisplayType = 2
.
.
.
.
.
Case Is = 17
DoCmd.OpenQuery ("17 - General AO")
DoCmd.OpenReport "17 - General AO", iDisplayType

Can someone please let me know what is going on and why I'm only getting the first 2 elements

[June7]

That project does not have a query or report named "17 - General AO". How can I analyze object that doesn't exist? In fact, only two of the queries and reports referenced in that procedure are in the project.

Is the report's RecordSource the query? No need to open query before running report.

[djb79]

The way the system works is you copy the ExtTag's for the items that you want to generate reports for into the table titles "InstSelectTable" then click the GenChkSht button.

The dB that I've uploaded has the fault but is a highly cut down version so as to reach the file limit, hence the missing reports and queries, the code should work though as the CommShtInd field in Term has been populated with only the existing report types.

For your conveniece, I've deleted the invalid report types from this attached version.

Regards
Darryl

[June7]

Even after running Compact & Repair, zip file still larger than allowed 2mb?

You are saying the issue is not specific to report "17 - General AO"?

You have all the data controls in the Page header section instead of Detail section. Try moving to Detail. Does that give the result you want?

Why do you want the queries to open?

[djb79]

The details are in the header, as I want each record in the query to bre printed on a seperate page-set; hence the outcome won't work for me.

Each record in the query should generate a seperate 1 page checksheet. I have removed the contents of the body of the checksheet (report) to save space. I had this working on Acc 2003 and recently switched over to Acc2010.

The original dB is ~60MB and has an additional ~20 queries and ~10 tables and ~25 reports with total entries of ~2000 records. in each table.

The error happens on all report types. If I run the VBA code with breakpoints on the line that calls the report then all records from the query get generated.

[June7]

Sorry, posted to wrong thread.

[djb79]

Bump,
Can anyone help with this problem?

Cheers
Darryl

[June7]

Your table/query structure has me confused. Three tables have ExtTag as primary key, they all have 15 records. One-to-One relationship? Why not one table?

TagRefQuery and EquipRefQuery have the same tables with InstIndex and CommissioningStatusTable linked on ExtTag primary keys. Alloc has ExtTag as PK but it is not linked to either of the other two. I don't see any filtering in either query, yet TagRefQuery shows 14 records and EquipRefQuery shows none. The only difference between the two queries is the join between InstSelectTable and Alloc. I think it is wrong in EquipRefQuery. Fix it and will show 14 records.

Then you UNION those two queries in CommRefQuery which shows 14 records. Then CommChkShtGenQuery selects from CommRefQuery, again 14 records. Then 01-DOL LV Motor selects from CommRefQuery with criteria under CommShtInd of "1", and this query is the RecordSource of report.

I think you need to simply. Why are you doing two identical queries then UNION them?

Still can't find why only 2 records show. Something is happening in the GenChkShtButton_Click procedure. Have you step debugged?

[djb79]

Your table/query structure has me confused. Three tables have ExtTag as primary key, they all have 15 records. One-to-One relationship? Why not one table?

Legacy issue as initial dB design was done from Excel spreadsheets. Also the following tables in full dimesions have the following ~number of columns:
15 = Alloc
30 = Term
15 = Inst Index
15 = CommissioningStatusTable

Although there are primary keys across all 3 tables, there is also no gaurantee that all tables will be populated for a given ExtTag due to the nature of what is being represented.

TagRefQuery and EquipRefQuery have the same tables with InstIndex and CommissioningStatusTable linked on ExtTag primary keys. Alloc has ExtTag as PK but it is not linked to either of the other two. I don't see any filtering in either query, yet TagRefQuery shows 14 records and EquipRefQuery shows none. The only difference between the two queries is the join between InstSelectTable and Alloc. I think it is wrong in EquipRefQuery. Fix it and will show 14 records.

TagRefQuery works on ExtTags being inserted into InstSelectTable, whereas EquipRefQuery works on PTermRs being inserted into IntSelectTable, allowing you to either pull out logical groups or specific items. The situation you describe is correct.

Then you UNION those two queries in CommRefQuery which shows 14 records. Then CommChkShtGenQuery selects from CommRefQuery, again 14 records. Then 01-DOL LV Motor selects from CommRefQuery with criteria under CommShtInd of "1", and this query is the RecordSource of report.

The funtionality you described is what is desired once you understand the link with PTermR.

I think you need to simply. Why are you doing two identical queries then UNION them?

Refer Above

Still can't find why only 2 records show. Something is happening in the GenChkShtButton_Click procedure. Have you step debugged?

This is where it begins to explode my mind. When I step through it line by line, all the records print as they should, whereas, If I run it normally only the first 2.

I have also coded the Report's source queries so that they draw straight from the Tables and I still have the same issue.

I have also put message boxes immediately before the OpenReport call (the same line as to where the break points are set) and this still doesn't work. If there is an interlock, then is there a pause call that I can make.

I have no idea as to how to move forward as putting break points in the code and having to step throug the code is not a long term solution.

If you have any ideas please share them. This is not working properly.

Regards
Darryl

[June7]

I think the problem is with the procedure that updates the CommDocCirc field in CommissioningStatusTable. Put a breakpoint on Do While Not rsCommChkShtGenQuery.EOF to suspend code. The reports will open with all records. This code is running and changing the data before the reports are fully rendered. Remove the breakpoint and the problem recurs. Need to either put this code in another event (button Click or report Close) or put a pause in the code. You can Google that topic or see if this example from my project works for you. I tested and it did make a difference with this data sample. How long the wait needs to be for any possible dataset, I can't say.

    Dim WAIT As Double
    WAIT = Timer
    While Timer < WAIT + 30
        DoEvents  'do nothing
    Wend