Access Cookbook - Ken Getz [45]
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.
Figure 2-12. A record navigation toolbar can replace navigation buttons on the form
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