End of table code

[gafort]

I have a form with next and previous buttons to advance through records. They work until I get to record 1 or record 300. What code should I use, and where, to continue scrolling through the table to avoid runtime error?

[June7]

One method is to handle the error, another is to use RecordsetClone to check if at BOF or EOF. For starters review http://www.vbaexpress.com/forum/showthread.php?t=19086

[Missinglinq]

Here's what I use for this. If you don't want the Messagebox to pop, simply remark it out, and the code will simply not allow Access to attempt to move out of the range of Records.

Private Sub Next_Click()
  If CurrentRecord = RecordsetClone.RecordCount Then
    MsgBox "You are on the Last Record!"
  Else
    DoCmd.GoToRecord , , acNext
  End If
End Sub

Private Sub Previous_Click()
  If CurrentRecord = 1 Then
    MsgBox "You are on the First Record!"
  Else
   DoCmd.GoToRecord , , acPrevious
  End If
End Sub

Linq ;0)>

[gafort]

Thanks Linq, this one is a little trickier. Here is the code after I inserted your suggestions. Can you see what I did wrong?

Option Compare Database


Private Sub Add_New_Click()
DoCmd.GoToRecord , , acNewRec
End Sub


Private Sub btnVisitFacebook_Click()


If Nz(Me.Facebook, "") = "" Then
MsgBox "The Facebook Address is Empty!"
Cancel = True
Facebook.SetFocus
Exit Sub
End If
Application.FollowHyperlink [Facebook]
End Sub


Private Sub btnVisitWebsite_Click()
If Nz(Me.Website, "") = "" Then
MsgBox "The Website Address is Empty!"
Cancel = True
Website.SetFocus
Exit Sub
End If
Application.FollowHyperlink [Website]
End Sub


Private Sub Next_Click()
If CurrentRecord = RecordsetClone.RecordCount Then
DoCmd.GoToRecord , , acNext
End If
End Sub


Private Sub Next_GotFocus()
Me.Refresh
End Sub


Private Sub Previous_Click()
If CurrentRecord = 1 Then
DoCmd.GoToRecord , , acPrevious
End If
End Sub


Private Sub Previous_GotFocus()
Me.Refresh
End Sub


Private Sub Questions_Click()
DoCmd.OpenForm "Questions", acNormal
DoCmd.Close acForm, Me.Name
End Sub




Private Sub Save_Record_Click()
DoCmd.RunCommand acCmdSaveRecord
End Sub

[Missinglinq]

In trying to eliminate the user warnings (I guess that's what you were trying to do) you mixed up what to dump and what to keep. This will do it, assuming that the buttons are named Next and Previous.

Private Sub Next_Click()
  If CurrentRecord = RecordsetClone.RecordCount Then
    'Do nothing
  Else
    DoCmd.GoToRecord , , acNext
  End If
End Sub

Private Sub Previous_Click()
  If CurrentRecord = 1 Then
    'Do nothing
  Else
   DoCmd.GoToRecord , , acPrevious
  End If
End Sub

Not sure exactly what you're trying to do with this

Private Sub Next_GotFocus()
 Me.Refresh
End Sub

and the similar code in Previous_GotFocus event, but I suspect this is superfluous.

Linq ;0)>

[gafort]

Once again, thank you Linq, I owe you big time. that code worked perfectly. the following code was inserted because I thought i might lose user input data if they did not save. is it not needed? I wanted to automatically save verses a manual save click.

Not sure exactly what you're trying to do with this

Private Sub Next_GotFocus()
 Me.Refresh
End Sub

and the similar code in Previous_GotFocus event, but I suspect this is superfluous.

Linq ;0)>

[Missinglinq]

In Access, when you

1. Move to another Record
2. Close a Form
3. Close Access itself

The Record is automatically saved!

MS Access is classified as a Rapid Application Development tool because when used the way it is intended to be used, i.e. with Bound Forms, the Access Gnomes take care of the vast majority of the work involved, like saving a Record, unlike most apps, where the developer has to code even the most mundane tasks!

Me.Requery and Me.Refresh will also save the Current Record, but by Default, Access performs a Refresh every 60 seconds without user/developer interaction.

There is one caveat which applies to the numbered list, above:

If you use a custom Command Button to close a Form, using

DoCmd.Close

you need to first explicitly Save the Record, either using

DoCmd.RunCommand acCmdSaveRecord

or

If Me.Dirty Then Me.Dirty = False

so you should always have

DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close

or

If Me.Dirty Then Me.Dirty = False
DoCmd.Close

Because of a quirk in Access, when DoCmd.Close is used, Access closes the Form regardless of whether or not a PK field or other Required Field has been left blank or Validation rules have been violated!

If one of these things occur, Access will simply dump the Record, Close the Form, and not tell the user that the Record has been dumped!

The code If Me.Dirty Then Me.Dirty = False or DoCmd.RunCommand acCmdSaveRecord
forces Access to attempt to Save the Record, and if a violation has occurred, will throw up a warning message, allowing correction to be made before Closing the Form.

Linq ;0)>