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

Question

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 gets 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

How about uploading a small database in which we can see what you've done? So what do we know about the form you have created "against" table SALES? — Friedrich
Just want to clarify one thing for now: Why does the SALES table contain the Price column? Does it mean that the price at which a product is sold can be different from the price recorded for the product in the STOCK table? — Yarik

Ould Abba Ould Abba · Accepted Answer · 2012-03-01T12:21:31

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

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

1 Answers