Using sql in form

[Guest]

I am trying to use sql code with 2 variables updated from 2 fields out of my
form and subform. I have a customer and a stock tbl with a price grid tbl.

The sql code must use cust name variable and stock code variable to run the
query in the price grid to get the price of item for specific customer and
return this value to the price field on the invoice sub form.

This is what i have done sofar but my brain seems to have left me.

PLs help

Sub calccost()
Dim sql As String
Dim cost As Currency
Dim cust As String
Dim stockitem As String
Dim value As Currency

cust = Forms!Frminvoice.Customer
stockitem = Me.Inventory_Itemcode
sql = "SELECT TblPricematrix.Price" & _
"FROM TblStock_master INNER JOIN (TblCustomers INNER JOIN
TblPricematrix ON TblCustomers.ID = TblPricematrix.Customer)" & _
"ON TblStock_master.ID = TblPricematrix.[Stock item]" & _
"WHERE (((TblPricematrix.Customer)" _
& "= # " & cust & " # ) AND ((TblPricematrix.[Stock item])" _
& "= # " & stockitem & " # ));"
[Price] = docmd.RunSQL sql
End Sub

 

[Arvin Meyer]

You have to open a recordset based upon your SQL statement. Then set your
form variables = to the resulting row of data returned to the recordset.
Also "#" deliminators are only used for dates. Strings are surrounded by
quotes.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access