Microsoft Access List Boxes and Sublist Boxes

Question

I am trying to create a sub-list box that is controlled by the list box.  In other words in my example below, in my list box I select Bank of America and to the right the address appears and in the sub-list box the of Bank of America branches appears.  But, my sub-list box does NOT change when I select a different bank from my list box.  So, for example when I select Wells Fargo or the sub list box doesn't change to list all the Wells Fargo branches.  The Bank of America branches remains. (Please see picture below) .   I hope this makes sense.  Thank you in advance.

Answer 1

Do not call any phone number posted in these Answer groups.

Your requirement is basic cascading combo/list boxes. You need to requery the address list box after updating the bank list box. This can be done with a little vba code in the After Update event of the bank list box:

Me.[Address List Box Name].Requery

If the Bank list box is bound to a field in the form's record source, you may also want to requery in the On Current event of the form.

Answer 2

Sorry, I'm somewhat a beginner in MSAccess and I not sully sure what you are telling me.  Do I need to build queries?  What I have done is create a relationship between two tables.  But the second list box (sublist box) doesn't respond.

Thank you very much for your response.

Steve Gates

Answer 3

Take a look at the following detailed example.

https://utteraccess.com/wiki/Cascading_Combo_Boxes

Answer 4

Hi Steve, I'm a bit confused by what you have there. You appear to have 15 records so the form is obviously bound to a table. When you click on the main list box it changes the bank name, address and sub list box of branches. It doesn't make sense (to me) to have it bound.

What I would do is set up your tables, you woul need at least a couple

One table should have BankNameID (autonumber) and bankName (text)

A second table should have the BankNameID, BankCity, BankAddress, webaddress etc and I would add a code to differentiate main address and branch address.

I would then have two queries, the first containing bank details criteria as the code to pick up the main address

Then a second query with the same details and criteria as code to pick up the branch address.

Both queries would also have criteria of the list box selected ID

I would then create an unbound form with both listboxes and fields as you have.

In the onClick event of the list box I would have something like:

Forms![frmBank].RecordSource = "qryBankMain" which would populate the form with the bank name, web address, main address details etc.

The onClick event would also have something like:

Forms![frmBank]![lstBankSub].RowSource = "qryBankNameSub"

This would populate the sub list box with the branch address details.

Another option is to have a bound form to your bank table... and a linked subform to show branch details.