Report creating multiple pages for the same ID

[cvandijk]

Good Afternoon All,

I am trying to wrap up this application I am creating for one of our departments and I can't seem to figure out this last part of it.

I have a form where complaints can be filed, and each of these complaints can have multiple violations related to them. In the table structure I have a complaint table where each complaint is assigned a complaintID. This complaintID is also the foreign key in the violation table.

On the form, I have a button to create a compliance order based on the record the user is currently on. The report opens up with the appropriate filter and the subreport pulls all the violations associated with that record. However, since there are two or more instances of that specific foreign key in the violation table, there are multiple requests being created for each complaint. I need to be able to narrow this down to a single page that culminates all of that information.

Any help that anyone could provide on this problem would be greatly appreciated.

Thanks,

Chris
Analyst / Programmer

 

[dhookom]

Can you describe:
1) the record source of your main report

2) the record source of your subreport

3) the link master/child properties of your subreport control

If your main report is displaying too much duplicate information I expect you have violation information in your main report's record source as well as the subreport. If this is the case, I think you need to remove the violations from the main report.

Duane
Hook'D on Access
MS Access MVP

 

[cvandijk]

The record source of the main report is the data from the complaint table. The record source of the subreport is the data from the violation table.

The master/child properties are simply ComplaintID...meaning that the complaint ID that the report is based on will pull violations from the violation table that have that complaintID attached to them.

 

[dhookom]

Can you define "there are multiple requests being created"? What is a request?

If you add a text box to your main report Header section with a control source of:
=Count(*)
what gets displayed in the text box?

Are you suggesting the Record Source property of the main report is simply;
tblComplaints
or
SELECT * FROM tblComplaints
or similar?

If you set the background color of the main report to yellow, are you seeing duplicates in the yellow or in the subreport?

Duane
Hook'D on Access
MS Access MVP

 

[cvandijk]

What I mean by multiple requests is that I am getting two reports with the same data on them. Since I have the ComplaintID in two places in the violation table, the report thinks that it is pulling everything with the ID specified by the form.

If I do what you suggested with the count property, I get the number 2 when I try to print a summary report for one record.

The record source of the main report is select fields from the complaint table, such as complaint date, owner name and so on.

If I set the background to yellow, the entire report, even the extra pages, are set to yellow with the subreport staying normal white.

Thanks for your help...I appreciate your diligence.

 

[dhookom]

Please give us something we can trouble shoot rather than [tt][red]"The record source of the main report is select fields from the complaint table, such as complaint date, owner name and so on."[/red][/tt]

I really think this is the source of your problem but you haven't provide the SQL view of your report's record source.

Duane
Hook'D on Access
MS Access MVP