Print a report that has two subreports with multiple criteria

Great Day, I have a form that is used for a repair order at a automobile repair shop. The customer comes in a lays out what is wrong with the car – brakes squeal, gas smell, oil change, etc. When the service writer enters the repair needs into the form (frmJobOrder), there are three categories that the service writer can associate each repair item to: 1. Item part of original estimate; 2. Item part of Repair Order; and 3. Item recommended by technician (Line item 3 results while the technician is doing the repair and then finds additional things that need repair. ( I have included a screen shot of this section of the form).  

After the Repair Order screen has been completed, the service writer can print an invoice by clicking “Print Invoice.” The code behind the print invoice button is enclosed. What I am trying to do is only print items that are part of the repair order, which may or may not be part of the original estimate. I will also eventually want to create a routines that only prints items that are part of the original estimate and one that only prints items that the technician recommended, but I am only asking for assistance on this one piece of the puzzle, and I suspect that perhaps I can figure the other two out.

When I click "Print RepairOrder" all of the items are still printing. It may help to know that the items to be printed in the report are in a subreport. So to be clear, I only want the subreport to display repair items that are part of the repair order.

Thanks in advance for your assistance.

Sincerely,

David

Private Sub btnOpenReport_Click()
On Error GoTo Err_btnOpenReport_Click

    Dim stDocName As String
    Dim stFilter As String
    Dim stDateRange As String
    Dim stLinkCriteria
    
    'set conditions
    stDocName = "rptInvoice2"
‘    stLinkCriteria = "[JobID]=" & Me.JobID
    stLinkCriteria = "[JobID]=" & Me.JobID & " AND [Forms]![frmJob]![frmScopeofJobSub].[Form]![RepairOrder]= '-1'"   ' I am playing with some schemes to see how to print seperately estimate and invoice
    DoCmd.OpenReport ReportName:="rptInvoice2", _
    View:=acViewPreview, _
    WhereCondition:=stLinkCriteria

Exit_btnOpenReport_Click:
    Exit Sub

Err_btnOpenReport_Click:
    MsgBox Err.Description
    Resume Exit_btnOpenReport_Click
    
End Sub

Open in new window

Independent-Auto-Image-of-Repair.png
yddadsjd95Asked:
Who is Participating?
 
Dale FyeConnect With a Mentor Commented:
I forgot that OpenArgs is NULL if it is not passed, not "".

Try this:

    me.subreport1.Visible = NZ(Me.OpenArgs,  "RepairOrder") = "RepairOrder"
    me.subreport2.visible = NZ(me.OpenArgs, "Sub2") = "Sub2"
    me.subreport3.visible = NZ(me.OpenArgs, "Sub3") = "Sub3"
0
 
mbizupCommented:
For filtering the report, you need to use the underlying field in the reports recordsource, not the form control.

This filters repair order to -1:

stLinkCriteria = "[JobID]=" & Me.JobID & " AND [RepairOrder]= '-1'"

This filters it to whatever is on your form:

stLinkCriteria = "[JobID]=" & Me.JobID & " AND [RepairOrder] =  '" & [Forms]![frmJob]![frmScopeofJobSub].[Form]![RepairOrder] & "'"

If these values are numeric:

stLinkCriteria = "[JobID]=" & Me.JobID & " AND [RepairOrder]=  -1"
Or this:
stLinkCriteria = "[JobID]=" & Me.JobID & " AND [RepairOrder] =  " & [Forms]![frmJob]![frmScopeofJobSub].[Form]![RepairOrder]


0
 
Dale FyeCommented:
try changing:

stLinkCriteria = "[JobID]=" & Me.JobID & " AND [Forms]![frmJob]![frmScopeofJobSub].[Form]![RepairOrder]= '-1'"

to:

stLinkCriteria = "[JobID]=" & Me.JobID & " AND [RepairOrder]= -1"

This assumes that the report contains the [RepairOrder] field
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
yddadsjd95Author Commented:
Thank you mbizup and fyed, but I am still obtaining the same results.
0
 
Dale FyeConnect With a Mentor Commented:
My guess is that the problem lies with the subreport.  Change the query that is the recordsource for your subquery so that it only returns records for [RepairOrder] = -1.  Then change the code for strCriteria back to:

stLinkCriteria = "[JobID]=" & Me.JobID

However, I'm not actually certain that you need a subreport.  If you create your query properly, and use the grouping features of Access reports, you can probably get away with a single query and no subreports.

0
 
yddadsjd95Author Commented:
The reason I went with the subreports is because there could be many parts or repair items for each job order, and I kept getting duplicate records, so the subreport seemed to be the only way to remedy the situation. I want to print three types of documents from the same report:

1. Estimates
2. Invoices from work performed in work order
3. recommendations from the technician

If you could show me how to alter the query SQL through VBA, then I should be able to make your suggestive solution work. Although the other reports are not a part of this question, it would be the same application.

Thanks,

David
SELECT tblScopeOfJob.WorkRequestedID, tblScopeOfJob.JobId, tblScopeOfJob.JobTypeID, tblScopeOfJob.WorkRequested, tblScopeOfJob.WorkRequestedCost, tblScopeOfJob.Estimate, tblScopeOfJob.RepairOrder, tblScopeOfJob.Recommended, tblScopeOfJob.RemindInDays, tblScopeOfJob.RemindSentDate
FROM tblScopeOfJob;

Open in new window

0
 
Dale FyeCommented:
Did you try setting the criteria on the subforms recordsource as I recommended above?

If you could provide a sample database that contains one or two repair orders and all of the associated data for those repairs, and a sample of what you would like the report to look like, it would be difficult to give you a recommendation.
0
 
yddadsjd95Author Commented:
Yes I tried it before I sent the question, and it works for that one report, but it won't work when I want to print the other two reports - Reports # 1 and 3 above in my previous response. Is there a way to alter the SQL through VBA to do the same thing? In the past, I've created three separate report to handle those three separate reports where I would set the criteria in the query, but that isn't an efficient way to approach to it.

Part of this exercise is to improve my own limited skills and I thank you for your tutorial lessons here.

Thanks,

David
0
 
Dale FyeCommented:
The way I would do it is to copy the subreport and save it two more times, once for each category.  Then modify and save the associated query so the you have 3 separte subreports and 3 separate queries.

Then you could stack the 3 subreports in the details section of a single main report.
0
 
yddadsjd95Author Commented:
That sounds like a winner! How do I call, let's say, the report for the invoice (Repair Order)? The stLinkCriteria is stLinkCriteria = "[JobID]=" & Me.JobID. How would I modify this line to open the subreport that I am looking for?
0
 
Dale FyeCommented:
You cannot, unless the recordsource for the main report contains the appropriate field(s) like RepairOrder, from your original post, which is unlikely.

You could however, use the OpenArgs option for the OpenReport method, like:

docmd.OpenReport "reportname", acViewPreview, , strCriteria, , "RepairOrder"

 and in the Report_Open event do something like:

Private sub Report_Open

    me.subreport1.Visible = (Me.OpenArgs = "") OR (me.OpenArgs = "RepairOrder")
    me.subreport2.visible = (me.OpenArgs = "") OR (me.OpenArgs = "Sub2")
    me.subreport3.Visible = (me.OpenArgs = "") OR (me.OpenArgs = "Sub3")

End Sub

This would by default display all of the subreports, but if you specified an OpenArgs value, then it would hide all but that subreport.
0
 
yddadsjd95Author Commented:
I received an error that said "Invalid use of null" but strangely the next time I ran it, it appeared to run ok. I'll place the other command buttons on it and keep you posted.
0
 
yddadsjd95Author Commented:
Thank you fyed! It works like a charm. I really appreciate your assistance on this.

Have a great rest of the day and an even finer day tomorrow.

Sincerely,

David
0
All Courses

From novice to tech pro — start learning today.