Cascading List boxes help (1 Viewer)

[cjmitton]

I have a form where I need to select a business from first listbox then display the contacts for the business in the second listbox.

I've been looking at cascading listboxes / combo boxes but everything I've found so far does not fit my requirements as its usually based around a single field being displayed in a list / combo.

My senario is this:

My first listbox displays:
Business Name, Address line 1, city, business type
then hidden is the unique key (ID) for that business.

This is done by using a query as its row source and display all the relevent business I require.

My second Listbox displays:
Full name, Job title
then hidden is a unique key (ID) .

When one of the businesses is selected all the contacts that have the business ID assigned to them need to be displayed in the second list box.

I've created a Query (for the second listbox) and works when I manually give the query a business ID (to filter the contacts) but cannot seem to get the query to use the 'first listbox' selection.

I've tried to build the critieria in the query and can get as far as selecting the first list but do not know how to get it to select the correct column/field with the ID in?

I have a 'requery' procedure for the 'second listbox' on the event 'after update' (I've also tried 'on click' too) to try and run the query in the second listbox.

 

[John Big Booty]

Check out the article here, whilst it directly relates to a cascading combo box set the principal is exactly the same for your List boxes.

 

[cjmitton]

The example you suggested uses a references in a field on a form, not a field in another listbox (which is where I'm struggling). How do I get the query to look at the ID field in my listbox? can I reference the field directectly using its field name or can I reference it using a column number?

I've tried starting my reference like this in my query where [List_All_3rd_P_Bus] is the listbox.
[Forms]![frm_Matter_Add_Assign_3rd_P_Bus_and_Cont]![List_All_3rd_P_Bus]

The ID field in the query thats run by that list box is: B_3rdPartyBusinessID

Thanks

 

[John Big Booty]

I think that you will find that if you use the following criteria in the query populating your cascaded combo it will work;

[Forms]![YourFormName]![YourListBoxName]

The principals explained in that link are still valid.

 

[John Big Booty]

Here's a small sample that demonstrates the principal

 

[cjmitton]

Thanks John, I don't know that if its something I've done but your example did not work. I've attached the relevent part of my DB for you (or anyone else) to look at so you can see where I'm going wrong!

Thanks

Colin

 

[John Big Booty]

Sorry I don't have Access '10, so you're going to need to save that back to '07 at the very least.

 

[cjmitton]

Darn it! It wont allow me to back save to a older verison... I'll try to find what stopping it!

 

[cjmitton]

Having looked at your example against mine, I've spotted where I went wrong! I got my bound Column's incorrect!

As soon as I sorted that part out it went fine!

Thanks for your help

 

[John Big Booty]

Glad you got it sorted