Cascading Combo Box and Additional Parameter

[Guest]

I have cascading combo boxes where the virus chosen limits the test combo
box. I would like to also limit the test drop-down list by an additional
parameter; I would like only the RRT-PCR tests to come up, excluding the
cELISA and PCR tests.

In other words,

The code below results in the following tests being selected, when the virus
AIV is chosen:

Mtx RRT-PCR
H5 RRT-PCR
H7 RRT-PCR
PCR
cELISA

But I only want the first 3 to show up. How do I do this? The simpler the
solution, the better for me.

Private Sub cboVirus_AfterUpdate()

'Sets up test combo box as being dependent on virus combo box'
Me!cboTest.RowSource = "SELECT lstTests.Test FROM lstTests WHERE
lstTests.Virus = cboVirus.value"

'Clears previous entry'
Me![cboTest] = " "
Me![cboTest].Requery

End Sub

Thanks, AG

 

[Marshall Barton]

I have cascading combo boxes where the virus chosen limits the test combo
box. I would like to also limit the test drop-down list by an additional
parameter; I would like only the RRT-PCR tests to come up, excluding the
cELISA and PCR tests.

In other words,

The code below results in the following tests being selected, when the virus
AIV is chosen:

Mtx RRT-PCR
H5 RRT-PCR
H7 RRT-PCR
PCR
cELISA

But I only want the first 3 to show up. How do I do this? The simpler the
solution, the better for me.

Private Sub cboVirus_AfterUpdate()

'Sets up test combo box as being dependent on virus combo box'
Me!cboTest.RowSource = "SELECT lstTests.Test FROM lstTests WHERE
lstTests.Virus = cboVirus.value"

'Clears previous entry'
Me![cboTest] = " "
Me![cboTest].Requery

End Sub

Setting the RowSource automatically requeries the combo box
so the explicit Requery is a waste of time. Also, set the
test combo's value to Null instead of a space character.

Just add anothe condition to the row source query's WHERE
clause:

Me!cboTest.RowSource = "SELECT Test FROM lstTests WHERE
Virus = " & cboVirus & " AND Test Like '* RRT-PCR' "

 

[Guest]

Thanks Marsh,

I got rid of the "space" requery, that was a hold over from when I was
figuring out how to do cascading combo boxes, the dependent combo box was
then not clearing the previous entry.

As for the code, I am getting an "Enter Parameter Value" message box, and
unfortunately I am not yet versed enough in VBA to troubleshoot well. What
needs fixing?

Thanks, AG

 

[Marshall Barton]

I got rid of the "space" requery, that was a hold over from when I was
figuring out how to do cascading combo boxes, the dependent combo box was
then not clearing the previous entry.

As for the code, I am getting an "Enter Parameter Value" message box, and
unfortunately I am not yet versed enough in VBA to troubleshoot well. What
needs fixing?

The virus field in the table is probably a Text field? If
that's true, then we need to add quotes around the combo
box's value:

Me!cboTest.RowSource = "SELECT Test FROM lstTests WHERE
Virus = '" & cboVirus & "' AND Test Like '* RRT-PCR' "

In the future, please check the parameter in the prompt
message. It tells you exactly what term in the query is not
defined and is an important clue about what might be the
problem. In this case, I am guessing that it is 'AIV'.
Also, post a Copy/Paste of the exact code so we can see what
you are really using.

 

[Guest]

The quotes are what I needed, thanks Marsh.

But now, the successive combo box is not working, cboItem is not being
limited by the test chosen in cboTest.

Private Sub cboTest_AfterUpdate()

Me!cboItem.RowSource = "SELECT lstItems.Item FROM lstItems WHERE
lstItems.Test = cboTest.value"

End Sub


Private Sub cboVirus_AfterUpdate()

Me!cboTest.RowSource = "SELECT Test FROM lstTests WHERE Virus = '" &
cboVirus & "' AND Test Like '*RRT-PCR' "

End Sub

 

[Marshall Barton]

The quotes are what I needed, thanks Marsh.

But now, the successive combo box is not working, cboItem is not being
limited by the test chosen in cboTest.

Private Sub cboTest_AfterUpdate()
Me!cboItem.RowSource = "SELECT lstItems.Item FROM lstItems WHERE
lstItems.Test = cboTest.value"
End Sub


Private Sub cboVirus_AfterUpdate()
Me!cboTest.RowSource = "SELECT Test FROM lstTests WHERE Virus = '" &
cboVirus & "' AND Test Like '*RRT-PCR' "
End Sub

Note how the value of the combo box is inserted into the
query. To use the value inside the quotes, you need to use
a full form/control reference:

Me!cboItem.RowSource = "SELECT lstItems.Item FROM lstItems
WHERE lstItems.Test = Forms!nameof form.cboTest.Value"

Now note the alternative that I used in the virus combo box
and adapt that to the other combo. Since the Test field is
Text you also need to add the quotes as we did before:

Me!cboItem.RowSource = "SELECT lstItems.Item FROM lstItems
WHERE lstItems.Test = '" & cboTest.value & "' "

 

[Guest]

Thanks Marsh,

I had a few issues implementing your code, but in the end managed to get it
working.

AG