Custom Navigation buttons stop working when form is edited

[PlayerPianoMan]

I have a main form (frmLighthouse) which contains a subform (frmSubLHRepairs). I have just recently switched from Microsoft Office 365 32bit to 64bit. When I open the form and edit a field my custom navigation buttons no longer work. The code for the buttons to go to the first, next, previous and last record are all based on the Do.CmdRunCommand acCmdRecordsGoToxxx. I also have code in the forms Current Event which if the first record is on the form then the Buttons for Go To First record and Go To Previous record are disabled. The same if the last record is displayed the Go To Last and Go To Next buttons are disable otherwise all of the forms are active.

What happens is when the form loads and I edit any field on the form the custom navigation buttons no longer work. Any ideas? I have also enclosed a screen shot of the form.

Private Sub btnLastRecord_Click()
'Comments : Go to last record
'Parameters :
'Created : 04/01/2021
'Modified : 06/11/2023
'



DoCmd.RunCommand acCmdRecordsGoToLast
Me.LHName.SetFocus

End Sub



Private Sub Form_Current()
'Comments :
'Parameters :
'Created : 04/21/2021
'Modified : 06/10/2023
'

Dim intRetval As Integer
Dim Records As DAO.Recordset
Dim dbs As Database
Dim RecordCount As Long
Dim SRecordCount As String

Set dbs = CurrentDb

With Me
If RecordsetClone.RecordCount > 0 Then
.RecordsetClone.MoveLast
End If
!txtRecordCount = "Lighthouse " & .CurrentRecord & " of " & .RecordsetClone.RecordCount
End With

If Me.CurrentRecord = 1 Then
Me.btnPreviousRecord.Enabled = False
Me.btnFirstRecord.Enabled = False
Else
Me.btnPreviousRecord.Enabled = True
Me.btnFirstRecord.Enabled = True
End If

If Me.CurrentRecord = Me.RecordsetClone.RecordCount Or Me.NewRecord = True Then
Me.btnNextRecord.Enabled = False
Me.btnLastRecord.Enabled = False
Else
Me.btnNextRecord.Enabled = True
Me.btnLastRecord.Enabled = True
End If

If LHCountry = "Canada" Then
Me.LHCountry.Enabled = False
End If

If LHCountry = "United States" Then
Me.LHCountry.Enabled = False
End If

If LHCountry = " " Then
Me.LHCountry.Enabled = True
End If

If tbYesNoBox Then
tbYesNoBox.Caption = "P" '
Else
tbYesNoBox.Caption = ""
End If

If tbYesNoCertificate Then
tbYesNoCertificate.Caption = "P"
Else
tbYesNoCertificate.Caption = ""
End If

If tbYesNoRepair Then
tbYesNoRepair.Caption = "P"
Me.LHRepairNotes.Enabled = True
Else
tbYesNoRepair.Caption = ""
Me.LHRepairNotes.Enabled = False
End If

If tbTentCard Then
tbTentCard.Caption = "P"
Else
tbTentCard.Caption = ""
End If

If tbMedallion Then
tbMedallion.Caption = "P"
Else
tbMedallion.Caption = ""
End If

If tb4Sale Then
tb4Sale.Caption = "P"
Else
tb4Sale.Caption = ""
End If

If tbSigned Then
tbSigned.Caption = "P"
Else
tbSigned.Caption = ""
End If

Set dbs = Nothing

Me.btnExitForm.FontBold = False
Me.LHCatNumber.SetFocus

End Sub

[moke123]

Here's a custom class for navigation buttons. At the very least you can see how to handle the BOF and EOF events.

[Welshgasman]

Please use code tags when posting code. They keep the indentation and make it easier to read and understand.

[PlayerPianoMan]

Thanks! I am new here!

[PlayerPianoMan]

Thanks for the code. IT is quite a bit of code for a small database. In your sample database when the first record is displayed the Go Go First button (<<) is still active. It is the same if the last record is displayed (>>) it also is still active. Should these not be disabled as well. Also why if I edit on of the records does that cause the navigation buttons to no longer work?

[moke123]

