QueryDefs Not Working

[smallh]

I'm trying to run a query when I form opens so that I can send an automatic email. I have the query already written and when I run it on its own I get four rows back. When I run it in the open event. It first brings back one record, I then use getRowsOK to put the recordset into a variant and somehow it then thinks the recordcount is 2.

A. Why would it not be retrieving the correct no of rows?
B. Why does it go from thinking there is only one row to then think there is two?

Code Below:

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs![Homeowner Actions Next 2 days]
qdf.Parameters("Forms!Who") = people(peopleCounter, 0)
Set rst = qdf.OpenRecordset(dbOpenSnapshot)
counter = 0
If (rst.RecordCount > 0) Then
If GetRowsOK(rst, rst.RecordCount, avarRecords2) Then
dataToSend = True
body = body + "<TABLE CELLSPACING=0 BORDER=0 CELLPADDING=1 WIDTH=650> " + _
"<TR> <TD VALIGN='TOP' HALIGN = 'CENTER' COLSPAN=1><B><FONT FACE='Arial' SIZE=4><P> Homeowner Actions" + _
"</B></FONT> </td> </tr>" + _
"</TABLE> "
Do While counter < rst.RecordCount
body = body + "<TABLE CELLSPACING=0 BORDER=0 CELLPADDING=1 WIDTH=650> " + _
"<TR><TD WIDTH='20%' VALIGN='TOP'> " + _
"<B><FONT FACE='Arial' SIZE=2><P>" + CStr(avarRecords2(0, counter)) + " </B></FONT></TD> " + _
"<TD WIDTH='20%' VALIGN='TOP'> " + _
"<B><FONT FACE='Arial' SIZE=2><P>" + CStr(avarRecords2(1, counter)) + " </B></FONT></TD> " + _
"<TD WIDTH='60%' VALIGN='TOP'> " + _
"<B><FONT FACE='Arial' SIZE=2><P>" + CStr(avarRecords2(3, counter)) + " </B></FONT></TD> " + _
"</TR> " + _
body = body + "</TR> " + _
"<TR><TD VALIGN='TOP' COLSPAN=3> " + _
"<B><FONT FACE='Arial' SIZE=2><P> " + Nz(avarRecords2(5, counter)) + " </B></FONT></TD> " + _
"</TR> " + _
"<TR> " + _
"<TD WIDTH='10%' VALIGN='TOP'> " + _
"<B><U><FONT FACE='Arial' SIZE=2><P>By: " + CStr(Nz(avarRecords2(4, counter), "")) + "</B></U></FONT></TD> " + _
"</TR> " + _
"<TR> <TD VALIGN='TOP' COLSPAN=3> &nbsp </td> </tr>" + _
"</TABLE> " + _
"<FONT SIZE=2></FONT></BODY> "
counter = counter + 1
Loop
End If
End If
Set dbs = Nothing
Set qdf = Nothing
Set rst = Nothing


Function GetRowsOK(rstTemp As Recordset, _
intNumber As Integer, avarData As Variant) As Boolean

' Store results of GetRows method in array.
avarData = rstTemp.GetRows(intNumber)
' Return False only if fewer than the desired number of

' rows were returned, but not because the end of the
' Recordset was reached.
If intNumber > UBound(avarData, 2) + 1 And _
Not rstTemp.EOF Then
GetRowsOK = False
Else
GetRowsOK = True
End If

End Function

Any help will be most greatful, after spending 5 hours on it yesterday, I'm starting to pull my hair out.

H

 

[pbaldy]

Have you tried:

...
Set rst = qdf.OpenRecordset(dbOpenSnapshot)
rst.MoveLast
...