Storing an SQL result into a variable

[carleto]

I was wondering the best way to store a SQL result into a variable. I have a table that will always have just one field with one record. I want to store that result into a variable in a sub procedure. I thought I would be able to do the following:

Dim temp As String

SQL = "SELECT UserName INTO " & temp & " FROM tblUserName;"

DoCmd.RunSQL SQL

But this does not seem to work.

I have also considered using a record set but how do you store into a variable using a recordset?

 

[HitechUser]

carleto,

Have you tried using DLookUp???? There are examples in the help text....

 

[carleto]

Hey thanks that worked out well!!!