By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,573 Members | 938 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,573 IT Pros & Developers. It's quick & easy.

How to populate a listbox using a combo box?

P: 9
I have a combo box (cboStore) and a listbox (Qlist) both are unbound to a form call frmFAQ. What I want is to be a able to select a Subject from the combo box and have the results show up in the listbox.

In the rowsource of the combox I have this code:
Expand|Select|Wrap|Line Numbers
  1.  SELECT Subjecttbl.SubID, Subjecttbl.Subject FROM Subjecttbl; 
in the rowsource of the listbox I have:
Expand|Select|Wrap|Line Numbers
  1.  SELECT Subjecttbl.Subject, Listtbl.List FROM Subjecttbl INNER JOIN Listtbl ON Subjecttbl.SubID=Listtbl.SubID GROUP BY Subjecttbl.Subject, Listtbl.List;  
Everything shows up in the combo box but nothing in the listbox. Can someone help me with this.
Feb 6 '07 #1
Share this Question
Share on Google+
14 Replies


Rabbit
Expert Mod 10K+
P: 12,365
The way it is set up right now, your list box should show every record in Listtbl that has a matching SubID in Subjecttbl.

Is the column count and column widths set up correction?

If you want it to populate the list box depending on the choice in the combo box, then you have to add code to the After Update event of the combo box.

Expand|Select|Wrap|Line Numbers
  1. Me!NameOfListBox.RowSource = "Select Listtbl.List From Subjecttbl Where SubID = " & Me!NameOfComboBox
This means the combo box has to be bound on SubID.
Feb 6 '07 #2

P: 22
Theres also an example access file on microsofts download page that does exactly this


http://www.microsoft.com/downloads/d...DisplayLang=en

Have a look
Feb 6 '07 #3

NeoPa
Expert Mod 15k+
P: 31,485
See (Example Filtering on a Form.) for a fuller explanation.
In your SQL though, you are trying to reference items on a form. The SQL though, is executed outside of the form (in the SQL engine) so local references to the controls on the form will not be interpreted correctly (or at all even).
Feb 7 '07 #4

P: 9
The way it is set up right now, your list box should show every record in Listtbl that has a matching SubID in Subjecttbl.

Is the column count and column widths set up correction?

If you want it to populate the list box depending on the choice in the combo box, then you have to add code to the After Update event of the combo box.

Expand|Select|Wrap|Line Numbers
  1. Me!NameOfListBox.RowSource = "Select Listtbl.List From Subjecttbl Where SubID = " & Me!NameOfComboBox
This means the combo box has to be bound on SubID.
I have add the code to the program like this in the Afterupdate event of combobox:
Expand|Select|Wrap|Line Numbers
  1. Me!Qlist.RowSource = "Select Listtbl.List From Subjecttbl Where SubID = " & Me!Subject 
When I go to run the program a subject is already selected in the combo box? When I select a different subject in the combo box a message box pops up and prompt me to enter a parameter. It also have Listtbl.List right before you enter the parameter. So then I enter say 1 and nothing happens in the listbox. What happen here?

Does the combo box has to be bound to the form or table? I set the combox box bound to the SubID in the form through the control source. Is this correct?

Also do I need to bound the listbox to the form like I did the combo box. What goes in the rowsource of the listbox now?
Feb 7 '07 #5

NeoPa
Expert Mod 15k+
P: 31,485
Your code :
Expand|Select|Wrap|Line Numbers
  1. Me!Qlist.RowSource = "Select Listtbl.List From Subjecttbl Where SubID = " & Me!Subject 
is trying to get Listtbl.List From Subjecttbl.
Feb 7 '07 #6

P: 9
Your code :
Expand|Select|Wrap|Line Numbers
  1. Me!Qlist.RowSource = "Select Listtbl.List From Subjecttbl Where SubID = " & Me!Subject 
is trying to get Listtbl.List From Subjecttbl.
The subject shows up in the combo box but when I click on the the subject nothing shows up in the list box

Expand|Select|Wrap|Line Numbers
  1. Me!Qlist.RowSource = "Select Subjecttbl.Subject From Subjecttbl Where SubID = " & Me!Subject 
In the Subjecttbl I have:
SubID Subject
1 Red
2 White
3 Blue
4 Green

In the Listtbl I have:
ListID SubID List
1 1 Roses
2 2 Clouds
3 3 Sky
4 4 Grass

There is a little "+" beside the SubIDs which shows what is in the Listtbl.
When you click on the "+" the Subject and List shows up I guess this links the tables.

The Listtbl has the same "+" but is linked to a FAQtbl. The FAQtbl is bound to the form "frmFAQ".

Now in the Rowsource of the Combobox I have this:
Expand|Select|Wrap|Line Numbers
  1. SELECT Subjecttbl.SubID, Subjecttbl.Subject FROM Subjecttbl; 
the colum count is 2 and the column widths is 0";0.7875" for the combo box
as I stated before do I bound the combox to the form SubID or the table?

but I have nothing in the listbox. Is any suppose to go in the rowsource for the listbox?
Feb 7 '07 #7

Rabbit
Expert Mod 10K+
P: 12,365
The subject shows up in the combo box but when I click on the the subject nothing shows up in the list box

