Report Using Multiple Subforms from Junction Tables

[KCC47]

I am seeking assistance with problem I am having on a report.

The report will function as a customized letter to customers. The custom data will be entered from a master form: Rejected_Application_Frm.

There are a total of three subforms based on junction tables on this master form. My issue is this: how does this report include multiple items from these junction tables? For instance, if there are two rejection reasons, the letter will need to include the rejection_reason_text for each rejection reason included.

I also need to know how to include multiple vehicles on the letter and multiple applicants on the report.

I have already worked through several issues with the great help of June7 here: https://www.accessforums.net/forms/q...lem-32654.html.

[June7]

Probably need subreports because multiple 'many' tables are involved.

You have all relationships set as INNER joins. So when the query for report was built it adopted INNER joins. Needs to be outer (LEFT or RIGHT) joins, such as: "Include all records from Rejected_Application_Tbl and only those from ..."

Unfortunately, no applicant and rejection records to test with.

[KCC47]

I've created two subreports, and I have added some test data.

I know something is wrong because the report preview is showing all records together despite the fact that I have set the print preview with the proper VBA code to only display current record.

Should the report and subreports be based on queries? I'm uncertain how to set those up really.

[June7]

You haven't set the Master/Child Links properties of the subreport container controls. Need the ID pk/fk fields in each RecordSource.

As already stated, query join types must be changed from INNER, and also remove the Applicant, Vehicle, RejectionReason many tables from the main report RecordSource:
SELECT Rejected_Application_Tbl.*, Dealer_Tbl.Dealer, Dealer_Tbl.Dealer_Number, Office_Tbl.Office, Office_Tbl.Office_Address
FROM Office_Tbl RIGHT JOIN (Dealer_Tbl RIGHT JOIN Rejected_Application_Tbl ON Dealer_Tbl.ID = Rejected_Application_Tbl.Dealer_ID) ON Office_Tbl.ID = Rejected_Application_Tbl.Office_ID;

I don't understand how an application can apply to multiple vehicles but that is what you have setup. Each rejected application can have multiple vehicles, multiple reasons, and multiple applicants. Each 'many' table needs to be in a subreport. Do not include the parent table in the subreport RecordSource.

RecordSource for the Applicant subreport:
SELECT Applicant_Tbl.*, Applicant_Junction_Tbl.Rejected_Application_ID
FROM Applicant_Tbl RIGHT JOIN Applicant_Junction_Tbl ON Applicant_Tbl.ID = Applicant_Junction_Tbl.Applicant_ID;

[KCC47]

You haven't set the Master/Child Links properties of the subreport container controls. Need the ID pk/fk fields in each RecordSource.

I'm afraid I'm very lost here. I have never created a subreport; therefore, I don't know where to change the container controls. Is that in the properties for the report?

As already stated, query join types must be changed from INNER, and also remove the Applicant, Vehicle, RejectionReason many tables from the main report RecordSource:
SELECT Rejected_Application_Tbl.*, Dealer_Tbl.Dealer, Dealer_Tbl.Dealer_Number, Office_Tbl.Office, Office_Tbl.Office_Address
FROM Office_Tbl RIGHT JOIN (Dealer_Tbl RIGHT JOIN Rejected_Application_Tbl ON Dealer_Tbl.ID = Rejected_Application_Tbl.Dealer_ID) ON Office_Tbl.ID = Rejected_Application_Tbl.Office_ID;

Does it matter that the Deputy_Clerk_Tbl is not part of the Record Source here? I have added the Deputy_Clerk_Tbl.Initials field to the report, and it appears to be working. For the most part this seems to be working.

I don't understand how an application can apply to multiple vehicles but that is what you have setup. Each rejected application can have multiple vehicles, multiple reasons, and multiple applicants. Each 'many' table needs to be in a subreport. Do not include the parent table in the subreport RecordSource.

RecordSource for the Applicant subreport:
SELECT Applicant_Tbl.*, Applicant_Junction_Tbl.Rejected_Application_ID
FROM Applicant_Tbl RIGHT JOIN Applicant_Junction_Tbl ON Applicant_Tbl.ID = Applicant_Junction_Tbl.Applicant_ID;

I have attempted to change the Rejection_Reason_SubRpt to reflect this, but it isn't working.

You've provided me instructions on the Applicant_SubRpt, but I've created a query that concatenates the names (F,M,L,S) for the report. How do I incorporate that query into that SubRpt record source?

[June7]

Subforms and subreports are created by placing a subform/subreport container control on another form or report. The SourceObject of a container control can be a table, query, form, report. Master/Child Links are properties of the container control. These properties will synchronize the related parent/child records of the main form and subform. Click once on the subform/subreport and the container is selected and its properties can be viewed. Click again and the object held by the container is selected and can see its properties.

Don't need Deputy_Clerk_Tbl in the RecordSource.

RecordSource for the Applicant subreport:
SELECT Applicant_Junction_Tbl.Rejected_Application_ID, [First_Name] & " " & [MI] & " " & [Last_Name_Company_Name] & " " & [Suffix] AS ApplicantName
FROM Applicant_Tbl RIGHT JOIN Applicant_Junction_Tbl ON Applicant_Tbl.ID = Applicant_Junction_Tbl.Applicant_ID;

Name is a reserved word so use ApplicantName. Change textbox ControlSource to use this.

RecordSource for the Vehicle subreport:
SELECT Vehicle_Tbl.*, Vehicle_Junction_Tbl.Rejected_Application_ID
FROM Vehicle_Tbl RIGHT JOIN Vehicle_Junction_Tbl ON Vehicle_Tbl.VIN = Vehicle_Junction_Tbl.Vehicle_ID;

Now set the Master/Child Links.
Master: ID
Child: Rejected_Application_ID

[KCC47]

All that appears to be working now. That explanation on subforms/subreports and container controls really made the difference.

I believe there is only one question left for the report itself. I need a command button on my master form that will print the current record. I have one that does a print preview, but I also want one that will just print the report. I've tried printing from the print preview, but it wants to print both the records I have in that table. I suppose there is some code to add to such a command button.

[June7]

Try something like:

DoCmd.OpenReport "reportname", , , "ID=" & Me.ID

[KCC47]

I think that covers my issues with the report. Thank you.

I'll post some more issues back in the original form thread.

[KCC47]

I can only assume it is something I have done to cause this, but now that command button wants to ask for a parameter value, the ID. It also prints all the records.

What is causing this, and how do I fix it?

[June7]

My code was an example, need to use actual field and control names from your db. Looks like you want to filter on Rejected_Application_ID. So try:

DoCmd.OpenReport "Rejection_Letter_Rpt", , , "Rejected_Application_ID =" & Me.ID