Show query results in text field?

[sandman2869]

I have a form with a combo box and a text field, after the user makes a selection in the combo box, I would like to have the text box show the results from a query I made that runs off of the combo box selection. Is this possible at all?

 

[B827]

Yes,

I don't know how you've created the query but the following should work, either as an AfterUpdate event or on a button click

Set MyRst = MyDb.OpenRecordset("SQLQueryName", dbOpenDynaset)
MyRst.MoveFirst
Me!TextBoxName = MyRst!SourceFieldName
MyRst.close
Sandy

 

[sandman2869]

Sandy,
Thanks for your reply, however, I am getting an error after cut/pasting your code. It gives me a "variable not defined" error. I am hoping that you might be able to help explain this for me.
I created the query using SQL, it works fine when I run it seperately, but for some reason I cannot get the text box to show the result of the query.

 

[B827]

Sandman
sorry the full listing (which you may need to enhance) is:

Dim MyDb As Database, MyRst As Recordset, SQL As String
Set MyDb = CurrentDb
SQL = "Select ..... where ..... '" & Me!ComboName & "';"
Set MyRst = MyDb.OpenRecordset(SQL, dbOpenDynaset)
MyRst.MoveFirst
Me!TextBoxName = MyRst!SourceFieldName
MyRst.close

If your search field is Text (not Numeric) then you need the ' if it is numeric then you don't. Sandy