Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

DLookup/Combo Box criteria from another table

Status
Not open for further replies.

BCre8iv

Programmer
May 21, 2002
28
CA
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
 
In the subform's ProductID_AfterUpdate event procedure, you need something like this:
Me!CustomerPrice = DLookup(Me!CustomerType, _
"Products", "ProductID='" & Me!ProductID & "'")
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:
Code:
    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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top