Listboxes results based on table joins

[prog]

Hi,

I have two tables. One has a list of questions and the other table lists
answers to those questions. It's a one to many (one question many answers).

What I'm trying to do is have a list box list all the questions, which I
already have and then based on the question I pick (onclick) a second listbox
appears showing me specific answers to that question. And I already have
this created via joins between the two tables in the background.

Thanks

 

[Jeanette Cunningham]

Hi prog,
this is similar to creating cascading combo boxes.
Use the after update event of the listbox with questions, to build the row
source for the listbox with answers.

A simple example would be something like this:

Dim strSQL as String

strSQL = "Select AnswerID, AnswerDescr " _
& "From tblAnswer " _
& "Where QuestionID = " & Me.[NameOfQuestionListbox] & " " _
& "Order by AnswerDescr"
Me.[NameOfAnswerListbox].Rowsource = strSQL


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[prog]

Are there any websites with examples to my question below? Any sites that
you might recommend?

Hi prog,
this is similar to creating cascading combo boxes.
Use the after update event of the listbox with questions, to build the row
source for the listbox with answers.

A simple example would be something like this:

Dim strSQL as String

strSQL = "Select AnswerID, AnswerDescr " _
& "From tblAnswer " _
& "Where QuestionID = " & Me.[NameOfQuestionListbox] & " " _
& "Order by AnswerDescr"
Me.[NameOfAnswerListbox].Rowsource = strSQL


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi,

I have two tables. One has a list of questions and the other table lists
answers to those questions. It's a one to many (one question many
answers).

What I'm trying to do is have a list box list all the questions, which I
already have and then based on the question I pick (onclick) a second
listbox
appears showing me specific answers to that question. And I already have
this created via joins between the two tables in the background.

Thanks

 

[Jeanette Cunningham]

Here are some websites that show how to do cascading combo boxes.
Use them for your listboxes.

http://support.microsoft.com/kb/289670



http://msdn.microsoft.com/en-au/bb404901.aspx



http://www.utterangel.com/utterangel.aspx#9

there are 2 examples

Cascading Combo Boxes Database
30K
Demonstrates how to make combo boxes whose values are filtered by the
value of other combo boxes in a cascade.


And



Cascading Combo Boxes Using 2 Tables
17K
Demonstrates how to make combo boxes whose values are filtered by the
value of other combo boxes (whose record source are two tables) in a
cascade.


http://www.blueclaw-db.com/comboboxlist/access_dependent_dropdown_list.htm







http://www.microsoft.com/downloads/...5E-A29B-4A43-8508-78E549ECD14C&displaylang=en






Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Are there any websites with examples to my question below? Any sites that
you might recommend?

Hi prog,
this is similar to creating cascading combo boxes.
Use the after update event of the listbox with questions, to build the
row
source for the listbox with answers.

A simple example would be something like this:

Dim strSQL as String

strSQL = "Select AnswerID, AnswerDescr " _
& "From tblAnswer " _
& "Where QuestionID = " & Me.[NameOfQuestionListbox] & " " _
& "Order by AnswerDescr"
Me.[NameOfAnswerListbox].Rowsource = strSQL


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi,

I have two tables. One has a list of questions and the other table
lists
answers to those questions. It's a one to many (one question many
answers).

What I'm trying to do is have a list box list all the questions, which
I
already have and then based on the question I pick (onclick) a second
listbox
appears showing me specific answers to that question. And I already
have
this created via joins between the two tables in the background.

Thanks

 

[prog]

Ok. I was able to get that to work. Now I'm running into some issues when I
try to create a third listbox which returns results based on the second one.

Hi prog,
this is similar to creating cascading combo boxes.
Use the after update event of the listbox with questions, to build the row
source for the listbox with answers.

A simple example would be something like this:

Dim strSQL as String

strSQL = "Select AnswerID, AnswerDescr " _
& "From tblAnswer " _
& "Where QuestionID = " & Me.[NameOfQuestionListbox] & " " _
& "Order by AnswerDescr"
Me.[NameOfAnswerListbox].Rowsource = strSQL


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi,

I have two tables. One has a list of questions and the other table lists
answers to those questions. It's a one to many (one question many
answers).

