String to find Recordset Field Value

[NevilleT]

I have a module that is generic and I want to pass values to it. Basically
it compares 2 recordsets (rstOld and rstNew) and if there is a difference,
will identify the old and new values. At this stage I am just printing them.

While Not rstOld.EOF
strCriteria = "[UserAccessNo] = " & rstOld!UserAccessNo
rstNew.MoveFirst
rstNew.FindFirst strCriteria
If rstNew!Read <> rstOld!Read Then
Debug.Print rstOld!Read & " " & rstNew!Read
End If
rstOld.MoveNext
Wend

What I would like to do is replace "rstOld!UserAccessNo" with a string so
that for other tables, I can pass the primary key field name as a parameter.
For example, if instead of "UserAccessNo", the field was "PersonNo", I would
call the sub with a parameter

subCheckValues(strFieldName as string).

If the value I pass to strFieldName is "PersonNo" I thought the following
would work.

dim strFullName as string
strFullName = "rstNew!" & strFieldName

strCriteria = "[UserAccessNo] = " & strFullName

Unfortunately no. Having spent about a day on it, it is time to turn to the
experts. How can I use a string to refer to a field value in a recordset?

 

[Albert D. Kallal]

Actually, the critical concept in ms-access is that virtually EVERYTHING is
a collection.

that means forms, reports, controls on forms, reocrdsets...EVERYTHING is a
collection.

Once you realize the above, then virtually everything in ms-access will to
an understanding.

if you open a form in ms-access, it becomes part of the forms collection.
Assuming only ONE form opened, then

forms!frmCustomer
forms!(0)
forms!("frmCustomer")

Are ALL equivalent. Notice how I used a string for the last collection
example...

so:

rstNew(strFieldName)

That example will work for controls collections, reports, forms..and
yes..your recordset example.....

It is very important you grasp the concept of collections in ms-access....as
virtually everything is referenced
from a collection....

we could go:

strFormName = "frmCustomer"

and then refence the open form via:

forms(strFormName)

 

[NevilleT]

Hi Albert
I think I understand where you are coming from, however I still have a
problem, and it may be in the syntax. To simplify things, assume I create a
string called strFieldName. I then say

strFieldName = "UserAccessNo" If I change

strCriteria = "[UserAccessNo] = " & rstOld!UserAccessNo
for
strCriteria = "[UserAccessNo] = " & rstOld!(strFieldName)

I get an error "Compile Error. Type-declaration character does not match
declared data type"

Where am I going wrong?

 

[John Spencer]

strCriteria = "[UserAccessNo] = " & rstOld.Fields(strFieldName)

and probably, you want something like

strCriteria = "[" & strFieldName & "]" = " & rstOld!(strFieldName)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi Albert
I think I understand where you are coming from, however I still have a
problem, and it may be in the syntax. To simplify things, assume I create
a
string called strFieldName. I then say

strFieldName = "UserAccessNo" If I change

strCriteria = "[UserAccessNo] = " & rstOld!UserAccessNo
for
strCriteria = "[UserAccessNo] = " & rstOld!(strFieldName)

I get an error "Compile Error. Type-declaration character does not match
declared data type"

Where am I going wrong?

Actually, the critical concept in ms-access is that virtually EVERYTHING
is
a collection.

that means forms, reports, controls on forms, reocrdsets...EVERYTHING is
a
collection.

Once you realize the above, then virtually everything in ms-access will
to
an understanding.

if you open a form in ms-access, it becomes part of the forms collection.
Assuming only ONE form opened, then

forms!frmCustomer
forms!(0)
forms!("frmCustomer")

Are ALL equivalent. Notice how I used a string for the last collection
example...

so:

rstNew(strFieldName)

That example will work for controls collections, reports, forms..and
yes..your recordset example.....

It is very important you grasp the concept of collections in
ms-access....as
virtually everything is referenced
from a collection....

we could go:

strFormName = "frmCustomer"

and then refence the open form via:

forms(strFormName)

 

[Albert D. Kallal]

strCriteria = "[UserAccessNo] = " & rstOld!(strFieldName)


You need to leave out the !

strCriteria = "[UserAccessNo] = " & rstOld(strFieldName)

Referencing a collection by a number, or string does not use the !

if the company name was the 3rd field, then all 3 of the
following are the same:

strField = "Companyname"

rstOld(2) ' 3 rd field name
rstold("CompanyName")
rstold(strField)

There is no ! used when you work with a collecion in ms-access.....

 

[NevilleT]

Thanks Albert. That solved the problem. My gratitude for your assistance.
The program is working fine now and the collection suggestion makes a lot of
sense.

strCriteria = "[UserAccessNo] = " & rstOld!(strFieldName)

You need to leave out the !

strCriteria = "[UserAccessNo] = " & rstOld(strFieldName)

Referencing a collection by a number, or string does not use the !

if the company name was the 3rd field, then all 3 of the
following are the same:

strField = "Companyname"

rstOld(2) ' 3 rd field name
rstold("CompanyName")
rstold(strField)

There is no ! used when you work with a collecion in ms-access.....