Comboboxes blank when form recordset has no records

[Jman]

I have a series of three comboboxes on a form that let the user narrow down their record selection criteria on a table of invoices: 1) cboClientID, 2) cboProjectID, 3) cboVendorName, in that order. In the AfterUpdate sub for cboClientID, I set the other two comboboxes to display null and then set the form's recordsource to "SELECT * FROM tblInvoices WHERE ClientID=0" in order to display no records until a ProjectID has been entered. This works just fine in Access 2003, but in Access 2007 it blanks out the values selected in the comboboxes so that they appear to be empty when in fact they are not. This is rather disconcerting to the users, to say the least.

Is there a better way to do what I'm trying to do or is there a way to work around this in 2007?

[June7]

Interesting, I would have expected the same behavior for 2003.

I presume the comboboxes are multi-column with a lookup alias. This means when the combobox is filtered, lookup values are not available for display.

Is the form in continuous or datasheet view and multiple records are visible at a time? I have dealt with numerous threads on this same issue and have yet to discover a satisfactory solution for situation where multiple records are viewed.

Might have to use an unbound combobox and code to populate it with the field value and code to save selected value.

[John_G]

Hi -

That suggests the combo boxes are bound to table/query fields. What you might try is to have all the combo boxes unbound, and set the form record source only when you make a selection from cboProjectID. The other thing you might try is to change the form's filters according to your selections, instead of changing the recordsource.

John