{"id":538,"date":"2013-02-15T02:00:49","date_gmt":"2013-02-15T02:00:49","guid":{"rendered":"http:\/\/regina-whipp.com\/blog\/?p=538"},"modified":"2016-07-08T22:45:36","modified_gmt":"2016-07-09T02:45:36","slug":"custom-navigation-buttons","status":"publish","type":"post","link":"https:\/\/regina-whipp.com\/blog\/?p=538","title":{"rendered":"Custom Navigation Buttons…"},"content":{"rendered":"

While Access provides Navigation Buttons, they must always be at the bottom of the Form.\u00a0 This does not work for my Form designs (Figure 1).\u00a0 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.\u00a0 You can also apply some *conditional formatting* making them enable\/disabled based on certain criteria.<\/p>\n

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

\"Treatments\"

Figure 1<\/p><\/div>\n

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

\"NavigationButtons1\"

Figure 2<\/p><\/div>\n

Now for the code…<\/span><\/h4>\n

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

Private Sub cmdFirst<\/strong>_Click()<\/p>\n

\r\nOn Error GoTo SmartFormError\r\n\r\n     DoCmd.RunCommand acCmdRecordsGoToFirst\r\n\r\n     If IsLoaded("frmTreatments") Then\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Forms![frmTreatments]![txtTreatmentDetailID] = Me.txtTreatmentDetailID\r\n\u00a0\u00a0\u00a0\u00a0 End If\r\n\r\nExit_SmartFormError:\r\nExit Sub\r\n\r\nSmartFormError:\r\n    If Err = 2046 Or Err = 2501 Then\r\n       Resume Next\r\n    Else\r\n       MsgBox Err.Description\r\n       Resume Exit_SmartFormError\r\n    End If\r\n<\/pre>\n
End Sub<\/p>\n
Private Sub cmdPrevious<\/strong>_Click()<\/p>\n
\r\nOn Error GoTo SmartFormError\r\n\r\n     DoCmd.RunCommand acCmdRecordsGoToPrevious\r\n     \r\n     If IsLoaded("frmTreatments") Then\r\n            Forms![frmTreatments]![txtTreatmentDetailID] = Me.txtTreatmentDetailID\r\n     End If\r\n\r\nExit_SmartFormError:\r\nExit Sub\r\n\r\nSmartFormError:\r\n    If Err = 2046 Or Err = 2501 Then\r\n       Resume Next\r\n    Else\r\n       MsgBox Err.Description\r\n       Resume Exit_SmartFormError\r\n    End If\r\n<\/pre>\n
End Sub<\/p>\n
Private Sub cmdNext<\/strong>_Click()<\/p>\n
\r\nOn Error GoTo SmartFormError\r\n\r\n   If Me.CurrentRecord &lt; Me.Recordset.RecordCount Then\r\n     DoCmd.RunCommand acCmdRecordsGoToNext\r\n\r\n     If IsLoaded("frmTreatments") Then\r\n            Forms![frmTreatments]![txtTreatmentDetailID] = Me.txtTreatmentDetailID\r\n     End If\r\n\r\n   End If\r\n\r\nExit_SmartFormError:\r\nExit Sub\r\n\r\nSmartFormError:\r\n    If Err = 2046 Or Err = 2501 Then\r\n       Resume Next\r\n    Else\r\n       MsgBox Err.Description\r\n       Resume Exit_SmartFormError\r\n    End If\r\n<\/pre>\n
End Sub<\/p>\n
Private Sub cmdLast<\/strong>_Click()<\/p>\n
\r\nOn Error GoTo SmartFormError\r\n\r\nIf Me.CurrentRecord < Me.Recordset.RecordCount Then\r\n     DoCmd.RunCommand acCmdRecordsGoToLast\r\n\r\n     If IsLoaded("frmTreatments") Then\r\n            Forms![frmTreatments]![txtTreatmentDetailID] = Me.txtTreatmentDetailID\r\n     End If\r\n\r\nEnd If\r\n\r\nExit_SmartFormError:\r\nExit Sub\r\n\r\nSmartFormError:\r\n    If Err = 2046 Or Err = 2501 Then\r\n       Resume Next\r\n    Else\r\n       MsgBox Err.Description\r\n       Resume Exit_SmartFormError\r\n    End If\r\n<\/pre>\n
End Sub<\/p>\n
Private Sub cmdAddNew<\/strong>_Click()<\/p>\n
\r\nOn Error GoTo SmartFormError\r\n\r\n     DoCmd.RunCommand acCmdRecordsGoToNew\r\n\r\nExit_SmartFormError:\r\n   Exit Sub\r\n\r\nSmartFormError:\r\n   If Err = 2046 Or Err = 2501 Then\r\n     Resume Next\r\n   Else\r\n     MsgBox Err.Description\r\n     Resume Exit_SmartFormError\r\nEnd If\r\n<\/pre>\n
End Sub<\/p>\n
In the code below be sure to change the field names under .AddNew to match the fields on your Form…<\/p>\n
Private Sub cmdDuplicate<\/strong>_Click()<\/p>\n
\r\nOn Error GoTo Err_Handler\r\n'Modified from http:\/\/www.allenbrowne.com\/ser-57.html\r\n\r\n     Dim strSQL As String\r\n     Dim db As DAO.Database\r\n\r\nSet db = DBEngine(0)(0)\r\n\r\n     If Me.Dirty Then\r\n        Me.Dirty = False\r\n     End If\r\n\r\n     If Me.NewRecord Then\r\n        MsgBox "You must be on a record to proceed.", vbInformation, "Treatment"\r\n     Else\r\n\r\n     'Duplicate the main record\r\n     With Me.RecordsetClone\r\n        .AddNew\r\n        !tdTreatmentID = Me.txtTreatmentID\r\n        !tdTreatmentTypeID = Me.txtTreatmentTypeID\r\n        !tdPageID = 1\r\n        !tdPositionFlag = True\r\n        .Update\r\n        .Bookmark = .LastModified\r\n\r\n'Display the duplicate.\r\nMe.Bookmark = .LastModified\r\nEnd With\r\nEnd If\r\n\r\n   Set db = Nothing\r\nExit_Handler:\r\n   Exit Sub\r\n\r\nErr_Handler:\r\n   MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDuplicate_Click"\r\n   Resume Exit_Handler\r\n<\/pre>\n
End Sub<\/p>\n
Private Sub cmdDelete<\/strong>_Click()<\/p>\n
\r\nOn Error GoTo Err_cmdDelete_Click\r\n\r\n     If MsgBox("You are about to Delete this record, are you sure?", vbYesNo, "Delete") = vbYes Then\r\n        DoCmd.SetWarnings False\r\n        DoCmd.RunCommand acCmdDeleteRecord\r\n        DoCmd.SetWarnings True\r\n     Else\r\n        DoCmd.CancelEvent\r\n     End If\r\n\r\nExit_cmdDelete_Click:\r\nExit Sub\r\n\r\nErr_cmdDelete_Click:\r\nMsgBox Err.Description\r\nResume Exit_cmdDelete_Click\r\n<\/pre>\n
End Sub<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
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. […]<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,63],"tags":[64,18,31,53,21],"class_list":["post-538","post","type-post","status-publish","format-standard","hentry","category-access-tips","category-database-design","tag-access-tips","tag-database-design","tag-forms","tag-navigation-buttons","tag-vba","odd"],"_links":{"self":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/538","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=538"}],"version-history":[{"count":22,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/538\/revisions"}],"predecessor-version":[{"id":1274,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/538\/revisions\/1274"}],"wp:attachment":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=538"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=538"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=538"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}