Returning a value based on two combo boxes

J

JLS04

I'm working with Access 2002. I was wondering if anybody could provide
guidance on how to set up a dialogue box (dialogue box may not be the right
term), in a form that automatically returns a value from a table based on
answers chosen in two other combo boxes.
 
J

John Vinson

I'm working with Access 2002. I was wondering if anybody could provide
guidance on how to set up a dialogue box (dialogue box may not be the right
term), in a form that automatically returns a value from a table based on
answers chosen in two other combo boxes.
Click to expand...

I'm not sure this is exactly what you mean, but you can create a Query
using form references as criteria. First create a small unbound Form
(frmCrit say) with your two combo boxes on it. Then, create a new
Query based on the table that you wish to search; on the criteria
under the fields you're searching put

=[Forms]![frmCrit]![comboboxname]

under each searched field.

It's best to base a Report (for printing) or Form (for onscreen
display) on this Query; put a command button on frmCrit to open it.

If you want to just display one single value, you can do it in a
different way. Put a textbox on the Form containing the combo boxes
with a Control Source

=DLookUp("[fieldname]", "[tablename]", "[searchfield1] = " &
[cboField1] & " AND [searchfield2] = " & [cboField2])

using your own object names of course. You may need to Requery the
textbox in the AfterUpdate event of each of the combo boxes.

John W. Vinson[MVP]
 
J

JLS04

John, thanks for your response. Your second idea seems very close to what
I'm trying to do. I have a user making a selection from one query box, then
based on the selection in that combo box, they make a selection in a second
combo box. My idea is to have a single value returned from a table based on
the answers in the two combo boxes. I think using the textbox and the
control source you showed me will work, I'm just having a little trouble
filling in the blanks you mentioned. Specifically, [searchfield1] and
[searchfield2] - what should those reference? Also, [cboField1] and
[cboField2] - I'm assuming those are references to the two combo boxes I'm
using, is that correct? Again, thanks very much for your reply!

John Vinson said:
I'm working with Access 2002. I was wondering if anybody could provide
guidance on how to set up a dialogue box (dialogue box may not be the right
term), in a form that automatically returns a value from a table based on
answers chosen in two other combo boxes.
Click to expand...

I'm not sure this is exactly what you mean, but you can create a Query
using form references as criteria. First create a small unbound Form
(frmCrit say) with your two combo boxes on it. Then, create a new
Query based on the table that you wish to search; on the criteria
under the fields you're searching put

=[Forms]![frmCrit]![comboboxname]

under each searched field.

It's best to base a Report (for printing) or Form (for onscreen
display) on this Query; put a command button on frmCrit to open it.

If you want to just display one single value, you can do it in a
different way. Put a textbox on the Form containing the combo boxes
with a Control Source

=DLookUp("[fieldname]", "[tablename]", "[searchfield1] = " &
[cboField1] & " AND [searchfield2] = " & [cboField2])

using your own object names of course. You may need to Requery the
textbox in the AfterUpdate event of each of the combo boxes.

John W. Vinson[MVP]
Click to expand...
 
J

John Vinson

John, thanks for your response. Your second idea seems very close to what
I'm trying to do. I have a user making a selection from one query box, then
based on the selection in that combo box, they make a selection in a second
combo box. My idea is to have a single value returned from a table based on
the answers in the two combo boxes. I think using the textbox and the
control source you showed me will work, I'm just having a little trouble
filling in the blanks you mentioned. Specifically, [searchfield1] and
[searchfield2] - what should those reference? Also, [cboField1] and
[cboField2] - I'm assuming those are references to the two combo boxes I'm
using, is that correct? Again, thanks very much for your reply!
Click to expand...

The searchfieldX names refer to whatever fields in your table you're
trying to search using the combo boxes; you're correct about the
cboFieldX - that's just the Name property of the combo.

John W. Vinson[MVP]
 
J

JLS04

John - I'm a slow learner! I've got to be close on this thing but something
just isn't clicking. I put the code in the data/control source as you
recommended and the syntax must be correct because I don't get any errors,
but when I run the form, I'm I get "error#" I've taken enough of your time
already, but if you had any additional thoughts, I would be very greatful.
Here are a couple specifics:

