Problem with AfterUpdate - Field shows code, not answer

[r0nniem]

I'm wanting to select a [name] on the form, and it to automatically fill in the [acro]. I've added a msg so I can see what it's doing and it's showing the code "SELECT [Contractors Table].acro FROM [Contractors Table]WHERE(([Contractors Table].Name)=[forms]![frmcmcstaff]![cmbname]);", not the answer. Can you help me out please?

Private Sub cmbName_AfterUpdate()
Dim dbs As DAO.Database, rst As DAO.Recordset, strSQL As String

Set dbs = CurrentDb

strSQL = "SELECT [Contractors Table].acro " & _
"FROM [Contractors Table]" & _
"WHERE(([Contractors Table].Name)=[forms]![frmcmcstaff]![cmbname]);"

MsgBox strSQL
Set rst = dbs.OpenRecordset(strSQL)

Me![txtAcro] = "I-T-S/" & rst![Acro]

End Sub

 

[coco86]

Well, the message box should be showing the SQL not the result...that's what you've told it to do. If you want to see the result move the line down to after you open the recordset and change it to

msgbox rst![Acro]

Since you're only looking for a single record, though, I'd use dlookup rather than a recordset. You could change your code to a single line:

Me![txtAcro] = dlookup("Contractors Table","Acro","Name='" & [forms]![frmcmcstaff]![cmbname] & "'")

-Coco

 

[r0nniem]

coco86 thank you for your help. I'm not familiar with the dlookup function so I didn't get it working that way.

I did get it working by giving [name] 2 columns; Me![txtAcro] = Me![cmbName].Column(1)