A twist on cascading combos (?). THANKS!

[TomH]

OK... here goes, everyone.

Cascading combos... I THINK I can figure out again even though its been a while. Although, I see some using VB code that seems a LOT simpler than the awkward queries and REQUERY command I use.

What I want to be able to do is query on names (in a form called frmMAIN) by EITHER first or last name. I would like to have two combobox controls FIRST and LAST. When I choose a LAST name from the dropdown, then look at the FIRST name list, it shows only those appropriate for that last name. Of course, this is standard cascading.

BUT... I also want to be able to do it the other way around... choose FIRST and then LAST. And, I'd like to be able to go back and forth... SMITH, JOHN, DAVIS, MICHAEL, etc, and have the combos continually updating with appropriate values.

So, I would have a single table called tblNAMES with fields Firstname and Lastname... I think. If someone could help me with the simplest VB code or queries and update code to make this happen, I would be eternally grateful. Thanks all.

Tom

 

[RainLover]

You do this with a Query.

To find the first name The Criteria for the LastName is something like "Forms!myForm!cboLastName"

Then create a new field in the Query with this
"Forms!myForm!cboLastName"
On the second line of the Criteria for this expression place > Is Null <

You end up with this.

SELECT MyTable.PersonID, MyTable.a, Forms!myForm!cboLastName AS Expr1 FROM MyTable WHERE (((MyTable.PersonID)=Forms!myForm!cboLastName)) Or (((Forms!myForm!cboLastName) Is Null));

Now do the reverse for the last Name.