"KJGinNC" <(E-Mail Removed)> wrote in message
news:A5B6980E-3432-435B-9DB7-(E-Mail Removed)
> Hi,
>
> I've got some ugly code below that works, but at the end I get the
> "Either BOF or EOF is true...." message. I don't want that message
> to appear, plus I can't see why it keeps showing up. I've tried
> Do..Loop, If..Then..Else, While..Wend.
>
> Can anyone tell me what's wrong or how to make it better?
>
> Thanks
>
> KJGinNC
>
> Option Compare Database
>
> Private Sub GetRecords_Click()
> On Error GoTo Err_GetRecords_Click
>
> Dim cnn As ADODB.Connection
> Dim rst As ADODB.Recordset
> Dim strSQL As String, strSQL2 As String
> Dim Sname As String, Alevel As String
> Dim Sweek As Long
> Dim flag As Integer
>
> Set cnn = CurrentProject.Connection
> Set rst = New ADODB.Recordset
>
>
> rst.Open "Specialist", cnn, adOpenDynamic, adLockPessimistic
> rst.MoveFirst
> Sname = (rst![MpApprvdBy])
>
> Sweek = [Forms]![Test3]![WeekNum]
> Alevel = [Forms]![Test3]![AuditLevel]
>
> DoCmd.SetWarnings False
>
> DoCmd.RunSQL "SELECT TOP 10 PERCENT RawData.ID, RawData.MpApprvdBy,
> RawData.Week," _
> & "RawData.ToAudit INTO Xtmp FROM RawData WHERE
> (((RawData.MpApprvdBy)=" _
> & Chr(34) & Sname & Chr(34) & ") AND ((RawData.Week)=" _
> & Sweek & ") AND ((RawData.ToAudit) Is Null)) ORDER BY
> Rnd([ID])" DoCmd.RunSQL "UPDATE xTmp INNER JOIN RawData ON xTmp.ID =
> RawData.ID SET RawData.ToAudit =" _
> & Chr(34) & Alevel & Chr(34)
>
> Do While rst.EOF = False
> rst.MoveNext
> Sname = (rst![MpApprvdBy])
>
> DoCmd.RunSQL "SELECT TOP 10 PERCENT RawData.ID,
> RawData.MpApprvdBy, RawData.Week," _
> & "RawData.ToAudit INTO Xtmp FROM RawData WHERE
> (((RawData.MpApprvdBy)=" _
> & Chr(34) & Sname & Chr(34) & ") AND ((RawData.Week)=" _
> & Sweek & ") AND ((RawData.ToAudit) Is Null)) ORDER BY
> Rnd([ID])" DoCmd.RunSQL "UPDATE xTmp INNER JOIN RawData ON
> xTmp.ID =
> RawData.ID SET RawData.ToAudit =" _
> & Chr(34) & Alevel & Chr(34)
> Loop
>
> DoCmd.SetWarnings True
> Exit_GetRecords_Click:
>
> Exit Sub
>
> Err_GetRecords_Click:
> MsgBox Err.Description
> Resume Exit_GetRecords_Click
> End Sub
rst.EOF becomes true when you have moved *past* the last record, not
when you are *on* the last record. In your loop, you call rst.MoveNext
and then refer to rst!MpApprvdBy, without checking first to see if
MoveNext took the recordset past the last record. Your code should
probably be structured like this:
'----- start of revised code -----
Private Sub GetRecords_Click()
On Error GoTo Err_GetRecords_Click
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String, strSQL2 As String
Dim Sname As String, Alevel As String
Dim Sweek As Long
Dim flag As Integer
Sweek = [Forms]![Test3]![WeekNum]
Alevel = [Forms]![Test3]![AuditLevel]
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "Specialist", cnn, adOpenDynamic, adLockPessimistic
DoCmd.SetWarnings False
Do Until rst.EOF
Sname = (rst![MpApprvdBy])
DoCmd.RunSQL _
"SELECT TOP 10 PERCENT " & _
"RawData.ID, RawData.MpApprvdBy, " & _
"RawData.Week, RawData.ToAudit " & _
"INTO Xtmp FROM RawData " & _
"WHERE (((RawData.MpApprvdBy)=" & _
Chr(34) & Sname & Chr(34) & _
") AND ((RawData.Week)=" & Sweek & _
") AND ((RawData.ToAudit) Is Null)) " & _
"ORDER BY Rnd([ID])"
DoCmd.RunSQL _
"UPDATE xTmp INNER JOIN RawData " & _
"ON xTmp.ID = RawData.ID " & _
"SET RawData.ToAudit =" & Chr(34) & Alevel & Chr(34)
rst.MoveNext
Loop
Exit_GetRecords_Click:
DoCmd.SetWarnings True
Exit Sub
Err_GetRecords_Click:
MsgBox Err.Description
Resume Exit_GetRecords_Click
End Sub
'----- end of revised code -----
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