What I'm trying to do is have a list box list all the questions, which I
already have and then based on the question I pick (onclick) a second
listbox
appears showing me specific answers to that question. And I already have
this created via joins between the two tables in the background.

Thanks

 

[Jeanette Cunningham]

It is the same process whether 2, 3, 4 or more cascading combos or list
boxes.

The 3rd list box has its row source filtered by the item selected in the 2nd
list box.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Ok. I was able to get that to work. Now I'm running into some issues
when I
try to create a third listbox which returns results based on the second
one.

Hi prog,
this is similar to creating cascading combo boxes.
Use the after update event of the listbox with questions, to build the
row
source for the listbox with answers.

A simple example would be something like this:

Dim strSQL as String

strSQL = "Select AnswerID, AnswerDescr " _
& "From tblAnswer " _
& "Where QuestionID = " & Me.[NameOfQuestionListbox] & " " _
& "Order by AnswerDescr"
Me.[NameOfAnswerListbox].Rowsource = strSQL


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi,

I have two tables. One has a list of questions and the other table
lists
answers to those questions. It's a one to many (one question many
answers).

What I'm trying to do is have a list box list all the questions, which
I
already have and then based on the question I pick (onclick) a second
listbox
appears showing me specific answers to that question. And I already
have
this created via joins between the two tables in the background.

Thanks

 

[prog]

But do I need to modify the AfterUpdate event in the second list box to get
the third list box to work? This is where I'm running into the error. I add
an afterupdate event to the second list box inorder to create the third, but
doing this causes an issue with the first listbox. I'm guessing based on your
answer I don't need to add a second afterupdate event to the second listbox.

It is the same process whether 2, 3, 4 or more cascading combos or list
boxes.

The 3rd list box has its row source filtered by the item selected in the 2nd
list box.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Ok. I was able to get that to work. Now I'm running into some issues
when I
try to create a third listbox which returns results based on the second
one.

Hi prog,
this is similar to creating cascading combo boxes.
Use the after update event of the listbox with questions, to build the
row
source for the listbox with answers.

A simple example would be something like this:

Dim strSQL as String

strSQL = "Select AnswerID, AnswerDescr " _
& "From tblAnswer " _
& "Where QuestionID = " & Me.[NameOfQuestionListbox] & " " _
& "Order by AnswerDescr"
Me.[NameOfAnswerListbox].Rowsource = strSQL


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Hi,

I have two tables. One has a list of questions and the other table
lists
answers to those questions. It's a one to many (one question many
answers).

What I'm trying to do is have a list box list all the questions, which
I
already have and then based on the question I pick (onclick) a second
listbox
appears showing me specific answers to that question. And I already
have
this created via joins between the two tables in the background.

Thanks

 

[prog]

I guess my question is then, How do I define the row source for the 3rd list
box based on the values of the second box (which are based on the first)?
Should I use syntax like the following for the row source in the third
listbox: Me.[listbox2].Requery ??

It is the same process whether 2, 3, 4 or more cascading combos or list
boxes.

The 3rd list box has its row source filtered by the item selected in the 2nd
list box.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Ok. I was able to get that to work. Now I'm running into some issues
when I
try to create a third listbox which returns results based on the second
one.

Hi prog,
this is similar to creating cascading combo boxes.
Use the after update event of the listbox with questions, to build the
row
source for the listbox with answers.

A simple example would be something like this:

Dim strSQL as String

strSQL = "Select AnswerID, AnswerDescr " _
& "From tblAnswer " _
& "Where QuestionID = " & Me.[NameOfQuestionListbox] & " " _
& "Order by AnswerDescr"
Me.[NameOfAnswerListbox].Rowsource = strSQL


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Hi,

I have two tables. One has a list of questions and the other table
lists
answers to those questions. It's a one to many (one question many
answers).

What I'm trying to do is have a list box list all the questions, which
I
already have and then based on the question I pick (onclick) a second
listbox
appears showing me specific answers to that question. And I already
have
this created via joins between the two tables in the background.

Thanks

 

[Jeanette Cunningham]

I seem to be missing the point of your difficulty.
And I do remember how difficult it was when I first started with cascading
combos and list boxes.
I will try to give an example.

