how to look up values in form based on 2 field

[Guest]

Hi

The table consist of PO, PartNo and Qty fields. Which PO and PartNo is the
primary keys.
i have 2 combo box (PO , PartNo ) and a textbox in a form. I will like to
have a textbox(Qty) that automatically display the values base on the value
of 2 combo boxes.
How would I go about doing this? Any suggestions?
Dlookup ?

Thanks

 

[Guest]

Hi SeeKY,

You should be able to use a DLookup such as the following:

txtQty = DLookup("[Qty]", "[InsertTheTableNameHere]", "[PO]='" & cboPO & "'
AND [PartNo]='" & cboPartNo & "'")

This is assuming that PO and PartNo are stored as strings in your database
table. If not, they remove the single quotes which wrap the controls being
called.

Try avoiding naming your controls the same as a field that is being
referenced in a method call. Instead, try prefixing the control name with
something like 'txt' to differentiate it (i.e. txtQuantity).

Hope that helps.

Lance

 

[Guest]

In case it is not found in the table:

txtQty = Nz(DLookup("[Qty]", "[InsertTheTableNameHere]", "[PO]='" & cboPO &
"'
AND [PartNo]='" & cboPartNo & "'"),0)

It will return 0 if the lookup is not successful. DLookup returns Null on a
no hit, and the Nz() function will convert the Null to a 0.

Hi SeeKY,

You should be able to use a DLookup such as the following:

txtQty = DLookup("[Qty]", "[InsertTheTableNameHere]", "[PO]='" & cboPO & "'
AND [PartNo]='" & cboPartNo & "'")

This is assuming that PO and PartNo are stored as strings in your database
table. If not, they remove the single quotes which wrap the controls being
called.

Try avoiding naming your controls the same as a field that is being
referenced in a method call. Instead, try prefixing the control name with
something like 'txt' to differentiate it (i.e. txtQuantity).

Hope that helps.

Lance

Hi

The table consist of PO, PartNo and Qty fields. Which PO and PartNo is the
primary keys.
i have 2 combo box (PO , PartNo ) and a textbox in a form. I will like to
have a textbox(Qty) that automatically display the values base on the value
of 2 combo boxes.
How would I go about doing this? Any suggestions?
Dlookup ?

Thanks