I've got this code:

Private Sub Update_Click()
Dim strSQL As String
Me.PartNumber.SetFocus
strSQL = "SELECT [Cost] FROM [ItemsTbl] WHERE PartNumber='" & Me.PartNumber & "';"
MsgBox strSQL
Me.Cost.SetFocus
Cost = strSQL
End Sub

When I execute it, the message box attached shows up. This message box is supposed to have a $value, not the text shown. I am sure that I am missing something in the query line, but what?

mafhobb

it's showing exactly what you asked for: the string strSQL
you have to run the sql to get the result.

uhmm...

So how do I do that?

mafhobb

I am just not familiar with SQL queries in code...

recordsets.
set the database
set the recordset as part of the db
open the rs with the strSql
supply the result to the function (or variable).
- recordset.Fields(0) is the first field in the query. since there is only one field you can use this.

fGetCost
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String
strSql = "SELECT [Cost] FROM [ItemsTbl] WHERE PartNumber='" & Me.PartNumber & "';"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSql, dbOpenForwardOnly)

fGetCost = rs.Fields(0)
'Debug.Print fGetCost
Msgbox fGetCost

rs.Close
Set rs = Nothing
Set db = Nothing

That works!

Thank you Wazz

mafhobb

yoor velcome.