424,051 Members | 1,247 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,051 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.
Nov 7 '18 #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,780
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!
Nov 7 '18 #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.
Nov 7 '18 #3

NeoPa
Expert Mod 15k+
P: 31,168
All the techniques you need for that are included in this article (Cascaded Form Filtering).
Nov 7 '18 #4

NeoPa
Expert Mod 15k+
P: 31,168
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.
Nov 7 '18 #5

P: 3
Thanks for this NeoPa. I'll have a read and see what I come up with.
Nov 8 '18 #6

NeoPa
Expert Mod 15k+
P: 31,168
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.
Nov 9 '18 #7

Post your reply

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