String for Multiple filters

[tl mike]

I am trying to setup a orders db and it has customers with different price levels when they place the order I want to save the current price for the item in the order details table.

I know how to write a string for a single filter but how would I do it to filter both the Product ID and the Price Level ID to get the customers price

I currently have:
strFilter = "ProductID = " & Me!ProductID

Me!UnitPrice = DLookup("Price", "tblPrice", strFilter)

The table where the prices are saved is tblPrice and the price level field is named PriceLevel and on the order form there is a text box that shows the customers price level name txtPriceLevel.

 

[dcb]

strFilter = "ProductID = " & Me.ProductID & " AND " & " PriceLevelID = " & Me.PriceLevelID

 

[dcb]

Sorry didnt read correctly

strFilter = "ProductID = " & Me.ProductID & " AND " & " PriceLevelID = " & Me.txtPriceLevel

if txtPriceLevel = PriceLevelID if its a combo box or do aa Dlookup to ref the PriceLevelID from the txtPriceLevel

 

[tl mike]

Thank you for the reply!!

I am still having troubles it is not pulling up the price after the Product is updated

Private Sub ProductID_AfterUpdate()
On Error GoTo Err_ProductID_AfterUpdate
Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductFK = " & Me.ProductFK & " AND " & " PriceLevelFK = " & Me.PriceLevelFK

' Look up product's unit price and assign it to UnitPrice control.
Me!UnitPrice = DLookup("Price", "tblPriceCurrent", strFilter)


Exit_ProductID_AfterUpdate:
Exit Sub
Err_ProductID_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductID_AfterUpdate
End Sub

 

[dcb]

only after the product is updated?

Try debug.print strFilter and see if you can see something that looks wrong

 

[boblarson]

And are both FK values numeric or are any of them text?

 

[tl mike]

Here is a broken down sample of the DB

I am in the process of changing the racers code to be the same as the price code, as before when the original db was setup it was only for racers and we had another built for our products and now we want to track what our racers purchases.

 

[boblarson]

Two things:

1. Get rid of the lookups at table level.

2. Your combo box is now named PartNumberFK but the AfterUpdate event is for ProductID - I think you renamed the control but didn't move the code to the right named after update event.