sub report not working correctly

[Hap007]

Hi,

I have an Access Main report with an Access Sub report

The sub report has detail items that I want to display for Leasers.

I have the sub report Link fields defined:
LeaserID
EquipmentID

For the first record in the main report, the sub report fires up and prints correctly

For every additional Record in the Main Report, the sub report comes up blank.

I have done this many times before and had no problem, but this time, well I am stumped!!!

Any idea why the first record in the main Report shows the corrrect details in the sub report, but for every additional main report detail record, the sub report is blank?

Thanks,
Hap...


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[dhookom]

Your link master/child doesn't make sense to me. Are these actually a primary and foreign key? It's a bit difficult to troubleshoot this without understanding your table structures.

Duane
Hook'D on Access
MS Access MVP

 

[Hap007]

Hi Duane,

Thanks for getting back to me.

The Master report is listing Leasers and their equipment.
The master report is in Leaser and then Equipment Sequence

For each Leaser/Equipment record, I link a sub report that displays Expenses for the Truck(Equipment), so the link to the sub report is LeaserID and EquipmentID.

Does that describe the relationship?

For the First LeaserID, the sub report fires correctly for each EquipmentID, but when the leaserID changes, the sub report then returns zero details.

It does not matter what the first Leaser is, the report works correctly, but when the LeaserID Changes, it then stops working:

LeaserID EquipmentID
0100 2041
0100 2062
0100 3045
0240 1010 Subreport stops here
0240 2031
0300 2025
0300 2026

Thanks,
Hap...


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[dhookom]

If EquipmentID is unique then you should not need to include the LeaserID in the link.

Can we assume you are trying to display records from three primary tables:
Leases
Equipment
Expenses
If this is the case, then the main report would have a record source that includes Leases and Equipment. The subreport would contain only Expenses. The linking field would be EquipmentID.

You could also consider the main report being based on just the Leases with the subreport containing Equipment and Expenses. The linking field would be LeaserID.


Duane
Hook'D on Access
MS Access MVP

 

[Hap007]

Hi Duane,

Thanks for getting back.

I appreciate the information, but the problem is:
1) The Main Report and the Sub Report were working fine
2) Made some changes to the report, no problem
3) Made some changes to the queries, no problem
4) Then, some where along the way, the sub report stops working for all but the first Leaser Record, sub report works for first main report record, but no others.
5) I finally made a new (main)report, copied and pasted all the items from the original report to the new report and now the new report/sub report works.

Bottom line, somewhere in making changes to the original main report/sub report, the sub report broke and I do not know why.

It almost seems to be a bug in MS Access.

Thanks,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[Hap007]

Hi,

I have fixed this problem and thought I would check back and let anyone interested know how.

I had a report that included several sub reports that worked fine.

It would run for all leaseors or I could just past a single leasor and that would work.

Some where during development, the report stopped working. It would work fine for one leasor (Showed main report and all sub reports) but if I selected 'All', only the first leasor would have the correct detail and sub report detail. For all remaining Leasors, the main report printed fine, but the sub reports were blank.

Seems that I must have changed a query after developing the original report.

The only solution, was to create a brand new main report,(called some thing different then the problem report). Then I just copied all of the subreports to the new main report and it again works like a champ.

Seems that if you have a main report and a sub report, if you alter the query of the sub report, you might create a problem.

Just wanted to share my findings.

Have a Great Day,
Hap...


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121