Ability to use union query in form

[Sharon]

In my form, I have created a combo box with the control source as a union
query, shown below. However, when I click on the combo box, it opens up andn
shows the correct information, but it won't let me make a selection.

SELECT tblExpenseReport.ExpenseReportNumber as ReportNumber
FROM tblExpenseReport

UNION SELECT tblTravelExpenseReport.TravelExpenseReportNumber as ReportNumber
FROM tblTravelExpenseReport;

UNION SELECT tblCheckRequest.CheckRequestNum as ReportNumber
FROM tblCheckRequest;

UNION SELECT tblPurchaseOrder.PurchaseOrderNum as ReportNumber
FROM tblPurchaseOrder;

I was then going to do an IF statement something to the affect that IF
another field is equal to "Cash", then just show the ExpenseReportNumbers,
etc.

Maybe I should not be doing it with a union query but just with code? If
MethodofPayment is "Cash" then show only the ExpenseReportNumbers?

 

[Steve Schapel]

Sharon,

The problem with not being able to select an item in the combobox is
unlikely to be because you are using a Union Query as its Row Source
(you did mean Row Source, didn't you, not control source?). It is more
likely to be caused by the setup of the combobox itself. Is its Locked
property set to Yes? Is its Control Source set correctly and is the
Data Type of the field it is bound to correct? Check also the Column
Count, Bound Column, and Column Widths properties. The AllowEdits
property of the form?

Having said that, I notice the rogue ; characters in the Union Query - I
am not sure what effect they will have, but is not right.

As regards the "Cash" situation, I think the easiest way is to do it in
code. On the After Update event of the MethodOfPayment control,
something like this...
If Me.MethodOfPayment = "Cash" Then
Me.YourCombo.RowSource = "SELECT
tblExpenseReport.ExpenseReportNumber FROM tblExpenseReport"
Else
Me.YourCombo.RowSource = "YourUnionQuery"
End IF
Me.YourCombo.Requery

This assumes this combobox is on a single view form, rather than
continuous view.
You will also probably want similar code on the form's Current event.