Union Query and Combo Box Synchronization

[Sharon]

I have two combo boxes:
the first is the cboReportType
the second is cboReportNumber

The first is based on a table with different types of reports: check
request, expense reimbursement request, travel reimbursement request,
purchase order request

The second combo box is based on a union query containing all report
numbers, i.e., expense report number, travel report number, check request
number, purchase order request number.

When I select the first combo box value, cboReportType, I want only the
records pertaining to that type of report to populate the second combo box.
I tried to modify the code that I found from Graham Seach. Can someone tell
me how to do this using a union query rather than a table? Or tell me what
else might be wrong with my code? Thanks.


Private Sub cboReportTypeName_AfterUpdate()
Dim sSQL As String

'This function sets the RowSource of cboReportNumber, based on the
'value selected in cboReportTypeName.
sSQL = "SELECT ReportNumber " _
& " FROM qryAllReports WHERE ReportNumber = " & Me.cboReportTypeName _
& " ORDER BY ReportNumber"
Me.cboReportNumber.RowSource = sSQL

 

[Klatuu]

This line is suspect:
FROM qryAllReports WHERE ReportNumber = " & Me.cboReportTypeName

Shouldn't you be filtering on ReportTypeName rather than ReportNumber?
if qryAllReports doesn't have a ReportTypeName field, it needs to.

I believe the base problem is it sounds like you have a table of report
numbers for each report type. This is not a good idea. As you can see, it
only makes your life harder. It would be better to combine that tables into
one talbe that has a reportype field. It would be easy to filter on that
field.

If you multiple report number tables don't have a field to tell you what the
type is, you can create a calculated field in your Union query that will
resovle that:

Select "CheckRequest" As ReportType, ReportNumber FROM SomeTable
UNION ALL
Select "ExpenseReimbursement" As ReportType, ReportNumber FROM AnotherTable

And so on.