Custom Navigation Buttons…

While Access provides Navigation Buttons, they must always be at the bottom of the Form.  This does not work for my Form designs (Figure 1).  Using the code below you can put your Navigation buttons where you want them AND avoid the *No Current Record* message that happens when the underlying recordset contains no records.  You can also apply some *conditional formatting* making them enable/disabled based on certain criteria.

In Figure 1, I have a Main Form with a Tab Control with several Subforms.  While you can go back and forth and edit records new records are not added here.  Using code in the On_Current event of the Subform I disable the Add New button.  (To get the custom Record Count *Treatment 1 of 1* click here.)

Figure 1

However, if you open the Subform by itself, which can do from another selection on the Main Menu, you can Add New so the Add New button becomes enabled (Figure 2)….

Figure 2

Now for the code…

What are the IsLoaded lines?  They are not required for the Navigation Buttons to work.  If you are just looking for Navigation Buttons then you can safely delete those lines.  What it does do is control the scrolling of records when the Subform is opened with the Main Form but allow the scrolling of all records if opened as a separate Subform (Main Form).  So, if you want to use your Form as a Main Form AND a Subform then you will need those lines but, of course, modify to match your field names.

Private Sub cmdFirst_Click()

On Error GoTo SmartFormError

     DoCmd.RunCommand acCmdRecordsGoToFirst

     If IsLoaded("frmTreatments") Then
            Forms![frmTreatments]![txtTreatmentDetailID] = Me.txtTreatmentDetailID
     End If

Exit_SmartFormError:
Exit Sub

SmartFormError:
    If Err = 2046 Or Err = 2501 Then
       Resume Next
    Else
       MsgBox Err.Description
       Resume Exit_SmartFormError
    End If

End Sub

Private Sub cmdPrevious_Click()

On Error GoTo SmartFormError

     DoCmd.RunCommand acCmdRecordsGoToPrevious
     
     If IsLoaded("frmTreatments") Then
            Forms![frmTreatments]![txtTreatmentDetailID] = Me.txtTreatmentDetailID
     End If

Exit_SmartFormError:
Exit Sub

SmartFormError:
    If Err = 2046 Or Err = 2501 Then
       Resume Next
    Else
       MsgBox Err.Description
       Resume Exit_SmartFormError
    End If

End Sub

Private Sub cmdNext_Click()

On Error GoTo SmartFormError

   If Me.CurrentRecord < Me.Recordset.RecordCount Then
     DoCmd.RunCommand acCmdRecordsGoToNext

     If IsLoaded("frmTreatments") Then
            Forms![frmTreatments]![txtTreatmentDetailID] = Me.txtTreatmentDetailID
     End If

   End If

Exit_SmartFormError:
Exit Sub

SmartFormError:
    If Err = 2046 Or Err = 2501 Then
       Resume Next
    Else
       MsgBox Err.Description
       Resume Exit_SmartFormError
    End If

End Sub

Private Sub cmdLast_Click()

On Error GoTo SmartFormError

If Me.CurrentRecord < Me.Recordset.RecordCount Then
     DoCmd.RunCommand acCmdRecordsGoToLast

     If IsLoaded("frmTreatments") Then
            Forms![frmTreatments]![txtTreatmentDetailID] = Me.txtTreatmentDetailID
     End If

End If

Exit_SmartFormError:
Exit Sub

SmartFormError:
    If Err = 2046 Or Err = 2501 Then
       Resume Next
    Else
       MsgBox Err.Description
       Resume Exit_SmartFormError
    End If

End Sub

Private Sub cmdAddNew_Click()

On Error GoTo SmartFormError

     DoCmd.RunCommand acCmdRecordsGoToNew

Exit_SmartFormError:
   Exit Sub

SmartFormError:
   If Err = 2046 Or Err = 2501 Then
     Resume Next
   Else
     MsgBox Err.Description
     Resume Exit_SmartFormError
End If

End Sub

In the code below be sure to change the field names under .AddNew to match the fields on your Form…

Private Sub cmdDuplicate_Click()

On Error GoTo Err_Handler
'Modified from http://www.allenbrowne.com/ser-57.html

     Dim strSQL As String
     Dim db As DAO.Database

Set db = DBEngine(0)(0)

     If Me.Dirty Then
        Me.Dirty = False
     End If

     If Me.NewRecord Then
        MsgBox "You must be on a record to proceed.", vbInformation, "Treatment"
     Else

     'Duplicate the main record
     With Me.RecordsetClone
        .AddNew
        !tdTreatmentID = Me.txtTreatmentID
        !tdTreatmentTypeID = Me.txtTreatmentTypeID
        !tdPageID = 1
        !tdPositionFlag = True
        .Update
        .Bookmark = .LastModified

'Display the duplicate.
Me.Bookmark = .LastModified
End With
End If

   Set db = Nothing
Exit_Handler:
   Exit Sub

Err_Handler:
   MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDuplicate_Click"
   Resume Exit_Handler

End Sub

Private Sub cmdDelete_Click()

On Error GoTo Err_cmdDelete_Click

     If MsgBox("You are about to Delete this record, are you sure?", vbYesNo, "Delete") = vbYes Then
        DoCmd.SetWarnings False
        DoCmd.RunCommand acCmdDeleteRecord
        DoCmd.SetWarnings True
     Else
        DoCmd.CancelEvent
     End If

Exit_cmdDelete_Click:
Exit Sub

Err_cmdDelete_Click:
MsgBox Err.Description
Resume Exit_cmdDelete_Click

End Sub

 3,276 total views,  1 views today