Attempting to open report to specific record but it keeps showing all records - sort of?

Question

I have a report with several sub-reports which are all extract data from the same table. The only reason I made them sub-reports is for size constraints in using Access to create reports (21 inch issue). I have form (frmService) that is loaded from another form (frmWorkOrders but that doesn't really matter I don't think) and it shows a single record in tblServiceRecord. The form opens correctly, operates correctly to the same single record, however on frmService I have a control (button) that opens the report (rptServiceRecord) so we can print the record for our customers. Anyway, the report ALWAYS brings up SOME of every record but not all parts of the multiple records.

I have several sub-reports (srptFirstStage, srptSecondStage, etc.) and the only two sub-reports that print every record are the two listed above. All other sub-reports only print the associated record that I want it to print, even if there are multiple records in the database. The weirdest part, and the part that's keeping me from solving this, is that all of the sub-reports were created equally. The report itself has a filter (see code below) but none of the sub-reports have this filter in them. I also have Filter On Load checked as Yes. I also tried to use a query to create this report using the following SQL:

SELECT tblServiceRecord.*, tblWorkOrder.Task, [FName] & " " & [LName] AS FullName, "WD0" & [tblWorkOrder]![ID] AS WorkOrder
FROM tblServiceRecord INNER JOIN (tblCustomers RIGHT JOIN tblWorkOrder ON tblCustomers.ID = tblWorkOrder.CustomerID) ON tblServiceRecord.WorkOrderID = tblWorkOrder.ID
WHERE (((tblServiceRecord.WorkOrderID)=[Forms]![frmService]![txtWO]));

Bringing up the query in Datasheet view brings up the correct information and doesn't list multiple rows from the tables. So I'm not entirely sure why the report does show multiple rows, even though the second rows srptFirstStage and srptSecondStage are showing are blank.

To open the report (rptServiceRecord) from the control I use:

Private Sub Command275_Click()
On Error GoTo Command275_Click_Err

    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.OpenReport "rptServiceRecord", acViewPreview, , "[WorkOrderID] = Forms.[frmService].[txtWO]", acNormal


Command275_Click_Exit:
    Exit Sub

Command275_Click_Err:
    MsgBox Error$
    Resume Command275_Click_Exit

End Sub

Unhandled Exception Unhandled Exception · Accepted Answer · 2019-01-24T17:27:06

Try this in case WorkOrderID is a numeric field:

DoCmd.OpenReport "rptServiceRecord", acViewPreview, , "[WorkOrderID] = " & Forms("frmService")("txtWO").Value, acNormal

Try this in case WorkOrderID is a string field:

DoCmd.OpenReport "rptServiceRecord", acViewPreview, , "[WorkOrderID] = '" & Forms("frmService")("txtWO").Value & "'", acNormal

Attempting to open report to specific record but it keeps showing all records - sort of?

1 Answers