Expand|Select|Wrap|Line Numbers
  1. Me!Qlist.RowSource = "Select Subjecttbl.Subject From Subjecttbl Where SubID = " & Me!Subject 
In the Subjecttbl I have:
SubID Subject
1 Red
2 White
3 Blue
4 Green

In the Listtbl I have:
ListID SubID List
1 1 Roses
2 2 Clouds
3 3 Sky
4 4 Grass

There is a little "+" beside the SubIDs which shows what is in the Listtbl.
When you click on the "+" the Subject and List shows up I guess this links the tables.

The Listtbl has the same "+" but is linked to a FAQtbl. The FAQtbl is bound to the form "frmFAQ".

Now in the Rowsource of the Combobox I have this:
Expand|Select|Wrap|Line Numbers
  1. SELECT Subjecttbl.SubID, Subjecttbl.Subject FROM Subjecttbl; 
the colum count is 2 and the column widths is 0";0.7875" for the combo box
as I stated before do I bound the combox to the form SubID or the table?

but I have nothing in the listbox. Is any suppose to go in the rowsource for the listbox?
I'm sorry, it was my fault in the first place for giving you the wrong code.

For the Combo Box:
Row Source = SELECT Subjecttbl.SubID, Subjecttbl.Subject FROM Subjecttbl
Column Count = 2
Column Width = 0;0.7875
Bound Column = 1

For the After Update event of the Combo Box:
Expand|Select|Wrap|Line Numbers
  1. Me!QList.RowSource = "SELECT Listtbl.List FROM Listtbl WHERE SubID = " & Me!Subject
  2.  
Feb 7 '07 #8

P: 9
I'm sorry, it was my fault in the first place for giving you the wrong code.

For the Combo Box:
Row Source = SELECT Subjecttbl.SubID, Subjecttbl.Subject FROM Subjecttbl
Column Count = 2
Column Width = 0;0.7875
Bound Column = 1

For the After Update event of the Combo Box:
Expand|Select|Wrap|Line Numbers
  1. Me!QList.RowSource = "SELECT Listtbl.List FROM Listtbl WHERE SubID = " & Me!Subject
  2.  
Nothing shows up in the listbox when I make a selection from the combox box.
Feb 8 '07 #9

Rabbit
Expert Mod 10K+
P: 12,365
Try putting the code in the On Change event.
Feb 8 '07 #10

P: 9
Try putting the code in the On Change event.
I put this in the After_Update of the combobox
Expand|Select|Wrap|Line Numbers
  1. Static str1 As String
  2. Dim SQL As String
  3. str1 = str1 & Me![Combo0].Value & ","
  4. Me![List2].RowSource = str1
  5. Me![List2].Requery 
and this in the Rowsource of the combobox
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT Subjecttbl.SubID, Subjecttbl.Subject FROM Subjecttbl; 
What happens now is the comboxbox has the subjects in it but the list box displays numbers instead of a list like when I click the first subject in the combobox a number 1 is displayed in the listbox or a 2 you if I click on the second subject.

the column count is 1 which shows the numbers when I set it to 2 or 3 nothing shows in the listbox. colum width 0.7875. I also change the list box to Value list.
Feb 10 '07 #11

NeoPa
Expert Mod 15k+
P: 31,485
Try putting the code in the On Change event.
I put this in the After_Update of the combobox
Expand|Select|Wrap|Line Numbers
  1. Static str1 As String
  2. Dim SQL As String
  3. str1 = str1 & Me![Combo0].Value & ","
  4. Me![List2].RowSource = str1
  5. Me![List2].Requery 
and this in the Rowsource of the combobox
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT Subjecttbl.SubID, Subjecttbl.Subject FROM Subjecttbl; 
I think Rabbit is away for a few days, but I'm sure he'll want to know why you chose to put it in the AfterUpdate event of the ComboBox rather than where he suggested in the OnChange event (In case you weren't sure, they are not the same thing).
Feb 10 '07 #12

P: 9
I think Rabbit is away for a few days, but I'm sure he'll want to know why you chose to put it in the AfterUpdate event of the ComboBox rather than where he suggested in the OnChange event (In case you weren't sure, they are not the same thing).
I finally got it to work. I tried putting the code in the OnChange event but it did not work. So I put it back in the AfterUpdate event and modified the code
Expand|Select|Wrap|Line Numbers
  1. Dim SQL As String
  2. SQL = "Select List From[Listtbl] where [SubID] = " & Me![Subject].Value
  3. Me![Qlist].RowSource = SQL
  4. Me![Qlist].Requery
  5.  
Thank you for all of your help Rabbit and NeoPa.
Feb 15 '07 #13

Rabbit
Expert Mod 10K+
P: 12,365
Not a problem, glad you got it working.
Feb 15 '07 #14

NeoPa
Expert Mod 15k+
P: 31,485
I guess the AfterUpdate event was the right place after all then :D
Glad you got it working anyway.

Just as a final point, it's very rarely necessary to add the .Value part to a control as it is the default property in most (if not all) cases. Sometimes people choose to for documentary purposes, and that's fine, but it's not required.
Feb 17 '07 #15

Post your reply

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