Lookup Customer And Case Price

[sideswipe]

Can anyone help please. I have some code which when a product is entered in an order table the after update event looks up the price in the products table and enters the case price automatically. The code is as follows.

Dim strFilter As String

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

' Look up product's case price and assign it to case Price control.
Me!CASEPRICE = DLookup("caseprice", "Products", strFilter)

This however is not sufficient for the purpose I require. Since I started the project things!! keep getting added. Now we need each customer to have their own unique price list.

Therefore, the system needs to look up the customeraccountid in orders, then the customeraccountid, product id and the caseprice from the customerprices table in order to match them all and automatically enter this caseprice into the caseprice field on the orders form for the specific customer.

Has anyone got any ideas. i'm no programmer.

Dan

 

[Bastien]

Hi,

You might want to stop using the DLookUp Function and switch to SQL for these queries. It will be easier to program and you can have as many SQL statements as you want. There is a limit on DLookUp, though I can't remember what it is off hand.

use the recordset functionality to pass the values along...
SQL1 = select Price from CaseTable where caseID = ItemID

this picks the price of the case from the table



SQL2 = insert into SaleTable(CasePrice) values (Price) where saleID = SalesNo and ItemId = ItemPicked

puts the princ into the sales table...
You will need to adjust this to match your table names and field names...you can get agood overview on how the recordset works by setting up a switchboard, adapt and use that to meet what you want to do...
hth Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[sideswipe]

thanks for your reply
i didnt know you could use sql in forms. I thought you could only use vba.

Im okay getting the data input but the function i need to do is as follows.

When the user goes to enter a new order they input the customer the order date etc and then move onto the product. When they enter the product this is when the function should initiate (the after update event control on the vba screen)by looking to the tables via the code and put the specific price in the caseprice control on the form thus eliminating the need to have price lists on every desk. Is this any clearer.
regards
dan