| re: Access recordset alternative?
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" <maccaroo@yahoo.co.uk> wrote in message
news:1106654415.326153.29950@f14g2000cwb.googlegro ups.com...[color=blue]
> 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)
>[/color]
| 