Suppose you were trying to sort out countries, states and cities (please
ignore the fact of cities with the same name in different states for this
example).

The first combo would allow user to select a country.

The 2nd combo only shows the cities that are in the country selected in the
1st combo.
The 2nd combo would see the value of the ID of the Country from the 1st
combo.
The 2nd combo would use a where clause something like:
"Where [CountryID] = " & Me.Combo1& ""
in the query that makes the row source for the 2nd combo.


On to the 3rd combo.
The 3rd combo looks at the 2nd combo to see which value for city was chosen.
The 3rd combo has a where clause something like:
"Where [CityID] = " & Me.Combo2 & ""
in the query that makes the row source for the 3rd combo.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I guess my question is then, How do I define the row source for the 3rd
list
box based on the values of the second box (which are based on the first)?
Should I use syntax like the following for the row source in the third
listbox: Me.[listbox2].Requery ??

It is the same process whether 2, 3, 4 or more cascading combos or list
boxes.

The 3rd list box has its row source filtered by the item selected in the
2nd
list box.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Ok. I was able to get that to work. Now I'm running into some issues
when I
try to create a third listbox which returns results based on the second
one.

:

Hi prog,
this is similar to creating cascading combo boxes.
Use the after update event of the listbox with questions, to build the
row
source for the listbox with answers.

A simple example would be something like this:

Dim strSQL as String

strSQL = "Select AnswerID, AnswerDescr " _
& "From tblAnswer " _
& "Where QuestionID = " & Me.[NameOfQuestionListbox] & " " _
& "Order by AnswerDescr"
Me.[NameOfAnswerListbox].Rowsource = strSQL


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Hi,

I have two tables. One has a list of questions and the other table
lists
answers to those questions. It's a one to many (one question many
answers).

What I'm trying to do is have a list box list all the questions,
which
I
already have and then based on the question I pick (onclick) a
second
listbox
appears showing me specific answers to that question. And I already
have
this created via joins between the two tables in the background.

Thanks

 

[prog]

Ok. So, I'm setting the rowsource to what you have described, but everytime
i run the form it asks me to Enter a Parameter value.
Here is my sql in the Row Source (for the second listbox that connects to
the first listbox):
SELECT Answer FROM tblAnswers WHERE join_field=Me.List1;


Do I have the syntax correct for Me.List1? Should there be any special
characters around it?

I seem to be missing the point of your difficulty.
And I do remember how difficult it was when I first started with cascading
combos and list boxes.
I will try to give an example.

Suppose you were trying to sort out countries, states and cities (please
ignore the fact of cities with the same name in different states for this
example).

The first combo would allow user to select a country.

The 2nd combo only shows the cities that are in the country selected in the
1st combo.
The 2nd combo would see the value of the ID of the Country from the 1st
combo.
The 2nd combo would use a where clause something like:
"Where [CountryID] = " & Me.Combo1& ""
in the query that makes the row source for the 2nd combo.


On to the 3rd combo.
The 3rd combo looks at the 2nd combo to see which value for city was chosen.
The 3rd combo has a where clause something like:
"Where [CityID] = " & Me.Combo2 & ""
in the query that makes the row source for the 3rd combo.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I guess my question is then, How do I define the row source for the 3rd
list
box based on the values of the second box (which are based on the first)?
Should I use syntax like the following for the row source in the third
listbox: Me.[listbox2].Requery ??

It is the same process whether 2, 3, 4 or more cascading combos or list
boxes.

The 3rd list box has its row source filtered by the item selected in the
2nd
list box.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Ok. I was able to get that to work. Now I'm running into some issues
when I
try to create a third listbox which returns results based on the second
one.

:

Hi prog,
this is similar to creating cascading combo boxes.
Use the after update event of the listbox with questions, to build the
row
source for the listbox with answers.

A simple example would be something like this:

Dim strSQL as String

strSQL = "Select AnswerID, AnswerDescr " _
& "From tblAnswer " _
& "Where QuestionID = " & Me.[NameOfQuestionListbox] & " " _
& "Order by AnswerDescr"
Me.[NameOfAnswerListbox].Rowsource = strSQL


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Hi,

