Re: Moving through records and EOF

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.

vufltrn <knooner@xxxxxxxxxxx> wrote in
news:1186844313.086347.62780@xxxxxxxxxxxxxxxxxxxxxxxxxxxx:

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

' 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





--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

.