Using SQL to get data into a variable

[wingforward]

I'm trying to use a simple Select statement to get data into a variable to set a field and I have two questions. Is this possible? What am I doing wrong?


'Start Code
Dim strCodeID as String
strCode ID = Me.CodeID

Dim SQLCmd as String
SQLCmd = "SELECT Description FROM tblCodes WHERE CodeID = " & strCodeID & ";"

Dim strResult as String
strResult = DoCmd.RunSQL (SQLCmd)

Me.Description = strResult

'End Code

 

[vbaInet]

Have a look at the DLookup() function. That's what you need.

 

[wingforward]

That's a whole lot simpler than what I was trying. Thanks.

 

[vbaInet]

You're welcome.

 

[Bowmap]

Yes Dlookup is quick and easy. But, just so you know...

The SQL statement should look like

"SELECT Description FROM tblCodes WHERE CodeID = '" & strCodeID & "';"

Assuming that CodeID is a text field you need to surround your value with single quotes. FYI a date value needs pound signs i.e. #12/21/2012#.

Cheers