Item not found in collection error 2

[vba317]

I have code that was working till I added one more line to it. The new line is to get access to another record. I have tried many things with no success. For the conditions from the query fyord =11. I code that has error is in blue. I was hoping to get some assistance.

Tom

Public Function CurMonYr(strUCI As String, strMon As String, strYr As String, iMon As Integer)
    Dim strSql As String
    Dim rstYr As DAO.Recordset
    Dim db As DAO.Database
    Dim intYr As Integer
    Set db = CurrentDb
    strSql = "SELECT fy.uci,fy.rptpddiff,fy.mon_shnm,fy.cy " & _
             "FROM dbo_rpt_FYInfo fy " & _
             "WHERE fy.uci='" & strUCI & "' " & _
             "AND fy.rptpddiff=1;"
    Set rstYr = db.OpenRecordset(strSql, dbOpenSnapshot)
    If Not rstYr.EOF Then    ' Make sure not empty set
        With rstYr
            .MoveLast
            .MoveFirst
        End With
        For intYr = 0 To rstYr.RecordCount
            ' Set Variables
            strUCI = (rstYr![uci])
            strMon = (rstYr![mon_shnm])
            strYr = (rstYr![cy])
[Blue]            iMon = (rstYr![fyord])  [/Blue]
        Next intYr
    End If
End Function

 

[MajP]

Fyord is not in the recordset.

SELECT fy.uci,fy.rptpddiff,fy.mon_shnm,fy.cy

 

[Andrzejek]

Your code looks a little suspicious to me…

[pre]
For intYr = 0 To rstYr.RecordCount
' Set Variables
strUCI = (rstYr![uci])
strMon = (rstYr![mon_shnm])
strYr = (rstYr![cy])
iMon = (rstYr![fyord])
Next intYr
[/pre]
If you expect only one record in your rstYr, then you don’t need the For – Next loop. I would then use If RecordCount = 1 then, and in Else provide an error that multiple records should not happen.

If you do expect more than one record, then you populate your variables from arbitrary ‘first’ (since there is no ORDER BY in your SQL) record in your rstYr since you not have any MoveNext in your loop.

But if you do expect multiple records and you do add MoveNext, then you will populate your variables from the arbitrary ‘last’ record and overwrite any values of your variables from any previous records.

Also, with multiple records, if you go from 0 to RecordCount, and let’s say you have 5 records, you will go 6 times thru the loop, from 0 to 5.

Suspicious to me...

Have fun.

---- Andy

 

[genomon]

Also you forgot to include the new fyord field in the SELECT statement that defines your recordset:

strSql = "SELECT fy.uci,fy.rptpddiff,fy.mon_shnm,fy.cy,fyord " & _
"FROM dbo_rpt_FYInfo fy " & _
"WHERE fy.uci='" & strUCI & "' " & _
"AND fy.rptpddiff=1;"

Beir bua agus beannacht!

 

[vba317]

genomon,
You are correct I forgot to add fyord to the select statement. I added it and everything works fine now.

Thanks

 

[vba317]

Andy,
You are correct . Since I am only expecting one record I am changing the query to reflect that.