Search code examples
vbams-accessreport

Listbox Double-Click to open Report - always shows no records

I have a listbox that uses two different tables to get the list data (tblWorkOrder & tblCustomers). The data itself is all from tblCustomers but tblWorkOrder is in there just to filter out any items of the list that don't have an entry in tblWorkOrder. Here is the SQL just in case:

SELECT DISTINCT tblCustomers.ID, [FName] & " " & [LName] AS FullName
FROM tblCustomers RIGHT JOIN tblWorkOrder ON tblCustomers.ID = tblWorkOrder.CustomerID;

The event procedure for the double-click is here:

Private Sub lstCustomers_DblClick(Cancel As Integer)
DoCmd.OpenReport "rptCustomers", acViewPreview, , "[tblWorkOrder].[ID] = " & Forms("frmReports")("lstCustomers").Value, acNormal
End Sub

The original issue I had was that the field [ID] was referencing two tables and could have meant either one. So I added [tblWorkOrder] to delineate which table it was referring for the report. I'm pretty sure this is where I've messed this up, but being so bad at referencing form controls, I'm not entirely sure how to reference it.

EDIT: Here is the SQL for rptCustomers:

SELECT tblWorkOrder.Task, [FName] & [LName] AS FullName, tblCustomers.Company, tblCustomers.Email, tblCustomers.ContactPhone, tblCustomers.Address, [City] & ", " & [State] AS CityState, tblCustomers.ZipCode, tblCustomers.Country, tblCustomers.Notes, tblCustomers.ID, "WD0" & [tblWorkOrder]![ID] AS WONumber, tblWorkOrder.ID
FROM tblCustomers RIGHT JOIN tblWorkOrder ON tblCustomers.[ID] = tblWorkOrder.[CustomerID];
Solution

  • Nothing wrong with the control reference, however wrong field is referenced. Listbox BoundColumn is tblCustomers.ID yet the report filter criteria references tblWorkOrder.ID. Should be tblCustomers.ID or if the report RecordSource includes tblWorkOrder and CustomerID field, could use tblWorkOrder.CustomerID or just CustomerID.

    The listbox reference could be simpler: Me.lstCustomers

    "tblCustomers.ID = " & Me.lstCustomers

    Although it does seem odd that there is never a match. A tblWorkOrder.ID of 2 should still match with a lstCustomers value of 2, just would not be the desired records.