ComboBox displays blank rows (1 Viewer)

[Kryten107]

I have 2 ComboBoxes that are used to select a record to display on a second form. When a last name is selected in the first box, the second one displays all the available first names for that last name. It all works fine, but I'm trying to remove the duplicate last names (and ideally the first names) from the box.

Everything I've read says that using SELECT DISTINCT should suffice, but I'm having issues with it. This is what I've observed so far:

1) Using a SELECT statement in the RowSource property: displays all names, including duplicates
2) Using a SELECT DISTINCT statement in the RowSource property: displays all names, including duplicates
3) Referencing a SELECT query (SearchLastName) in the RowSource property: displays all names, including duplicates
4) Referencing a SELECT DISTINCT query in the RowSource property: displays blank rows as described in another forum: "The problem is that the text does not appear in the combo box. It's not that it's not returning the records, it is obvious from the length of the vertical scrollbar that it is returning many records, it just doesn't display anything."

I don't have any Format or Input Masks set, so his issue is not my issue, but his description of the symptoms is the same.

I've attached a stripped version of the DB. Any input/suggestions would be greatly appreciated.

 

[Scooterbug]

I was going to take a look...but I dont have Acess 2007. If you could save it as a MDB file, you should get more responses.

 

[Kryten107]

Sorry about that. Tada, mdb.

 

[Scooterbug]

The reason why the SELECT DISTINCT isn't working is because you have the clientID in with the rowsource. Since the ClientID in itself is also distinct, it will show every record where the ClientID is unique.

I think what you want is what is called "Cascading Combo boxes". What you can do is take out the client ID from the last name...then you can use Select Distinct. Use the results of that to set criteria for the first name. You can grab the ClientID off of the FirstName combo box then.


I've reattached the database to illustrate. Note that I commented out the code on the On_Change event of the cboLastName. No need for that with the criteria already set in the rowsource for cboFirstName.