Same old problem: Multiple Combo Boxes

[tweaked2005]

So I apologize in advance. I know this question continually pops up, but I'm to the point of frustrated and have no idea what to try next. My problem seems simple, and I've read all the postings I could find about the issue, but nothing I seem to try works.

Scenario:
tblCustomers
<customerID>
<FirstName>
<LastName>
<Adress...>
<...more customer info>

I have a form with multiple combo boxes (for now we'll stick to two to be simple). One for first name, and one for second name. So when I initially added these two combo boxes I was able to independently select the info displayed in the rest of the form based on my selection. As others have asked, I want to almost "drill down" the info. So for example, if the user selected "Smith" for a last name, it would only give selections for Smith in the 2nd combo box (first name), and the user could the select the desired first name (displaying that record(s) info in the lower part of the form.

I've tried creating the row source of my combo2 box to query based on the selection in my combo1 box - either I'm getting errors, or the second box is just empty.

I've tried lots of different options (trying to follow the advice in similar post). My latest SQL code is:

SELECT TBLCUSTOMERS.customerId, TBLCUSTOMERS.firstName, TBLCUSTOMERS.lastName
FROM TBLCUSTOMERS
WHERE (((TBLCUSTOMERS.lastName)=[FORMS]![FRMSEARCH]![COMBO1]));

I really don't know where to start. At this point - I'm doubting anything I know about access. Any help at all is greatly appreciated.

Thanks!

 

[Trevil]

Are you requerying combo 2 after selecting item in combo 1 (i.e. in the 'After_Update' event)?

Me.Combo2.Requery



Code: Where the vision is often rudely introduced to reality!

 

[tweaked2005]

Yes, I am Requerying my 2nd combo box in the "After_Update" event.

Sigh.

 

[PHV]

And what is the BoundColumn of COMBO1 ?
Better, what is the SQL code of COMBO1.RowSource ?

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

 

[tweaked2005]

Both bound to 1 - customerID.

The SQL for combo box 1 is:

SELECT tblCustomers.customerId, tblCustomers.firstName FROM tblCustomers;

Thanks for all your help so far. Its giving me hope!

 

[Trevil]

Are you sure about your SQL or is that a typo? I thought combo1 listed LAST names -- not FIRST??

Code: Where the vision is often rudely introduced to reality!

 

[PHV]

So, Combo1 is for selecting firstName and Combo2 is for selecting corresponding lastName ?
In the AfterUpdate event procedure of Combo1:
Me!Combo2.RowSource = "SELECT customerId, firstName, lastName" _
& " FROM tblCustomers WHERE firstName='" & Me!Combo1.Column(1) & "'"

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

 

[tweaked2005]

IT WORKED. YOU ARE AMAZING!

wow, thank you guys SOOOO MUCH for you patience!