Moving through records and EOF

[vufltrn]

I have a form that displays records for a user to review. I am trying
to code command buttons that allow the user to move to the next
record. Whenever they get to the last record, the button just goes
shows the current record again; it does not give any error. This is my
first time trying to use recordsets or the EOF to try and do this and
any help would be appreciated.

Code starts here:
-------------------------

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open
Dim mySQL, FormType As String
Dim WhereExist As Integer

mySQL = CurrentDb.QueryDefs("qryAudit").SQL
WhereExist = InStr(mySQL, "WHERE")
If WhereExist > 0 Then
mySQL = Left(mySQL, WhereExist - 1)
Else
mySQL = Left(mySQL, InStr(mySQL, ";") - 1)
End If
mySQL = mySQL & " WHERE [Crew1] = '" & DLookup("[Name]",
"qryUserID") & "'"
mySQL = mySQL & " AND [ReviewedBySelf] = False"
CurrentDb.QueryDefs("qryAudit").SQL = mySQL
Me.RecordSource = mySQL

If (Me.Recordset.EOF Or Me.Recordset.BOF) Then
MsgBox "You have no unreviewed records."
Cancel = True
End If

With Me
.RecordsetType = 2
.chkReviewed.Visible = True
.lblReviewed.Visible = True
.cmdNextRecord.Visible = True
.boxReviewedBySelf.Height = 1500 '1440 twips = 1 inch
.boxReviewedBySelf.Top = 18120
.chkReviewed.Locked = False
End With

Exit_Sub:
Exit Sub

Err_Form_Open:
MsgBox "Error number: " & Err.Number & "; " & Err.Description
Resume Exit_Sub

End Sub
--------------------------------------------------------------
Private Sub cmdNextRecord_Click()
On Error GoTo Error_cmdNextRecord_Click
Dim mySQL As String

Me.Recordset.MoveNext
Me.txtHidden.SetFocus

If Me.Recordset.EOF Then
MsgBox "You have displayed all unreviewed records. Please
either exit " & _
"to the Main Menu, or mark records as 'Reviewed'.",
vbOKOnly
Me.Requery

If (Me.Recordset.BOF Or Me.Recordset.EOF) Then
MsgBox "You have no more unreviewed records.", vbOKOnly
DoCmd.Close , , acSaveNo
GoTo Exit_Sub
End If

Me.Recordset.MoveFirst

End If

Exit_Sub:
Exit Sub

Error_cmdNextRecord_Click:
MsgBox "Error number: " & Err.Number & "; " & Err.Description
Resume Exit_Sub

End Sub

------------------------------------
End of Code

Any help would be greatly appreciated. This is driving me crazy!

Thanks

 

[Bob Quintal]

I could not duplicate your issue with the .EOF code not firing,
if you try without the requery, does the form move back to the
first record?
I have put some comments into the code.

I have a form that displays records for a user to review. I am
trying to code command buttons that allow the user to move to
the next record. Whenever they get to the last record, the
button just goes shows the current record again; it does not
give any error. This is my first time trying to use recordsets
or the EOF to try and do this and any help would be
appreciated.

Code starts here:

' this dims mySQL as a variant

Dim mySQL, FormType As String

'I think you want Dim mySQL as string, FormType As String

Dim WhereExist As Integer

mySQL = CurrentDb.QueryDefs("qryAudit").SQL
WhereExist = InStr(mySQL, "WHERE")
If WhereExist > 0 Then
mySQL = Left(mySQL, WhereExist - 1)
Else
mySQL = Left(mySQL, InStr(mySQL, ";") - 1)
End If
mySQL = mySQL & " WHERE [Crew1] = '" &
DLookup("[Name]",
"qryUserID") & "'"
mySQL = mySQL & " AND [ReviewedBySelf] = False"
CurrentDb.QueryDefs("qryAudit").SQL = mySQL
Me.RecordSource = mySQL

'For no records in the set, .BOF AND .EOF will be true
'But no need to test for .BOF at all, because if there are one
or more records, EOF will be false

If Me.Recordset.EOF then

'If (Me.Recordset.EOF Or Me.Recordset.BOF) Then
MsgBox "You have no unreviewed records."
Cancel = True
End If

With Me
.RecordsetType = 2
.chkReviewed.Visible = True
.lblReviewed.Visible = True
.cmdNextRecord.Visible = True
.boxReviewedBySelf.Height = 1500 '1440 twips =
1 inch

.boxReviewedBySelf.Top = 18120

' since you open the recordset as a snapshot (type 2)
' this is useless. the query is not updateable.

.chkReviewed.Locked = False
End With

Exit_Sub:
Exit Sub

Err_Form_Open:
MsgBox "Error number: " & Err.Number & "; " &
Err.Description Resume Exit_Sub

End Sub
--------------------------------------------------------------
Private Sub cmdNextRecord_Click()
On Error GoTo Error_cmdNextRecord_Click
Dim mySQL As String

Me.Recordset.MoveNext

' try moving this line to an else section of the .EOF If Block.

Me.txtHidden.SetFocus

If Me.Recordset.EOF Then
MsgBox "You have displayed all unreviewed records.
Please
either exit " & _
"to the Main Menu, or mark records as
'Reviewed'.",
vbOKOnly
Me.Requery

If (Me.Recordset.BOF Or Me.Recordset.EOF) Then
MsgBox "You have no more unreviewed records.",
vbOKOnly DoCmd.Close , , acSaveNo
GoTo Exit_Sub
End If

' this is redundant becaue of the requery.

Me.Recordset.MoveFirst

End If

Exit_Sub:
Exit Sub

Error_cmdNextRecord_Click:
MsgBox "Error number: " & Err.Number & "; " &
Err.Description Resume Exit_Sub

End Sub

------------------------------------
End of Code

Any help would be greatly appreciated. This is driving me
crazy!

Thanks