Use Variable as Recordset Field Name

[John Thomas]

'This is the problem below in the "With RS2" section, it does not properly
recognize fld.Name as the Field name. I could also use the Variable
'"BookName", but I get the same; Item not in Collection error.

Dim db2 As Database, RS2 As Recordset
Set db2 = CurrentDb
Dim fld As Field
Dim BookName, OtherUses As String

Set RS2 = db2.OpenRecordset(strSQL)
For Each fld In RS2.Fields
If fld.Name = BookName Then
With RS2
.Edit
!fld.Name = "Number of Times Used in " & BookName &
Count2 & OtherUses
.Update
End With
End If
Next

Thanks John

 

[Guest]

rs2 = db2.openrecordset(strSQL)
there is no strSQL.
prior to opening the record set you need
strSQL ="some SQL string".
This will add field name and bookname to the collection.
Good luck.

 

[John Thomas]

Sorry to mislead you, there is the proper strSQL, I just didn't type it in,
the problem is in the fact that it does not see the variable as the field
name.

 

[Guest]

Hi John,

I believe this gets into the bang(!) vs dot discussion, and the sometimes
overlooked option of using ()'s rather than quotes to specify collection
items. I must admit that my habit is to use the bang, but I do remember
reading that it is actually recommended by many to use the ()'s instead ror a
few reasons, one of which being that it would allow you to use variables for
the item name. To use ()'s in place of the bang, you just eliminate the bang
and enclose the item name in quotes inside the ()'s. Or, if using a
variable, place the variable inside the ()'s without quotes.

You may want to try replacing:

!fld.Name = ...

with:

(fld.Name) = ...

I'm not sure if this will fix your problem, but it does seem like it may.

HTH, Ted Allen

 

[John Thomas]

Gave it a try, but got a compile error: expected line number or end of
statement.
Thanks for trying though.

 

[John Thomas]

I got it
..Fields(fld.Name) = ..

Thanks for the lead

 

[John Spencer (MVP)]

Why not just use the following? Does it fail?

Fld = ...

 

[John Thomas]

Thanks, That worked also.

Why not just use the following? Does it fail?

Fld = ...