I have two tables. One has a list of questions and the other table
lists
answers to those questions. It's a one to many (one question many
answers).

What I'm trying to do is have a list box list all the questions,
which
I
already have and then based on the question I pick (onclick) a
second
listbox
appears showing me specific answers to that question. And I already
have
this created via joins between the two tables in the background.

Thanks

 

[John W. Vinson]

Ok. So, I'm setting the rowsource to what you have described, but everytime
i run the form it asks me to Enter a Parameter value.
Here is my sql in the Row Source (for the second listbox that connects to
the first listbox):
SELECT Answer FROM tblAnswers WHERE join_field=Me.List1;

That is NOT what Jeanette suggested. Reread her message. She was suggesting
that you concatenate the *VALUE* in Me.List1 into the SQL string, not
incorporating its name into the SQL string.

 

[prog]

Could you show me an example?

That is NOT what Jeanette suggested. Reread her message. She was suggesting
that you concatenate the *VALUE* in Me.List1 into the SQL string, not
incorporating its name into the SQL string.

 

[John W. Vinson]

Could you show me an example?

Ummm... Jeanette DID show you an example:

The 2nd combo would see the value of the ID of the Country from the 1st
combo.
The 2nd combo would use a where clause something like:
"Where [CountryID] = " & Me.Combo1& ""
in the query that makes the row source for the 2nd combo.

Your code would build a SQL string and then assign it as the RowSource of the
second combo.

 

[Jeanette Cunningham]

There are examples on that list of websites I posted a couple of days ago in
this thread.

Perhaps it would be easier if you posted the following:
--the names of the 3 list boxes
--the row source sql of the 2nd and 3rd list boxes and an indication of
where you think the problem is.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[prog]

Hi Jeanette,

Ok. So, here are my listboxes:

List1, List2, and List3

All of the code below use an Afterupdate event procedure.

The code to populate rowsource for List2, coming from Afterupdate List1
(This works fine):

Dim strSQL as String

strSQL = "Select description " _
& "From tbldescript " _
& "Where joinID = " & Me.[List1] & " " _
& "Order by description"
Me.[List2].Rowsource = strSQL

Here is the code for rowsource List3, coming from Afterupdate List2 (List3
is not populating for some reason,eventhough,based on the code below, it
should):

Dim strSQL as String

strSQL = "Select followup_description " _
& "From tblFollowup " _
& "Where joinID = " & Me.[List2] & " " _
& "Order by followup_description"
Me.[List3].Rowsource = strSQL

So, List3 for some reason is not populating when I select updated values
from List 2.

 

[Jeanette Cunningham]

You can test the sql of the row source for list 3 like this:

strSQL = "Select followup_description " _
& "From tblFollowup " _
& "Where joinID = " & Me.[List2] & " " _
& "Order by followup_description"
Debug.Print strSQL


Open the form in normal view and make a selection from list 2.
Now go Ctl + G to open the immediate window.
Copy the sql
Now open a new query and switch to sql view.
Post in the sql.
Switch the query to datasheet view and if there is a problem, you will get
an error message that most likely will help you to fix the sql so that list
3 will populate.

Another thing to check is the listbox
Have you checked the no. of columns returned by the query with the no of
columns in the list box? do they match, or is the column you want hidden by
mistake.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi Jeanette,

Ok. So, here are my listboxes:

List1, List2, and List3

All of the code below use an Afterupdate event procedure.

The code to populate rowsource for List2, coming from Afterupdate List1
(This works fine):

Dim strSQL as String

strSQL = "Select description " _
& "From tbldescript " _
& "Where joinID = " & Me.[List1] & " " _
& "Order by description"
Me.[List2].Rowsource = strSQL

Here is the code for rowsource List3, coming from Afterupdate List2 (List3
is not populating for some reason,eventhough,based on the code below, it
should):

Dim strSQL as String

strSQL = "Select followup_description " _
& "From tblFollowup " _
& "Where joinID = " & Me.[List2] & " " _
& "Order by followup_description"
Me.[List3].Rowsource = strSQL

So, List3 for some reason is not populating when I select updated values
from List 2.