How to limit combo box list based on value in another combo box?

[Guest]

Hello,

I have a combo-box (#1) that looks up to a lookup table that contains a
series of Adverse Event categories. I want a second combo box that will only
show the Adverse Events themselves based on the Category chosen in Combo Box
#1. My thought was to have the combo box look up to a query that looks
something like this, but it does not work (see code):

SELECT LU_AETest.AESelect
FROM LU_AETest
WHERE (((LU_AETest.AE)=[Me].[AE]))
ORDER BY LU_AETest.AESelect;

where 'LU_AETest' is the lookup table, 'LU_AETest.AESelect' is the AE value
I'm looking to fill in, 'LU_AETest.AE' is the AE category that I'm looking to
match up to the field 'AE' that I've just filled out on the form. All that
happens when I click on the 'AESelect' combo box is 'Me.AE - Enter Parameter
Value'. Once I fill in the AE Category ('AE'), I would like that value to
limit what the user sees for choices of appropriate AE's ('AESelect'). How
can I do this?

Thanks,

 

[Marshall Barton]

Hello,

I have a combo-box (#1) that looks up to a lookup table that contains a
series of Adverse Event categories. I want a second combo box that will only
show the Adverse Events themselves based on the Category chosen in Combo Box
#1. My thought was to have the combo box look up to a query that looks
something like this, but it does not work (see code):

SELECT LU_AETest.AESelect
FROM LU_AETest
WHERE (((LU_AETest.AE)=[Me].[AE]))
ORDER BY LU_AETest.AESelect;

where 'LU_AETest' is the lookup table, 'LU_AETest.AESelect' is the AE value
I'm looking to fill in, 'LU_AETest.AE' is the AE category that I'm looking to
match up to the field 'AE' that I've just filled out on the form. All that
happens when I click on the 'AESelect' combo box is 'Me.AE - Enter Parameter
Value'. Once I fill in the AE Category ('AE'), I would like that value to
limit what the user sees for choices of appropriate AE's ('AESelect'). How
can I do this?

It looks like what you have is fine, except that you can not
use Me in a query (it's strictly a VBA keyword). Change
that part to the full form reference:

WHERE LU_AETest.AE =Forms!yourform.AE

Double check that the AE combo box is really named AE.

 

[Guest]

Hi Marshall,

Definitely a step in the right direction and thank you! The only thing I'm
noticing is that when I choose an 'AE Category', I'm pretty much 'locked-in'
to the list of AE's that shows in the second combo box. In other words, if I
choose a different 'AE Category' in that first Combo box (while still on the
same record), the 'lookup' Combo Box #2 still shows the list of 'AE's' that
belongs with the first choice of 'AE Category', instead of 'refreshing' the
list of AE's to fit the different category. Is there a way to make a refresh
of the 'AE' Combo box occur so that my data entry people can change the 'AE
Category' if they need to and see a new list of associated 'AE's' in that
second Combo Box?

Thanks.
--
Pat Dools

Hello,

I have a combo-box (#1) that looks up to a lookup table that contains a
series of Adverse Event categories. I want a second combo box that will only
show the Adverse Events themselves based on the Category chosen in Combo Box
#1. My thought was to have the combo box look up to a query that looks
something like this, but it does not work (see code):

SELECT LU_AETest.AESelect
FROM LU_AETest
WHERE (((LU_AETest.AE)=[Me].[AE]))
ORDER BY LU_AETest.AESelect;

where 'LU_AETest' is the lookup table, 'LU_AETest.AESelect' is the AE value
I'm looking to fill in, 'LU_AETest.AE' is the AE category that I'm looking to
match up to the field 'AE' that I've just filled out on the form. All that
happens when I click on the 'AESelect' combo box is 'Me.AE - Enter Parameter
Value'. Once I fill in the AE Category ('AE'), I would like that value to
limit what the user sees for choices of appropriate AE's ('AESelect'). How
can I do this?

It looks like what you have is fine, except that you can not
use Me in a query (it's strictly a VBA keyword). Change
that part to the full form reference:

WHERE LU_AETest.AE =Forms!yourform.AE

Double check that the AE combo box is really named AE.

 

[Marshall Barton]

Definitely a step in the right direction and thank you! The only thing I'm
noticing is that when I choose an 'AE Category', I'm pretty much 'locked-in'
to the list of AE's that shows in the second combo box. In other words, if I
choose a different 'AE Category' in that first Combo box (while still on the
same record), the 'lookup' Combo Box #2 still shows the list of 'AE's' that
belongs with the first choice of 'AE Category', instead of 'refreshing' the
list of AE's to fit the different category. Is there a way to make a refresh
of the 'AE' Combo box occur so that my data entry people can change the 'AE
Category' if they need to and see a new list of associated 'AE's' in that
second Combo Box?

Right! I should have added that you need to add a line of
code to both the first combo box's AfterUpdate event should
look like:

Me.AESelect = Null
Me.AESelect.Requery

and the form's Current event

Me.AESelect.Requery