Text box value from SQL statement 1

[denright]

I have a form that is bound to a table called ContactHistory. For new records, I would like to populate a text box (txtPersonID) on the form using a query that takes data from another table (Person).

I tried this:

sqlPersonID = "SELECT DISTINCTROW PERSON.PersonID " & _
"FROM PERSON " & _
"WHERE PERSON.First & "" "" & PERSON.Last = cboClientName.Value;"
txtPersonID = sqlPersonID

The query itself works but I can't populate the textbox because txtPersonID is a long and sqlPersonID is a string.

How can I get the value of that query into the text box?

 

[wemeier]

Try txtPersonID = CLng(sqlPersonID). BTW, how come txtPersonID is a long datatype? "txt" is the common prefix for text fields.

 

[PaulBricker]

If you print sqlPersonID to the Debug window you will see it looks like this

SELECT DISTINCTROW PERSON.PersonID FROM PERSON WHERE PERSON.First & " " & PERSON.Last = Paul Bricker
Thats what sqlPersonID is being set to, not just the value Paul Bricker.
To do what you want, you will need to use the DLookUp() Function or actually open a recordset based on sqlPersonID. I think the DLookUp would be easier.
Set the control source for txtPersonID to
=DLookUp("PersonID","Person","[First]& ' ' & [Last] = '" & cboClientName & "'&quot

You may need to adjust the syntax for the Where arugument. I don't know how the data in cboClientName is set up. If it's in two columns your syntax will be different.

Paul

 

[denright]

DLookup worked perfectly. Thanks!