DLookup/Combo Box criteria from another table

[BCre8iv]

Hi there,
DLookup is new to me but from what I understand it is probably my best bet. I have a Products table that has three different prices, Key is ProductID.
Prices: Retail, Wholesale1, and Wholesale2.
Now I have a customers table where the user selects the CustomerType using a combo box. CustomerType: Retail, Wholesale1 and Wholesale2.
Now when the user does up an order he selects the customer from the customers table, customer information is drawn into the Orders form, and selects the product in a subform on the order form from the product table.
What I need help with is getting the right price to draw into a field on the subform called CustomerPrice. This price is based on the product the user picks and the CustomerType. Any help would be greatly appreciated?
Thx. Tina

 

[RickSpr]

In the subform's ProductID_AfterUpdate event procedure, you need something like this:
Me!CustomerPrice = DLookup(Me!CustomerType, _
"Products", "ProductID='" & Me!ProductID & "'&quot
This depends on the CustomerType field's value being identical (except for case) to the name of the desired price field in the Products table.

If the CustomerType value is different from the field name, you need to translate it as follows:

    Dim strField As String
    Select Case Me!CustomerType
        Case "Retail"
            strField = "Retail"
        Case "Wholesale 1"
            strField = "Wholesale1"
        Case "Wholesale 2"
            strField = "Wholesale2"
    End Case
    If strField <> &quot;&quot; Then
        Me!CustomerPrice = DLookup(strField, &quot;Products&quot;, _
            &quot;ProductID='&quot; & Me!ProductID & &quot;'&quot;)
    Else
        Me!CustomerPrice = Null
    End If

Note: Both of these will set CustomerPrice to Null if the customer type isn't already set when the product is selected. You may want to do something different in such a case. Rick Sprague