Can't get rid of EOF message

K

KJGinNC

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
 
D

Dirk Goldgar

KJGinNC said:
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
Click to expand...

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 -----
 
K

KJGinNC

Thanks so much! I knew it was something simple like that! It works great now.


Dirk Goldgar said:
KJGinNC said:
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
Click to expand...

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)
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Random Sample by Person automation 4
ADOBD recordset on Informix database - values always divided by 10 2
HAVING LIKE 16
Form Losing Focus 0
Excel hangs 1

Top