How to populate a listbox using a combo box?

[rdade22]

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:

 SELECT Subjecttbl.SubID, Subjecttbl.Subject FROM Subjecttbl; 

in the rowsource of the listbox I have:

 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.

[Rabbit]

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.

Me!NameOfListBox.RowSource = "Select Listtbl.List From Subjecttbl Where SubID = " & Me!NameOfComboBox

This means the combo box has to be bound on SubID.

[Phille]

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

[NeoPa]

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).

[rdade22]

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.

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:

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?

[NeoPa]

Your code :

Me!Qlist.RowSource = "Select Listtbl.List From Subjecttbl Where SubID = " & Me!Subject 

is trying to get Listtbl.List From Subjecttbl.

[rdade22]

Your code :

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

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:

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?

[Rabbit]

The subject shows up in the combo box but when I click on the the subject nothing shows up in the list box

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:

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:

Me!QList.RowSource = "SELECT Listtbl.List FROM Listtbl WHERE SubID = " & Me!Subject
 

[rdade22]

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:

Me!QList.RowSource = "SELECT Listtbl.List FROM Listtbl WHERE SubID = " & Me!Subject
 

Nothing shows up in the listbox when I make a selection from the combox box.

[Rabbit]

Try putting the code in the On Change event.

[rdade22]

Try putting the code in the On Change event.

I put this in the After_Update of the combobox

Static str1 As String
Dim SQL As String
str1 = str1 & Me![Combo0].Value & ","
Me![List2].RowSource = str1
Me![List2].Requery 

and this in the Rowsource of the combobox

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.

[NeoPa]

Try putting the code in the On Change event.

I put this in the After_Update of the combobox

Static str1 As String
Dim SQL As String
str1 = str1 & Me![Combo0].Value & ","
Me![List2].RowSource = str1
Me![List2].Requery 

and this in the Rowsource of the combobox

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).

[rdade22]

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

Dim SQL As String
SQL = "Select List From[Listtbl] where [SubID] = " & Me![Subject].Value
Me![Qlist].RowSource = SQL
Me![Qlist].Requery
 

Thank you for all of your help Rabbit and NeoPa.

[Rabbit]

Not a problem, glad you got it working.

[NeoPa]

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.