Selecting Record with Combo Box 1

[tomhughes]

I am trying to use a combo box which contains Field2 data of TableName to select a record from Field1 and put it into a TextBox. Here is the code I am trying to use.
Can anyone please help me ??

Private Sub Combo11_Click()
Dim dbs As Database, rst As Recordset, strSQL As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("TableName")
strSQL = "SELECT Field1 FROM TableName where Field2= """ & Me.Combo11 & """"
Set rst = dbs.OpenRecordset(strSQL)
rst.MoveFirst
Me.TextBox = rst
End Sub

 

[dhookom]

You would need to specify the field from the recordset

   Me.TextBox = rst.Fields(0)

You should be more explicit with your declarations:

  Dim dbs As DAO.Database, rst As DAO.Recordset, strSQL As String

If Combo11 has a row source based on "TableName" then you could possibly include Field1 in the Row Source and use something like:

  Me.TextBox = Me.combo11.Column(1)

Otherwise, I would try

Private Sub Combo11_Click()
   Me.TextBox = DLookup("Field1","tableName","Field2= """ & Me.Combo11 & """")
End Sub

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[tomhughes]

Thanks dhookom - I really appreciate your help, and your quick response.