RecordSet based on textbox value 2

[glenmac]

I need to get a value based on the contents of a text box. I've tried this code to no avail. Can anyone see my error?
All help is greatly appreciated.

Private Sub Form_Load()
Text1 = currentDB.openrecordset(SELECT Single_Small_Price FROM Price WHERE Product_ID= me!List9)
End Sub

 

[Norris68]

You cannot reference the Me object. It would be better to build your string instead of relying on the query engine to look it up. (Plus, you forgot to put your SQL statement in quote marks)

Private Sub Form_Load()
Text1 = currentDB.openrecordset("SELECT Single_Small_Price FROM Price WHERE Product_ID=" & me!List9)
End Sub

However, the Access DLookup function is built to do just this sort of thing

Private Sub Form_Load()
Text1 = DLookup("Single_Small_Price", "Price", "Product_ID=" & me!List9)
End Sub

 

[Norris68]

Sorry, just spotted another error (that I reproduced - tut tut!) - you are trying to assign a Recordset object to a Field. Solution:

Private Sub Form_Load()
Dim rs As Recordset
Set rs = currentDB.openrecordset("SELECT Single_Small_Price FROM Price WHERE Product_ID=" & me!List9)
Text1 = rs("Single_Small_Price")
rs.Close
Set rs = Nothing
End Sub

 

[glenmac]

Thank you very much a Star!! for you.