Use two combo boxes to control a third

[WestWingFan]

I have a form and a subform. In the subform is a combo box (#3). I want to
filter the list of available options for this combo box based on the
selections made on two other combo boxes (#1) and (#2). I was able to get
this to work by putting the code as a condition on the query for combo box #3
when all three combo boxes were on the same subform. But I wanted combo box
#1 on the main form, while I kept combo #2 and #3 on the subform. I can get
combo box #3 to respond to the criteria found in Combo #1, but not both #1
and #2. Is this even possible? Thanks in advance for the help!

 

[n00b]

If I wanted to do that I would create an after update event for combo
#1 and #2. I would place some code in each event that sets the row
source of combo #3.

Something like this for Combo 1 (main form):

MySubform.Form.Combo3.RowSource = "SELECT * FROM MyTable WHERE Field1
= '" & Combo1 & "' AND Field 2 = '" & Combo2 & "'"


Something like this for combo 2 (subform):

Combo3.RowSource = "SELECT * FROM MyTable WHERE Field1 = '" &
Parent.Combo1 & "' AND Field 2 = '" & Combo2 & "'"


Of course without specific details, I cannot tell you exactly what to
do.

 

[WestWingFan]

Ok. I put this code in the after update event for Combo 1 (main form):

Me![sbfrmBmkSelector]![cboCompBmkSelect].RowSource = "SELECT * FROM
tblCompBenchmarks WHERE SubjectID= '" & cboCompSubjectSelect & "' AND
LevelOrder= '" & cboCompLevelSelect & "'"

and this in the after update event for Combo 2 (subform):

cboCompBmkSelect.RowSource = "SELECT * FROM tblCompBenchmarks WHERE
SubjectID= '" & cboCompSubjectSelect & "' AND LevelOrder= '" &
cboCompLevelSelect & "'"

When I test it, it comes back with an error off of Combo 1 (form) that
"cboCompLevelSelect" is undefined. Do I need to put
Me![sbfrmBmkSelector]![cboCompBmkSelect] there instead?

 

[n00b]

Here's the correction for Combo 1:

Me.sbfrmBmkSelector.Form.cboCompBmkSelect.RowSource = "SELECT * FROM
tblCompBenchmarks WHERE SubjectID= '" & cboCompSubjectSelect & "' AND
LevelOrder= '" & Me.sbfrmBmkSelector.Form.cboCompLevelSelect] & "'"

It sounds like cboCompLevelSelect is on the subform...


Combo 2 should be:

cboCompBmkSelect.RowSource = "SELECT * FROM tblCompBenchmarks WHERE
SubjectID= '" & Parent.cboCompSubjectSelect & "' AND LevelOrder= '" &
cboCompLevelSelect & "'"

Ok. I put this code in the after update event for Combo 1 (main form):

Me![sbfrmBmkSelector]![cboCompBmkSelect].RowSource = "SELECT * FROM
tblCompBenchmarks WHERE SubjectID= '" & cboCompSubjectSelect & "' AND
LevelOrder= '" & cboCompLevelSelect & "'"

and this in the after update event for Combo 2 (subform):

cboCompBmkSelect.RowSource = "SELECT * FROM tblCompBenchmarks WHERE
SubjectID= '" & cboCompSubjectSelect & "' AND LevelOrder= '" &
cboCompLevelSelect & "'"

When I test it, it comes back with an error off of Combo 1 (form) that
"cboCompLevelSelect" is undefined. Do I need to put
Me![sbfrmBmkSelector]![cboCompBmkSelect] there instead?

If I wanted to do that I would create an after update event for combo
#1 and #2.  I would place some code in each event that sets the row
source of combo #3.

Something like this for Combo 1 (main form):

MySubform.Form.Combo3.RowSource = "SELECT * FROM MyTable WHERE Field1
= '" & Combo1 & "' AND Field 2 = '" & Combo2 & "'"

Something like this for combo 2 (subform):

Combo3.RowSource = "SELECT * FROM MyTable WHERE Field1 = '" &
Parent.Combo1 & "' AND Field 2 = '" & Combo2 & "'"

Of course without specific details, I cannot tell you exactly what to
do.

- Show quoted text -

 

[WestWingFan]

Thanks for all the help! You are right cboCompLevelSelect is on the subform.
I used your corrected code. Now I get the error "Data type mismatch in
critera expression". Help! I'm not sure what do look at or trouble shoot now.

Here's the correction for Combo 1:

Me.sbfrmBmkSelector.Form.cboCompBmkSelect.RowSource = "SELECT * FROM
tblCompBenchmarks WHERE SubjectID= '" & cboCompSubjectSelect & "' AND
LevelOrder= '" & Me.sbfrmBmkSelector.Form.cboCompLevelSelect] & "'"

It sounds like cboCompLevelSelect is on the subform...


Combo 2 should be:

cboCompBmkSelect.RowSource = "SELECT * FROM tblCompBenchmarks WHERE
SubjectID= '" & Parent.cboCompSubjectSelect & "' AND LevelOrder= '" &
cboCompLevelSelect & "'"

Ok. I put this code in the after update event for Combo 1 (main form):

Me![sbfrmBmkSelector]![cboCompBmkSelect].RowSource = "SELECT * FROM
tblCompBenchmarks WHERE SubjectID= '" & cboCompSubjectSelect & "' AND
LevelOrder= '" & cboCompLevelSelect & "'"

and this in the after update event for Combo 2 (subform):

cboCompBmkSelect.RowSource = "SELECT * FROM tblCompBenchmarks WHERE
SubjectID= '" & cboCompSubjectSelect & "' AND LevelOrder= '" &
cboCompLevelSelect & "'"

When I test it, it comes back with an error off of Combo 1 (form) that
"cboCompLevelSelect" is undefined. Do I need to put
Me![sbfrmBmkSelector]![cboCompBmkSelect] there instead?

If I wanted to do that I would create an after update event for combo
#1 and #2. I would place some code in each event that sets the row
source of combo #3.

Something like this for Combo 1 (main form):

MySubform.Form.Combo3.RowSource = "SELECT * FROM MyTable WHERE Field1
= '" & Combo1 & "' AND Field 2 = '" & Combo2 & "'"

Something like this for combo 2 (subform):

Combo3.RowSource = "SELECT * FROM MyTable WHERE Field1 = '" &
Parent.Combo1 & "' AND Field 2 = '" & Combo2 & "'"

Of course without specific details, I cannot tell you exactly what to
do.

On Jan 16, 8:18 pm, WestWingFan
I have a form and a subform. In the subform is a combo box (#3). I want to
filter the list of available options for this combo box based on the
selections made on two other combo boxes (#1) and (#2). I was able to get
this to work by putting the code as a condition on the query for combo box #3
when all three combo boxes were on the same subform. But I wanted combo box
#1 on the main form, while I kept combo #2 and #3 on the subform. I can get
combo box #3 to respond to the criteria found in Combo #1, but not both #1
and #2. Is this even possible? Thanks in advance for the help!- Hide quoted text -

- Show quoted text -

 

[n00b]

Its probably due to the fact that we didn't wrapper the combo box
references in the code with the Nz function.

Me.sbfrmBmkSelector.Form.cboCompBmkSelect.RowSource = "SELECT * FROM
tblCompBenchmarks WHERE SubjectID= '" & Nz(cboCompSubjectSelect, -1) &
"' AND
LevelOrder= '" & Nz(Me.sbfrmBmkSelector.Form.cboCompLevelSelect, -1) &
"'"

Combo 2 should be:

cboCompBmkSelect.RowSource = "SELECT * FROM tblCompBenchmarks WHERE
SubjectID= '" & Nz(Parent.cboCompSubjectSelect, -1) & "' AND
LevelOrder= '" &
Nz(cboCompLevelSelect, -1) & "'"


Val() would probably work too.

Thanks for all the help! You are right cboCompLevelSelect is on the subform.
I used your corrected code. Now I get the error "Data type mismatch in
critera expression". Help! I'm not sure what do look at or trouble shoot now.

Here's the correction for Combo 1:

Me.sbfrmBmkSelector.Form.cboCompBmkSelect.RowSource = "SELECT * FROM
tblCompBenchmarks WHERE SubjectID= '" & cboCompSubjectSelect & "' AND
LevelOrder= '" & Me.sbfrmBmkSelector.Form.cboCompLevelSelect] & "'"

It sounds like cboCompLevelSelect is on the subform...

Combo 2 should be:

cboCompBmkSelect.RowSource = "SELECT * FROM tblCompBenchmarks WHERE
SubjectID= '" & Parent.cboCompSubjectSelect & "' AND LevelOrder= '" &
cboCompLevelSelect & "'"

Ok. I put this code in the after update event for Combo 1 (main form):
Me![sbfrmBmkSelector]![cboCompBmkSelect].RowSource = "SELECT * FROM
tblCompBenchmarks WHERE SubjectID= '" & cboCompSubjectSelect & "' AND
LevelOrder= '" & cboCompLevelSelect & "'"
and this in the after update event for Combo 2 (subform):
cboCompBmkSelect.RowSource = "SELECT * FROM tblCompBenchmarks WHERE
SubjectID= '" & cboCompSubjectSelect & "' AND LevelOrder= '" &
cboCompLevelSelect & "'"
When I test it, it comes back with an error off of Combo 1 (form) that
"cboCompLevelSelect" is undefined. Do I need to put
Me![sbfrmBmkSelector]![cboCompBmkSelect] there instead?
:
If I wanted to do that I would create an after update event for combo
#1 and #2.  I would place some code in each event that sets the row
source of combo #3.
Something like this for Combo 1 (main form):
MySubform.Form.Combo3.RowSource = "SELECT * FROM MyTable WHERE Field1
= '" & Combo1 & "' AND Field 2 = '" & Combo2 & "'"
Something like this for combo 2 (subform):
Combo3.RowSource = "SELECT * FROM MyTable WHERE Field1 = '" &
Parent.Combo1 & "' AND Field 2 = '" & Combo2 & "'"
Of course without specific details, I cannot tell you exactly what to
do.
On Jan 16, 8:18 pm, WestWingFan
I have a form and a subform. In the subform is a combo box (#3). Iwant to
filter the list of available options for this combo box based on the
selections made on two other combo boxes (#1) and (#2). I was ableto get
this to work by putting the code as a condition on the query for combo box #3
when all three combo boxes were on the same subform. But I wanted combo box
#1 on the main form, while I kept combo #2 and #3 on the subform. I can get
combo box #3 to respond to the criteria found in Combo #1, but notboth #1
and #2. Is this even possible? Thanks in advance for the help!- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[WestWingFan]

nOOb,

Thanks for hanging in here with me. I tried the Nz() function and got the
same error. val() gives a compilation error. Other ideas? Thanks in advance.

Its probably due to the fact that we didn't wrapper the combo box
references in the code with the Nz function.

Me.sbfrmBmkSelector.Form.cboCompBmkSelect.RowSource = "SELECT * FROM
tblCompBenchmarks WHERE SubjectID= '" & Nz(cboCompSubjectSelect, -1) &
"' AND
LevelOrder= '" & Nz(Me.sbfrmBmkSelector.Form.cboCompLevelSelect, -1) &
"'"

Combo 2 should be:

cboCompBmkSelect.RowSource = "SELECT * FROM tblCompBenchmarks WHERE
SubjectID= '" & Nz(Parent.cboCompSubjectSelect, -1) & "' AND
LevelOrder= '" &
Nz(cboCompLevelSelect, -1) & "'"


Val() would probably work too.

Thanks for all the help! You are right cboCompLevelSelect is on the subform.
I used your corrected code. Now I get the error "Data type mismatch in
critera expression". Help! I'm not sure what do look at or trouble shoot now.

Here's the correction for Combo 1:

Me.sbfrmBmkSelector.Form.cboCompBmkSelect.RowSource = "SELECT * FROM
tblCompBenchmarks WHERE SubjectID= '" & cboCompSubjectSelect & "' AND
LevelOrder= '" & Me.sbfrmBmkSelector.Form.cboCompLevelSelect] & "'"

It sounds like cboCompLevelSelect is on the subform...

Combo 2 should be:

cboCompBmkSelect.RowSource = "SELECT * FROM tblCompBenchmarks WHERE
SubjectID= '" & Parent.cboCompSubjectSelect & "' AND LevelOrder= '" &
cboCompLevelSelect & "'"

On Jan 22, 8:16 pm, WestWingFan
Ok. I put this code in the after update event for Combo 1 (main form):

Me![sbfrmBmkSelector]![cboCompBmkSelect].RowSource = "SELECT * FROM
tblCompBenchmarks WHERE SubjectID= '" & cboCompSubjectSelect & "' AND
LevelOrder= '" & cboCompLevelSelect & "'"

and this in the after update event for Combo 2 (subform):

cboCompBmkSelect.RowSource = "SELECT * FROM tblCompBenchmarks WHERE
SubjectID= '" & cboCompSubjectSelect & "' AND LevelOrder= '" &
cboCompLevelSelect & "'"

When I test it, it comes back with an error off of Combo 1 (form) that
"cboCompLevelSelect" is undefined. Do I need to put
Me![sbfrmBmkSelector]![cboCompBmkSelect] there instead?

:
If I wanted to do that I would create an after update event for combo
#1 and #2. I would place some code in each event that sets the row
source of combo #3.

Something like this for Combo 1 (main form):

MySubform.Form.Combo3.RowSource = "SELECT * FROM MyTable WHERE Field1
= '" & Combo1 & "' AND Field 2 = '" & Combo2 & "'"

Something like this for combo 2 (subform):

Combo3.RowSource = "SELECT * FROM MyTable WHERE Field1 = '" &
Parent.Combo1 & "' AND Field 2 = '" & Combo2 & "'"

Of course without specific details, I cannot tell you exactly what to
do.

On Jan 16, 8:18 pm, WestWingFan
I have a form and a subform. In the subform is a combo box (#3). I want to
filter the list of available options for this combo box based on the
selections made on two other combo boxes (#1) and (#2). I was able to get
this to work by putting the code as a condition on the query for combo box #3
when all three combo boxes were on the same subform. But I wanted combo box
#1 on the main form, while I kept combo #2 and #3 on the subform. I can get
combo box #3 to respond to the criteria found in Combo #1, but not both #1
and #2. Is this even possible? Thanks in advance for the help!- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[n00b]

It could be the misuse of single quotes. Fields that are numbers
don't need the quotes in the criteria. Fields that are strings do.

 

[WestWingFan]

That did the trick. Thank you so much for all of your help!

 

[n00b]

Yay! Your welcome, anytime.