how to insert a custom(run time) value in a form in MS Access

i have 2 tables in Microsoft Access

1)SALES containing columns

product and product price

2)STOCK containing columns

product ,price and quantity

i have created a form against the table SALES

i want that when i enter the product name in the product column it automatically get开发者_C百科s the price from the STOCKS table against the product i have entered.

i may have to enter like 10 products name and the add the price of each to get the TOTAL SALE value ( i am making a Point Of Sale system )

how can this be achieve

thanks

Assuming productField an priceField are fields in your form, you have to add this code to the on exit event of productField

If Not prodChanged Then exit sub ' if the user hasn't edited the product
Dim recSet As Recordset
Set recSet = CurrentDb.OpenRecordset("Select stock.price from stock where stock.product =" & Me.productField.Value)
If Not recSet.EOF Then 'If the product exists
    Me.priceField = recSet.Fields(0)
Else
    'do default action
End If
podChanged=false 'reset the product editted state

you have to declare the global boolean variable prodChanged initialized to false and set it true in the OnChange of productField