Access recordset alternative?

[maccaroo]

Hi

Does anyone know an alternative way to get the result from a query in
access vba? I've got a form with a few labels that need populating.
Even if I only need a single value, the only way I can see to get some
of the data is by creating a recordset object and populating it, and
then extracting the field I need.

Here's my code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strCompanyName As String

strSQL = "SELECT tblCompanies.Name FROM tblCompanies WHERE
CompanyID = " & Me.Recordset!CompanyID

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

Me.lblCompanyName.Caption = rs!Name

Isn't there something like:

MyValue = RunSQL("SELECT tblCompanies.Name FROM tblCompanies WHERE
CompanyID = " & Me.Recordset!CompanyID)

[Terry Kreft]

Look at DLookup in help.

Personally though I would go with what you've got. If you really have a lot
of bits like this you can always write your own function to handle it

Function MyLookup(SQL as string) as variant
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset(SQL, dbOpeSnapshot)

MyLookup = rs.Fields(0).Value
End Function
and call it
dim SQL as string

SQL = "SELECT tblCompanies.Name " _
& "FROM tblCompanies " _
& " WHERE CompanyID = " _
& Me.Recordset!CompanyID

Me.lblCompanyName.Caption = MyLookUp(SQL)
or (horrible)

Me.lblCompanyName.Caption = MyLookUp("SELECT tblCompanies.Name " _
& "FROM tblCompanies "
_
& " WHERE CompanyID = "
_
&
Me.Recordset!CompanyID)

--
Terry Kreft
MVP Microsoft Access
"maccaroo" <ma******@yahoo.co.uk> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

Hi

Does anyone know an alternative way to get the result from a query in
access vba? I've got a form with a few labels that need populating.
Even if I only need a single value, the only way I can see to get some
of the data is by creating a recordset object and populating it, and
then extracting the field I need.

Here's my code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strCompanyName As String

strSQL = "SELECT tblCompanies.Name FROM tblCompanies WHERE
CompanyID = " & Me.Recordset!CompanyID

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

Me.lblCompanyName.Caption = rs!Name

Isn't there something like:

MyValue = RunSQL("SELECT tblCompanies.Name FROM tblCompanies WHERE
CompanyID = " & Me.Recordset!CompanyID)