asked on

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

mbizup

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]

Dale Fye

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

yddadsjd95

ASKER

Thank you mbizup and fyed, but I am still obtaining the same results.

SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

yddadsjd95

ASKER

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

Dale Fye

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.

yddadsjd95

ASKER

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

Dale Fye

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.

yddadsjd95

ASKER

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?

Dale Fye

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.

yddadsjd95

ASKER

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.

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

yddadsjd95

ASKER

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