423,680 Members | 2,439 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,680 IT Pros & Developers. It's quick & easy.

Using one drop down box to select contents of another drop down box

P: 3
Hi, I have done a search on this topic but can not find anything that clearly helps with this problem, so bear with me please.

I have a list box that contains a number of options for my staff to choose from. When they choose one of the options I then want another drop down box to be populated with the relevant further options based on their first selection.

Thanks for your help.
1 Week Ago #1

✓ answered by twinnyfo

Othafa,

Welcome to Bytes!

You would assign a VBA procedure to the AfterUpdate property of the List box that uses the value of the List box to generate the RowSource Property of the Combo Box. Example below:

Expand|Select|Wrap|Line Numbers
  1. Private Sub lstCompany_AfterUpdate()
  2.     Dim strSQL  As String
  3.  
  4.     strSQL = "SELECT ProductID, ProductName " & _
  5.              "FROM tblCompanyProducts " & _
  6.              "WHERE CompanyID = " & lstCompany & ";"
  7.  
  8.     Me.cboProducts.RowSource = strSQL
  9.     Me.cboProducts.Requery
  10.  
  11. End Sub
Hope that hepps!

Share this Question
Share on Google+
6 Replies


twinnyfo
Expert Mod 2.5K+
P: 2,666
Othafa,

Welcome to Bytes!

You would assign a VBA procedure to the AfterUpdate property of the List box that uses the value of the List box to generate the RowSource Property of the Combo Box. Example below:

Expand|Select|Wrap|Line Numbers
  1. Private Sub lstCompany_AfterUpdate()
  2.     Dim strSQL  As String
  3.  
  4.     strSQL = "SELECT ProductID, ProductName " & _
  5.              "FROM tblCompanyProducts " & _
  6.              "WHERE CompanyID = " & lstCompany & ";"
  7.  
  8.     Me.cboProducts.RowSource = strSQL
  9.     Me.cboProducts.Requery
  10.  
  11. End Sub
Hope that hepps!
1 Week Ago #2

P: 3
Thanks for the prompt reply.

Unfortunately I'm not very good at VBA yet and dont really understand how to use the example in my circumstances. I realise now that I should have explained my setup better.

I have 6 tables that I want to use.
Table 1 is called "Quality Area" and it contains 5 options. Tables 2 to 6 are called "Quality Theme 1", "Quality Theme 2" etc.

I then created a form using the contents of "Quality Area" in a drop down list box and another drop down list box that I want to list data from the relevant "Quality Theme" table


So if a user were to click on option 2 in "Quality Area", the drop down box next to it will be populated with the contents of "Quality Theme 2"

I hope that makes sense.

Thanks.
1 Week Ago #3

NeoPa
Expert Mod 15k+
P: 31,084
All the techniques you need for that are included in this article (Cascaded Form Filtering).
1 Week Ago #4

NeoPa
Expert Mod 15k+
P: 31,084
We seem to have crossed with our posts.

Your design sounds like a problem. It isn't normalised (See Database Normalisation and Table Structures).

Once that problem's been resolved, and you have all of your Quality Themes in a single table where they belong, you'll find that the other article I linked to will make better sense and you can achieve everything you require.
1 Week Ago #5

P: 3
Thanks for this NeoPa. I'll have a read and see what I come up with.
1 Week Ago #6

NeoPa
Expert Mod 15k+
P: 31,084
Sometimes Normalisation, and the fundamentally different way of thinking about things that databases in particular, and set theory in general, require can be a struggle. Especially for those that are used to other types of logic, such as coders and analysts. Once you get it though, everything fits into place much more easily.

Good luck and I hope you manage to make it make sense.
1 Week Ago #7

Post your reply

Sign in to post your reply or Sign up for a free account.