Not sure we have enough info to guess why the buttons don't work after an edit. Do you have other code in the form? Does it compile?

A few things don't look right to me but it's hard to tell if it's causing your problem.

why use DoCmd.RunCommand acCmdRecordsGoToLast ?

Try DoCmd.GoToRecord , , acLast

The reason I pointed you to the class code was to show you the .Recordset.AbsolutePosition .
When it equals 0 you are at the first record and there are no previous records. Absoluteposition is Zero based.
When your Recordset.RecordCount =Recordset.absoluteposition + 1 you are at the last record.
It's the easiest way to know where you are in your forms recordset.

IT is quite a bit of code for a small database.

Not really. What it is is a custom class. The idea is you write the code once and then you can use it easily in any project. Works on main forms and subforms.
The class module only needs to be copied into your project. You really don't even need to understand it.
Only the previous and next buttons are required. All the rest are optional. Just pass your button names to the class.
If you look at the buttons in my example, there is no code in any of the buttons. Everything is handled by the class.

To use it you only need 3 lines of code. 1 in your declarations (top of module) and 2 in the form load event.

Dim clsNB As clsFormNavBar


Private Sub Form_Load()


    Set clsNB = New clsFormNavBar
    clsNB.InitCls Me, Me.cmdPrev, Me.cmdNext, Me.cmdFirst, Me.cmdLast, Me.cmdNew, Me.LblCount, Disable     'You pass your button names and choose your options in this line

End Sub

I never gave much thought to the First and Last buttons (<< and >>) as they only go to the first and last records.
I just added the disable to those buttons in my class. If you want the updated code let me know.

[PlayerPianoMan]

Yes please send the updated code. I originally had Try DoCmd.GoToRecord , , acLast etc. for the buttons, I am going to go back to that to see what happens. The code compiles fine.I am guessing that when an edit occurs something is the execution order is keeping the buttons from working. I will plug in you solution and see it that works to fix the issue.

[moke123]

Could not replace the version above, so here's the new version

[PlayerPianoMan]

I had to rebuild the form from scratch. There was a typo in the commends of the module section that you sent and I was able to ferret that out. My form has the Navigation Buttons property set to No but when I do this the code that you gave me no longer works. I there a way to fix this?

[moke123]

My form has the Navigation Buttons property set to No but when I do this the code that you gave me no longer works.

The Navigation Buttons property makes no difference to the code.

Dim clsNB As cNavigation

Yes, a mistake. An old copy and paste error. That was the class name some time ago.

It is correct above in post#6.

I see you have a couple of navigation buttons on your form but I can't really tell its structure. Subforms?
My code will work for all of them when set up correctly.

Perhaps post a copy of your Db with the form and a few dummy records.
This will also help to answer your first question about editing.

[PlayerPianoMan]

I just needed confirmation because I scoured the code and came to the same conclusion. I have attached a zip file of the database. If I open frmLighthouse in design view, open form properties, on the format tab if I set Navigation Buttons to No then save and close the form. When I reopen the form the navigation buttons are all disabled. If I reverse the process they again work.

[PlayerPianoMan]

Also when I rebuilt the form and installed your code the editing issue seems to be ok for now.

[moke123]

It appears to work fine for me except for the the issue of the navigation buttons property. That's really odd.
I checked a few of my files and that property has no effect in mine. I can't recreate it.

That's a form property so I have no idea why it would have any effect on those buttons. I've never seen that before.
That property should only hide or show the nav buttons at the bottom of the form.

I hate to suggest it but the only thing I can think of is some sort of corruption. Hope I'm wrong.

[PlayerPianoMan]

That is why I tried to recreate the form from scratch. Would this corruption be in the form, or the source table or access itself? I just moved from Microsoft Office 365 32 bit to 64 bit last week. Maybe I will create a new database recreate the table and the form and see if it works then. If not would this indicate that something in Access is corrupted? Is there a way to just reinstall Access and not the whole Office? My son (who is also a computer programmer) and I have a saying "We make I have never seen that before possible on a daily basis!" I am used to weird but this is really weird.

[moke123]

I doubt it is Office or Access as I have the same problem with your Db but no others.