Access 2000 VBA 1

[DWSwaim]

I need to assign a value from a temp query field into a string variable to be used in the sub. Any ideas? Here’s the jist of my code:

Set dbsDatabase = CodeDb
strSQL = "SELECT Recruitment_Location_Name FROM [Recruitment Locations] WHERE [Recruitment_Location_ID] = intLocation"
Set qdfstrTemp = dbsDatabase.CreateQueryDef(strQname, strSQL)

‘I tried the next line (and several like it) to get the value
strLocation = [Temp_Location_Query].Recruitment_Location_Name
DoCmd.DeleteObject acQuery, strQname
Set dbsDatabase = Nothing

Thanks
Darrell

 

[tinymind]

Have you tried ...

strLocation = qdfstrTemp!Recruitment_Location_Name

Tiny


Perfection is Everything
If it worked first time we wont be here!

 

[TonyJollans]

Hi DWSwaim,

When you do CreateQueryDef you are creating a Query, not running it.

If you just want a single value from a table, use the DLookup Function ..

strLocation = DLookup("[Recruitment_Location_Name]","[Recruitment Locations]","[Recruitment_Location_ID] = " & intLocation)

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[DWSwaim]

strLocation = qdfstrTemp!Recruitment_Location_Name
Did not work but I may have some other coding to do for it.

strLocation = DLookup("[Recruitment_Location_Name]","[Recruitment Locations]","[Recruitment_Location_ID] = " & intLocation)
worked as is....

Thanks