Union Select with Cascading ComboBoxes

[jw5107]

I'm implenting the classic combo box cascade setup on my form. I need to incorporate an alternative to "Not In List" for adding new records to one of the combo boxes.

In the Row Source of one of the combo boxes I have this: SELECT distinct StatusSheetMstr.StatusNo FROM StatusSheetMstr UNION SELECT "<Add New Status Sheet>" AS Expr1 FROM StatusSheetMstr WHERE (((StatusSheetMstr.FleetID) = Forms!BuildStatusSheet!cboFleet)) GROUP BY StatusSheetMstr.StatusNo;

When the 1st combo box is selected - the second shows ALL of the records instead of the ones based on - StatusSheetMstr.FleetID = Forms!BuildStatusSheet!cboFleet

As soon as I take the - UNION SELECT "<Add New Status Sheet>" AS Expr1 - out of the row source - the cascading works fine between the 2 combo boxes.
When "Add New Status Sheet" is selected from the 2nd combo box - an After Update event runs that prompts the user for a new Status Sheet#.

Any examples or suggestions???
Thanks in advance.
jw5107

 

[PHV]

And this ?
SELECT StatusNo FROM StatusSheetMstr WHERE FleetID=Forms!BuildStatusSheet!cboFleet UNION SELECT "<Add New Status Sheet>" AS Expr1 FROM StatusSheetMstr

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886