Get table!Field Value in VBA

[Mike Thomas]

In Access 2003, DAO, I have a situation where I need to get a field's value
where I know the field name only as the contents of a string variable. The
very simplified example below demonstrates this more easily than I can
explain it.

Some code ...

strSQL = "Select * From mytable Where ID = 3"
Set r = db.openrecordset(strSQL, dbopenforwardonly)

strFieldValue = r!SomeField '' would work, but not possible in this snippet

strFieldName = "SomeField"

strFieldValue = r!strFieldName '' obviously gives 'no such field name'
error


Is there a way to code the above so that I can use the variable strFieldName
to accomplish the same thing as r!SomeField?

In my old days as a VFP programmer we called this macro substitution, where
strFieldName = "r!" + strFieldName, then strFieldValue = &strFieldName would
have worked.

Many thanks
Mike Thomas

 

[Albert D.Kallal]

Well, being previous FoxPro developer (back in the days of DOS 2.6 FoxPro),
I can identify with the macro substitutions issue.

However, since *EVERYTHING* in ms-access is a collection, and ALL
collections can be referenced by string values.

As a result of this discovery in ms-access, then I have NEVER missed, or
needed macro substitution in access...

So, there is two easy solutions you can use

#1..just use in your code as:

strFieldValue = r(strFieldName) '' obviously gives 'no such field name'

And, you can even reference open forms this way

Eg:

strWhatForm = "frmCustomers"
strWhatFieldContorl = "CompanyName"

So,

forms!frmCustomers!Companyname -- hard coded exmaple,
or, using strings...we can go

forms(strWhatForm)(strWhatFieldContorl) -- soft coded example....

Back to your example:

Of course, if you *only* needed the one field value, and not all values from
the record, you could use
use dllookup()

The syntax is:

dllookup("Field to grab", "table to grab from", "optional sql criteria"

So, you could go

strField = "Descripton"
strTable = "tblParts"

strPartDescription = dlookup(strField,strTable,"partid = 123")

The above is simply less work then creating a reocrdset, such as

dim rst as dao.RecordSet

set rst = currenddb.OpenReocrdSet("select * from tblParts where partid =
123")

strPartDescription = rst!Description
rst.Close

 

[Guest]

strFieldValue = r.Fields(strFieldName)

 

[Mike Thomas]

Albert, many thanks again for your help. I figured there must be a more
'modern' way than macro substitution.

Mike Thomas