Formula in control source: =DLookUp(" [LOB - All Companies]![Commission] ","
[LOB - All - Companies] "," [LOB - All Companies]![Company] = " & [Combo4] &
" AND [LOB - All Companies]![LOB] = " & [Combo2])

Where:
* "LOB - All Companies" is the table I'm using
* "Commission" is the field in the table LOB - All Companies I want to
return in the text box
* "Company" and "LOB" are the searchfields, both in the table "LOB - All
Companies"
* "Company", "LOB", and "Commission" are the only columns in the table LOB
- All Companies
* "Combo2" and "Combo4" are where the user selects a company and LOB and
the criteria used in the "commission" search
* The "Combo4" box is where the user selects a company and uses a different
table than "LOB - All Companies" one with a list of companies only
* The "Combo2" box is based on the selection made in "Combo4" and is where
you select the "LOB" associated with that company

Thanks again!

John Vinson said:
John, thanks for your response. Your second idea seems very close to what
I'm trying to do. I have a user making a selection from one query box, then
based on the selection in that combo box, they make a selection in a second
combo box. My idea is to have a single value returned from a table based on
the answers in the two combo boxes. I think using the textbox and the
control source you showed me will work, I'm just having a little trouble
filling in the blanks you mentioned. Specifically, [searchfield1] and
[searchfield2] - what should those reference? Also, [cboField1] and
[cboField2] - I'm assuming those are references to the two combo boxes I'm
using, is that correct? Again, thanks very much for your reply!
Click to expand...

The searchfieldX names refer to whatever fields in your table you're
trying to search using the combo boxes; you're correct about the
cboFieldX - that's just the Name property of the combo.

John W. Vinson[MVP]
Click to expand...
 
J

John Vinson

John - I'm a slow learner! I've got to be close on this thing but something
just isn't clicking. I put the code in the data/control source as you
recommended and the syntax must be correct because I don't get any errors,
but when I run the form, I'm I get "error#" I've taken enough of your time
already, but if you had any additional thoughts, I would be very greatful.
Here are a couple specifics:

Formula in control source: =DLookUp(" [LOB - All Companies]![Commission] ","
[LOB - All - Companies] "," [LOB - All Companies]![Company] = " & [Combo4] &
" AND [LOB - All Companies]![LOB] = " & [Combo2])
Click to expand...

A couple of syntax errors, is all. You don't need the table name in
the first argument; you don't need the blanks; and you DO need the
syntactically required quotes if either [Company] or [LOB] are Text
fields. Assuming that they both are Text, try

=DLookUp("[Commission]", "[LOB - All - Companies]",
"[Company] = '" & [Combo4] & "' AND [LOB] = '" & [Combo2] & "'")

If either field is Number (i.e. a foreign key numeric value) leave off
the ' before and the "'" after.

If Company might contain an apostrophe use "" in place of the ' before
and afterL

"[Company] = """ & [Combo4] & """ AND...


John W. Vinson[MVP]
 
J

JLS04

Hey John - it took me a couple days to get back to this but when I did, your
instructions worked like a charm! Thanks your your help!!

John Vinson said:
John - I'm a slow learner! I've got to be close on this thing but something
just isn't clicking. I put the code in the data/control source as you
recommended and the syntax must be correct because I don't get any errors,
but when I run the form, I'm I get "error#" I've taken enough of your time
already, but if you had any additional thoughts, I would be very greatful.
Here are a couple specifics:

Formula in control source: =DLookUp(" [LOB - All Companies]![Commission] ","
[LOB - All - Companies] "," [LOB - All Companies]![Company] = " & [Combo4] &
" AND [LOB - All Companies]![LOB] = " & [Combo2])
Click to expand...

A couple of syntax errors, is all. You don't need the table name in
the first argument; you don't need the blanks; and you DO need the
syntactically required quotes if either [Company] or [LOB] are Text
fields. Assuming that they both are Text, try

=DLookUp("[Commission]", "[LOB - All - Companies]",
"[Company] = '" & [Combo4] & "' AND [LOB] = '" & [Combo2] & "'")

If either field is Number (i.e. a foreign key numeric value) leave off
the ' before and the "'" after.

If Company might contain an apostrophe use "" in place of the ' before
and afterL

"[Company] = """ & [Combo4] & """ AND...


John W. Vinson[MVP]
Click to expand...
 
Top