[SOLVED] Outputting the result of an SQL query into a text box

Outputting the result of an SQL query into a text box

I'm creating a form that displays information about an individual that is taken from two tables when a name is entered by the user. I want the fields to be output into they're own text boxes. My code looks similar to what's below.

When I run the code it displays the literal query "SELECT name etc..." in the textbox. I saw Dlookup works for textboxes but to my understanding it doesn't work well with more than one table. Any advice would be greatly appreciated!

PS I'm a VBA/access newbie

Dim SQL, SearchInput As String

SQL = "SELECT name" & _
      "FROM tablename INNER JOIN othertablename ON tablename.name = othertablename.name" & _
      "WHERE  tablename.name LIKE ""*" & SearchInput & "*""

Me.txtbox = SQL

Solution

I'm pretty sure this is a duplicate, but it's faster to answer than hunt for the other posts.

You need to declare a recordset and assign the data returned from the select statement to it. This will leave you with something very similar to an array. After that you need to just line up the array element to the positions of the columns. IE. rs(0)=name in the select statement above.

Dim rs As Recordset
Dim SQL As String, SearchInput As String
SQL = "SELECT name " & _
  "FROM tablename INNER JOIN othertablename ON tablename.name = othertablename.name " & _
  "WHERE tablename.name LIKE ""*" & SearchInput & "*""
   Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

Me.txtBox = rs(0)

That should work.