Access Cookbook - Ken Getz [44]
Set the Name property for each of the command buttons, based on the following list (the code you'll use later depends on these particular names):
cmdFirst
cmdPrev
cmdNew
cmdNext
cmdLast
Add the following code to cmdFirst's Click event (for information on adding code to a form event, see the Preface):
Private Sub cmdFirst_Click( )
acbMoveFirst Me
End Sub
Add the following code to cmdPrev's Click event:
Private Sub cmdPrev_Click ( )
acbMovePrevious Me
End Sub
Add the following code to cmdNew's Click event:
Private Sub cmdNew_Click ( )
acbMoveNew Me
End Sub
Add the following code to cmdNext's Click event:
Private Sub cmdNext_Click( )
acbMoveNext Me
End Sub
Add the following code to cmdLast's Click event:
Private Sub cmdLast_Click ( )
acbMoveLast Me
End Sub
Add the following code to your form's Current event:
Private Sub Form_Current ( )
acbHandleCurrent Me
End Sub
Add the following code to your form's KeyPress event:
Private Sub Form_KeyPress(KeyAscii As Integer)
acbHandleKeys Me
End Sub
Set the form's KeyPreview property to True.
Add the following code to txtCurrentRow's AfterUpdate event:
Private Sub txtCurrentRow_AfterUpdate( )
acbMove Me, Me.txtCurrentRow
End Sub
Import the basMovement module from 02-06.MDB into your own application. (You'll need to verify that you've also set a reference to Microsoft DAO, using the Tools → References menu item from within the VBA editor. This code uses the DAO library, and later versions of Access don't add this reference by default.)
Discussion
This solution actually has three parts. The first part deals with the record navigation (Steps 1 through 8), the second part handles disabling the unavailable buttons (Steps 9 through 11), and the third part controls the direct movement to a specific row (Step 12).
For each of the five buttons, you've attached code that will call a common procedure whenever you press the button, thus reacting to the Click event. For each button, the subroutine you call calls a procedure that handles all the motion. Clicking on the first button calls this code:
Public Sub acbMoveFirst(frm As Form)
HandleMovement frm, acFirst
End Sub
which calls the HandleMovement procedure:
Private Sub HandleMovement(frm As Form, intWhere As Integer)
' It's quite possible that this will fail.
' Knowing that, just disregard any errors.
On Error Resume Next
DoCmd.GoToRecord , , intWhere
On Error GoTo 0
End Sub
Every subroutine that calls HandleMovement passes to it a reference to a form and an Access constant that indicates to what row it wants to move (acFirst, acPrevious, acNewRec, etc.). HandleMovement disables error handling, so Access won't complain if you try to move beyond the edges of the recordset. HandleMovement then uses the GoToRecord macro action to go to the requested row.
The second, and most complex, part of this solution handles enabling/disabling the buttons, depending on the current row. In Step 9, you attached a subroutine call to the form's Current event. This tells Access that every time you attempt to move from one row to another, Access should call this procedure before it displays the new row of data. This procedure, then, can do the work of deciding where in the recordset the current row is and, based on that information, can disable or enable each of the five navigation buttons. It also fills in the current row number and updates the display of the total number of rows.
A discussion of the full acbHandleCurrent code is beyond the scope of this solution (you can find the fully commented code in basMovement). As part of its work, however, the code must determine whether the current row is the "new" row. The new row is the one you get to if