filtering

[Joanna_gr]

hi all! I need to create a combo box to select and show all the same values in a column.
For instance I have two columns one with the banks and a second with their addresses.
Sometimes it happens the same bank to have 4 or 5 addresses and in this case there are 4 same entries in the first column. How do I define a tool in a way so each time to select and view only the entries that related to one bank and not all my entries or just one at combo box does. something like filtering if that explains better my request. Thank you very much

 

[SydneyGeek]

See the answers on this thread...
http://www.mrexcel.com/board2/viewtopic.php?t=67600
Denis

 

[Joanna_gr]

Are you sure that this explanation works in any case? Cause I only get a pop up window asking me to type a parameter value! But I don't want to type it. I want to SELECT it from the 1st combo box. Any ideas what might am I doing wrong?

 

[SydneyGeek]

The way I would do this is with 2 tables -- Banks and Branches. Banks has, for example, these fields: BankID, BankName. Branches has, for example, BranchID, BankID, BranchName, and other fields with address, phone, manager name, etc.
The 2 tables are related via the BankID field. OK, on the form, you have cmbBank. Control Source is BankID, RowSource has BankID and BankName as the fields. BankID is hidden (the Combo wizard handles this).
cmbBranch has these fields: BranchID (hidden, Control Source), BranchName, Address Details as required, and finally BankID. [Actually, a ListBox may be more flexible for showing all the fields, but the setup is the same].
Now, in Design View on cmbBranch, edit the RowSource (hit the "..." button to display the grid). Go to the BankID field, and in the criteria row, type Forms![MyFormName]![cmbBank] (Change MyFormName to suit). Close and save.
In design view on cmbBank, go to the Events tab. Hit the builder for AfterUpdate, select Code Builder, and in the row after Sub cmbBank_AfterUpdate, type: [cmbBranch].Requery
Close and save.

Now, the first combo will give a list of banks. Once you have made a selection the second combo (or ListBox) will show only branches for the bank that you selected.

HTH
Denis

 

[Joanna_gr]

At last. something that perfectly works. thank you thank you and thank you again. ray: