You'd like to provide some mechanism for allowing users to move from row to row on a form, but you think the navigation buttons Access provides are too small and unattractive. Also, you can't control when the user can or can't move to another row.
Access provides navigation buttons for you to use on forms, allowing you to move easily from row to row. However, you can neither move nor resize these buttons, and you can't change anything about their appearance.
You can create your own buttons, place them on a form, and have each button use the GoToRecord macro action. Unfortunately, this has two drawbacks:
If you attempt to move to the previous or next row and you're already at the end of the recordset, the macro will fail. The GoToRecord macro action just isn't smart enough to work in this case.
Your buttons will always be available, giving no indication of when you can use them.
To avoid errors, you must use VBA. This solution demonstrates the steps you can take to add the appropriate code to your application so that navigation buttons will move you safely from row to row and shows how to disable the navigation buttons when they are unavailable. The form frmNav in 02-06.MDB (see Figure 2-11) works this way. You can load it and give it a try before attempting to build your own. Use the navigation buttons to move from row to row (there are only a few rows in the table so far). Note that, as you move around in the table, the appropriate buttons become enabled and disabled. Also try using the PgUp and PgDn keys. You'll see that the appropriate buttons still become disabled as necessary. Try entering a row number into the text box in the navigation controls; when you leave the text box, you will move to the selected row number.
Follow these steps to include this functionality in your own applications:
Set your form's properties as shown in Table 2-4, removing the form's scrollbars and built-in navigation buttons. (Because this method works only for scrolling through rows of data, your form must also have its RecordSource property set so that the form displays rows of data.)
Copy the buttons from frmNav, or create your own five buttons on your form. Do not use the Access Button Wizard to create your buttons, because it will add inappropriate code to the buttons; you want to be able to supply the code yourself. If you create your own buttons, you can add pictures from Access's selection of pictures. Click on the Build button to the right of the Picture property on the properties sheet for each button. Also, create a text box named txtCurrentRow to display the current row number and a label named lblTotalRows to display the total number of rows. (In these solutions, the exact names of the controls you create usually don't matter. In this one, however, the names do matter; make sure your names match ours exactly.)
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):
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.)
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 you press the PgDn key until you're on the last row of data and then press the key once more (if your data set allows you to add rows). Access's NewRecord property tells you if you're on the new row. (See the Solution in Recipe 6.2 for more information on using this property.)
To enable cmdNew once you've entered some data on the new row, the form's KeyPress event calls acbHandleKeys, as shown here. This code checks each keystroke, and if cmdNew isn't enabled and the form is dirty, the code enables cmdNew.
Public Sub acbHandleKeys(frm As Form) Dim fEnabled As Boolean fEnabled = frm.cmdNew.Enabled If Not fEnabled And frm.Dirty Then frm.cmdNew.Enabled = True End If End Sub
To match the functionality of the standard Access navigation controls, the sample form reacts to the AfterUpdate event of the txtCurrentRow text box by moving to the row you've specified. The event procedure calls the acbMove subroutine, which does all the work. This procedure, shown later, does the following:
Retrieves a pointer to the form's recordset, using the recordset retrieved with the form's RecordsetClone property.
Moves to the first row (rst.MoveFirst) and then moves the specified number of rows from there (rst.Move).
Makes the form display the same row that's current in the recordset.
By equating the form's bookmark (a binary value, indicating the current row, whose exact contents are of no interest) and the recordset's bookmark, you make the form display the row that is current in the underlying recordset. If there is no current row (that is, if you've asked to go beyond the final row of data), an error occurs, and the code moves you directly to the new row on the form.
The source code for acbMove is:
Public Sub acbMove(frm As Form, ByVal lngRow As Long) ' Move to a specified row. On Error GoTo HandleErr Dim rst As DAO.Recordset ' Get a pointer to the form's recordset. Set rst = frm.RecordsetClone ' Move to the first row, and then hop to ' the selected row, using the Move method. rst.MoveFirst If lngRow > 0 Then rst.Move lngRow - 1 End If ' Finally, make the form show the ' same row as the underlying recordset. frm.Bookmark = rst.Bookmark rst.Close Set rst = Nothing ExitHere: Exit Sub HandleErr: ' If an error occurs, it's most likely that ' you requested to move to the row past the ' last row, the New row, and there's no bookmark ' there. If that's the error, just move ' to the New row programmatically. Select Case Err Case acbcErrNoCurrentRow DoCmd.GoToRecord , , acNewRec Resume Next Case Else MsgBox Error & " (" & Err & ")" Resume ExitHere End Select End Sub
The code provided in basMovement makes it easy for you to move this functionality from one application to another just by hooking the correct form and control events. You can get similar results by creating your own toolbar and using the record navigation buttons that Access provides. A toolbar you create will control whatever form happens to be the current form. Figure 2-12 shows a form/toolbar combination in action. You'll need to decide for yourself which technique you like best. The toolbar approach is simpler, but it is difficult to move toolbars from one database to another, and they do clutter up the work area. You also have little programmatic control over the toolbars.
The sample form updates the display of the total number of rows in lblTotalRows every time you move from row to row. When you first open the form, Access may not yet know how many rows will be in the recordset, and the value returned in the recordset's RecordCount property may be inaccurate. You can move to the last row when you first open the form, forcing Access to find out how many rows there will be, but this can be slow if your form's recordset contains a large number of rows. Access continues to calculate as you use the form, and eventually it will supply the correct value in the RecordCount property of the form's recordset. The compromise is that the total number of rows may be incorrect until you use the form for a few seconds. If this bothers you, you can add to the form's Open event code that works like this:
Dim rst As DAO.Recordset Set rst = Me.RecordsetClone rst.MoveLast
For small recordsets, this will be fast but also unnecessary, because the RecordCount property will already be accurate. For large recordsets, this might take a few seconds to calculate and will make opening your form seem slower.
For more information on using DAO in Access databases, see How Do I Use Data Access Objects (DAO) in New Databases? in the Preface.