Retrieve Data from Table not on Form

[cp2599]

I hope someone can point me in the right direction ...
I'm trying to retrieve data from a table to use for processing within
a form; however, that data is not within the scope of the form's data
although I could add an unbounded field to store this data once I
retrieve it. I can create the SELECT statement as a query, but I
don't know how to run it VBA. Could someone please tell me how to
create and execute a SELECT statement in VBA. Thank you

 

[cp2599]

Is it a single data field you're looking for, or several rows or
columns of data?

For a single field/row, you can use the function:
DLookup([fieldname],[tablename],[optional criteria])

For more data or complex processing in VBA, you should looking into
using recordsets.

I hope someone can point me in the right direction ...
I'm trying to retrieve data from a table to use for processing within
a form; however, that data is not within the scope of the form's data
although I could add an unbounded field to store this data once I
retrieve it.  I can create the SELECT statement as a query, but I
don't know how to run it VBA.  Could someone please tell me how to
create and execute a SELECT statement in VBA.  Thank you- Hide quotedtext -

- Show quoted text -

Thank you for the direction. I tried this dlookup statement and keep
getting runtime error 2001 - cancelled previous action. Do you know
why? When I click Help nothing appears.

Me.chrFamilyStatus = DLookup("[chrFamilyStatus]", "tblFam",
"[lngzFamilyID] = " & Me.lngzFamilyID & " AND [EndDate] is NULL")

 

[bismuth83]

Sorry for the delayed response, did you ever find a solution? Is
there other code that occurs previously that might have an affect?
Also, don't forget to account for empty values:

If not isnull(Me.lngzFamilyID) then
Me.chrFamilyStatus = DLookup("[chrFamilyStatus]", "tblFam",
"[lngzFamilyID] = " & Me.lngzFamilyID & " AND [EndDate] is NULL")
End if