By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,105 Members | 1,018 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,105 IT Pros & Developers. It's quick & easy.

Access recordset alternative?

P: n/a
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)

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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)

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.