New Record Problem

[Chris Kennedy]

I have a form where I control the navigation via command buttons rather the
navigation bar at the bottom (so I can do various bits of validation). I
don't want a user to be able to navigate to a new record using the next
button, so I test for the new record.

Dim intnewrec As Integer
intnewrec = Forms!frmInscriptions.NewRecord

If intnewrec = False Then
If ValidateInscription Then
DoCmd.GoToRecord acForm, "frmInscriptions", acNext
End If
End If

If intnewrec = True Then
MsgBox ("There are no more records in the database. If you need to
create a new one press the new record button")
DoCmd.GoToRecord acForm, "frmInscriptions", acPrevious
End If

The thing is I can get to a blank record at the end of a table add things to
it and then then when I press next I get the msgbox, not on the previous
record where I really want them to stop. The last record I want them to see
is, the last one filled in. Does anyone know how to acheive this.

 

[Dan Artuso]

Hi,
You have to use the form's recordset clone, do a MoveNext and see if you're on EOF.
If you are, disable the next button.

Something like:

Set rs = Me.RecordsetClone
rs.MoveNext
If rs.EOF Then
Me.cmdNext.Enabled = False
Else
Me.cmdNext.Enabled = True
End If

Set rs = Nothing

 

[Chris Kennedy]

I tried your code and a slight variation:

Set rst = New ADODB.Recordset

Set rst = Me.RecordsetClone

rst.MoveNext

If rst.EOF Then
MsgBox ("There are no more records in the database. If you need to
create a new one press the new record button")
DoCmd.GoToRecord acForm, "frmInscriptions", acPrevious
Else
MsgBox ("test")
DoCmd.GoToRecord acForm, "frmInscriptions", acNext
End If

It just won't test for EOF it just carries on going to the next record until
I get an error. Any ideas because RecordsetClone idea made sense to me.

 

[Dan Artuso]

Hi,
This code should go in the Current Event of the Form.
Is that where you have it?

 

[Chris Kennedy]

This is the current

Private Sub Form_Current()
Set rs = Me.RecordsetClone
rs.MoveNext
If rs.EOF Then
MsgBox ("There are no more records in the database. If you need to
create a new one press the new record button")
DoCmd.GoToRecord acForm, "frmInscriptions", acPrevious
Else
'DoCmd.GoToRecord acForm, "frmInscriptions", acNext
End If

Set rs = Nothing
End Sub

This button is the next button

Private Sub cmdNext_Click()

DoCmd.GoToRecord acForm, "frmInscriptions", acNext
End Sub

 

[Dan Artuso]

Hi,
This works for me (using DAO.Recordset)
I've added the rs.Bookmark = Me.Bookmark line, which is what I think
the problem was.


Private Sub Form_Current()
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

rs.Bookmark = Me.Bookmark
rs.MoveNext
If rs.EOF Then
Me.cmdNext.Enabled = False
Else
Me.cmdNext.Enabled = True
End If
Set rs = Nothing

End Sub