Issue with Recordset referencing

[mrf1xa]

Hi, been a while since I've dabbled with Access and missing something very silly!

I have this code:

If rst1![Request_Type] = "New" Then 'New request, check for ID's with these SRNs or Names
            i = rst1![No_Users]
            For c = 1 To i
                str = rst1!("SRN" & c)

The issue I have is with the last line I have shown. If I reference the field directly (str = rst1![SRN1]) then it works fine, so the data types are correct. But when I use the above I get the error "Type declaration character does not match declared data type".

What am I doing wrong please?

Nigel
Didn't someone say work is supposed to be fun? They didn't have computers then I guess....

 

[MajP]

str = rst1!("SRN" & c)

You are mixing notation. There is Bang and Dot.
Bang: rst1!FieldName
Dot: rst1.("fieldName")

Only with dot can you use a variable
rst1.("SRN" & C)

 

[mrf1xa]

Brilliant, many thanks, I knew it would be something silly I was missing!

Nigel
Didn't someone say work is supposed to be fun? They didn't have computers then I guess....

 

[PHV]

rst1.("SRN" & C)
In fact, either this:
rst1("SRN" & C)
or this:
rst1.Controls("SRN" & C)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

Actually that is incorrect. The following are correct.
rst1.Fields("SRN" & C)
or
rst1("SRN" & C)

There is no Controls collection of the Recordset object only a Fields collection.
Both are examples of dot notation, as previously stated. The ".fields" can be dropped since this is the default property of the recordset object. VB allows you to drop any default methods or properties.

I mistakenly left the . in the original post which was incorrect.

 

[PHV]

OOps, sorry.
Controls for Form, Fields for Recordset.