filling a combo box based on a value of another combo box

i have 2 tables.
Bank (bankid, bankName)
Branch (BankID, BranchID, BranchName)
(here BankID and BranchID are composite primary keys)
in the form i'm hoping to have 2 combo boxes. the first combo box contains all the banks.
the 2nd combo box should get filled according to the output of the 1st Combo Box how can i code this scenario ?

Look at "Demo2ComboA2002.mdb" (attachment, zip).
Open form and see.

What will be in the 2nd combo box? Do you mean you want the list for the 2nd combo box to be filtered by the 1st combo box?

IE - in the first combo box you would select the bank name, then in the 2nd box you would choose from a list of all the branches that belong to that bank?

If that's the case, you could:

For the "row source" of the 1st combo box, enter:

"SELECT Bank.bankid, Bank.bankName FROM Bank"

Then, for the 2nd combo box:

"SELECT Branch.BankID, Branch.BranchName FROM Branch WHERE Branch.BankID = Forms!FormName!Combobox1 OR Forms!FormName!Combobox1 Is Null"

On both combo boxes, make sure the "bound column" = 1 (assuming you want to store the id in your table and not the long name), the "column count" = 2 and the "column widths" = 0";(whatever width you want for the 2nd column)

This way, the actual bank/branch names will be displayed in the boxes, but the id will be stored in your table.

Then create an event procedure on the "after update" event of combobox1 to refresh the form...which is as simple as:

Me.Refresh

i tried
SELECT [Bank Branches].BranchID, [Bank Branches].BranchName FROM Banks INNER JOIN [Bank Branches] ON Banks.BankID = [Bank Branches].BankID WHERE (((Banks.BankName)=[Forms]![CBO]![BanksComboBox])) ORDER BY [Bank Branches].BranchID;

but the 2nd combobox doesnt show values

then i tried
SELECT [Bank Branches].BranchID, [Bank Branches].BranchName FROM Banks INNER JOIN [Bank Branches] ON Banks.BankID = [Bank Branches].BankID WHERE (((Banks.BankName)=[Forms]![CBO]![BanksComboBox].[Text])) ORDER BY [Bank Branches].BranchID;
(BanksComboBox.text is the only change)
again did not work..

but if i substitute the BanksComboBox with a text field, it works properly..
what am i doing wrong here ?

saleemMSMS said:

i tried
SELECT [Bank Branches].BranchID, [Bank Branches].BranchName FROM Banks INNER JOIN [Bank Branches] ON Banks.BankID = [Bank Branches].BankID WHERE (((Banks.BankName)=[Forms]![CBO]![BanksComboBox])) ORDER BY [Bank Branches].BranchID;

but the 2nd combobox doesnt show values

Click to expand...

You entered that for the row source of the 2nd combobox?

What do you have as the row source for BanksComboBox? Whichever column you have set as the bound column will be the value that is actually stored in the combo box - not necessarily what you see displayed on the form. So if the bound column of BanksComboBox is not the Bank Name, then the query for the row source of the 2nd combo box won't return any results.

Also - you'll have to refresh the form after the BanksComboBox is updated before the change will take effect for the 2nd combo box - if you haven't done so. On the "after update" event of the 1st combo box, enter an event procedure with the code: "Me.Refresh".

On another note - if you already have a table with the Bank ID and the Bank Names, you could just as easily store the Bank ID in any other tables instead of the Bank Names to save space.

hey thank you very much. DKO
i had used bound column as 1 in the first combo box and tried to equate the BankNAme.
i altered the row source of the 2nd combo as follows ad its working properly.

thank you again !!
SELECT [Bank Branches].BranchName, [Bank Branches].BankID FROM Banks INNER JOIN [Bank Branches] ON Banks.BankID=[Bank Branches].BankID WHERE (((Banks.BankID)=[Forms]![CBO]![BanksComboBox])) ORDER BY [Bank Branches].BranchName;

hey btw, how to answer this ?

i have 2 tables namely
Books ( BooksID, BookName, Price)
Magazine (MagID, MagName, Edition)

also i have another table.
Orders (OrderID, BooksName/MagName, NoOfBooks)

now i have a form for the Orders Table. the form has 2 combo Boxes. one the first combo box says whether its a Book or Magazine (its sort of hard coded) then depending on the 1st Combo box, the values of the 2d combo box should change. if Book is selected, all the bookNames should be loaded into the 2nd combo box where as if magazine is selected, all the magazine values should get filled.

how am i to do this in access ?


please answer.. thank you

saleemMSMS said:

hey btw, how to answer this ?

i have 2 tables namely
Books ( BooksID, BookName, Price)
Magazine (MagID, MagName, Edition)

also i have another table.
Orders (OrderID, BooksName/MagName, NoOfBooks)

now i have a form for the Orders Table. the form has 2 combo Boxes. one the first combo box says whether its a Book or Magazine (its sort of hard coded) then depending on the 1st Combo box, the values of the 2d combo box should change. if Book is selected, all the bookNames should be loaded into the 2nd combo box where as if magazine is selected, all the magazine values should get filled.

how am i to do this in access ?


please answer.. thank you

Click to expand...

For this, you'd have to do something like in MStef's example database.

ie, you'd enter something like this as an event procedure on the "after update" event of the 1st combo box.