Multiple price selection in subform

[mdrogan]

Forms: Products, Customers, Invoice,
Subform: Invoice Details.

Customers are categorized as Wholesale, Retail, & Distributor
Products have three price levels. Wholesale, Retail & Distributor.

In the main form Invoice, I have a combo lookup of CustomerID with a price level column.

In the Subform on the After Update of ProductID Combo I have:

Private Sub ProductID_AfterUpdate()

On Error GoTo Err_ProductID_AfterUpdate

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductID = " & Me!ProductID

' Look up Item's unit price and assign it to UnitPrice control.
Me!UnitPrice = DLookup("RetailPrice", "Product Line", strFilter)
Me!ProductID = DLookup("ProductID", "Product Line", strFilter)

Exit_ProductID_AfterUpdate:
Exit Sub

Err_ProductID_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductID_AfterUpdate

End Sub


How would I alter this so that, upon selecting customer in main form. The subform unit price will equal the price level indicated by the customer?

If the Customer ID equals wholesale then UnitPrice equals wholesale price of product column.

I have been copying, pasting and basically modifying but I don't know how to write this code. I can't seem to find examples either.

Thanks in advance.

Mary

 

[DBL]

How about a Select Case for the price level.

Select Case Me.LevelIDfield
case 1 'Equivalent ID Number for Wholesale
Me!UnitPrice = DLookup("WholesalePrice", "Product Line", strFilter)
Me!ProductID = DLookup("ProductID", "Product Line", strFilter)

case 2 'Equivalent ID Number for Retail
Me!UnitPrice = DLookup("RetailPrice", "Product Line", strFilter)
Me!ProductID = DLookup("ProductID", "Product Line", strFilter)

case 3 'Equivalent ID Number for Distrubter
Me!UnitPrice = DLookup("DistributerPrice", "Product Line", strFilter)
Me!ProductID = DLookup("ProductID", "Product Line", strFilter)

End Select

I haven't tested this so you may need to do some fiddling

I've also assumed that the Price Levels are taken from a separate table and have an unique ID number which is stored in the customer table. If not you would do

Case "Wholesale" etc


[This message has been edited by DBL (edited 03-18-2002).]

[This message has been edited by DBL (edited 03-18-2002).]

 

[mdrogan]

Ok, sounds good so far.

But how would I refer to the PriceLevel ID in my bound CustomerID combo box on my main form Invoice so that the in the ProductID pricelevel/case is derived from that value of the main form combo?

Thanks.

 

[DBL]

How many columns does your combo have? Say it's 3, the customerID (column 0), customername(column 1), pricelevel(column 2) It would be

Select Case Me.Comboname.column(2) 'Pricelevel column

Case 1 'the ID number for the price level

See how you get on with that

[This message has been edited by DBL (edited 03-19-2002).]

 

[mdrogan]

Ok, This is what I've got so far.

Private Sub ProductID_AfterUpdate()

On Error GoTo Err_ProductID_AfterUpdate

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductID = " & Me!ProductID

Select Case [Forms]![Sales Invoice]![PriceLevel]

Case 1 'Retail
Me!UnitPrice = DLookup("Retail", "Product Line", strFilter)
Me!ProductID = DLookup("ProductID", "Product Line", strFilter)

Case 2 'Wholesale
Me!UnitPrice = DLookup("Wholesale", "Product Line", strFilter)
Me!ProductID = DLookup("ProductID", "Product Line", strFilter)

Case 3 'Distributor
Me!UnitPrice = DLookup("Distributor", "Product Line", strFilter)
Me!ProductID = DLookup("ProductID", "Product Line", strFilter)

End Select

Exit_ProductID_AfterUpdate:
Exit Sub

Err_ProductID_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductID_AfterUpdate

End Sub

But it's not returning any value. Any help would be appreciated.