Query based on combo box values

[Guest]

I have 3 combo boxes on a form that users pick numeric values for. Based on
those 3 values, when a user clicks a button, I want to run a query or lookup
a dollar amount from another table for that combination of values. So for
example, I have combo box 1,2,and 3, and text box 1. If combo box 1 has a
value of 1, combo box 2 has a value of 1, combo box 3 has a value of 1, then
it looks up a dollar amount in another table for those values and places the
amount in text box 1 (i.e. $525--stored as numeric (525)). If combo box 1
has a value of 1, combo box 2 has a 1, and combo box 3 has a 2, it may return
$600). Any help would be greatly appreciated. Thanks

 

[Graham Mandeno]

Hi Bill

Use the values in the combo boxes to construct a WHERE clause, then use
DLookup. In your button's Click event procedure:

Dim sWhere as string
sWhere = "[Field1]=" & Combo1 & " and [Field2]=" & Combo2 _
& " and [Field3]=" & Combo3
Text1 = DLookup( "[PriceField]", "YourTable", sWhere )

 

[Larry Linson]

Bill,

Perhaps the example entitled "QueryByForm with Three Combo Boxes" would be
helpful. You can download it from http://accdevel.tripod.com/dwnindx.htm.

Larry Linson
Microsoft Access MVP