How to reference a field of a recordset with a variable

[kc-mass]

Hi

I have a table that is used for reporting purposes. It will only ever have
one record and the fields of that record are filled in by a variety of
queries from a variety of tables. One field is essentially a repeating
field Month1...Month6 which need to be filled with the prior six counts of
activity levels. I am trying to assign values to those fields using a
variable reference to the field name of the record set. It is not working .
It returns an error of "Item not found in this collection".

The code is below. The line with the stars is the problem. Any help
appreciated.

Kevin

Sub FillMonthlyCounts()
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim RSNew As DAO.Recordset
Dim strSQL As String
Dim intCounter As Integer
Dim strWhichMonth As String
Set DB = CurrentDb
Set RSNew = DB.OpenRecordset("tblReportDataByMonth")
For intCounter = 1 To 6
strSQL = "SELECT Count(tblDatesOf.DateOf) AS CountOfDateOf FROM
tblDatesOf " _
& "WHERE ((Month([DateOf])= " & PriorMonth(intCounter) & ") AND
(Year([DateOf])= " & PriorYear(intCounter) & "));"
Set RS = DB.OpenRecordset(strSQL)
If Not RS.EOF Then
RS.MoveFirst
Else
MsgBox "No data found"
Exit Sub
End If
strWhichMonth = "Month" & CStr(intCounter)
RSNew.MoveFirst
RSNew.Edit
RSNew!Fields(strWhichMonth) = RS!countofDateOf
'*********************************
RSNew.Update
Next intCounter
End Sub

 

[Albert D. Kallal]

RSNew!Fields(strWhichMonth) = RS!countofDateOf

Try:

RSNew(strWhichMonth) = RS!countofDateOf

 

[John W. Vinson]

strWhichMonth = "Month" & CStr(intCounter)
RSNew.MoveFirst
RSNew.Edit
RSNew!Fields(strWhichMonth) = RS!countofDateOf
'*********************************

I'm guessing that it should be RSNew.Fields(strWhichMonth) - using . rather
than ! as the delimiter. You should also step through the code and be sure
that strWhichMonth in fact exactly matches the fieldname.

 

[kc-mass]

The solution was " RSNew(strWhichMonth) = RS!countofDateOf"

Many Thanks to all.

Kevin

 

[David W. Fenton]

RSNew!Fields(strWhichMonth) = RS!countofDateOf

The ! operator is used to refer to objects that are members of a
collection. But "Fields" is a collection, so it's a property of the
recordset. Properties are referred to by the . operator. Thus:

RSNew.Fields(strWhichMonth) = RS!countofDateOf

....should do the trick. Given that the Fields collection is the
default collection of a recordset, this should also work:

RSNew(strWhichMonth) = RS!countofDateOf

This is a case where the implicit property wrappers around controls
in forms and reports that allow the use of the . operator for
controls leads people to get confused about which items can be
referred to with . and which with !. I have maintained in all my
coding the style of using the ! operator for controls, never the .
operator, partly because I'm stubborn, but also because I consider
it clearer coding. There's also the fact that in principle I don't
like depending on something that is created behind the scenes that I
can't control (and that is subject to corruption, which does happen
in a small number of cases).