Allowing Save Prompt With Combobox Used to Change Records-CodePudding

I need help from an advanced MS Access user who is good with VBA. I have a form with various project numbers. I have navigational buttons on my form such as Previous, Undo, Save, Refresh, Delete, Add Record, Next Record, and Save and Close.

In addition to this, I also have a combo box with a drop-down list. It displays the current project number, and when you select another number from this combo box, the form will jump to that record.

I have some code in the form’s Before Update section that prompts a user to save changes before moving to another record.

If Not (Me.NewRecord) Then
 If MsgBox("Would you like to save changes to this record?", vbQuestion   vbYesNo   vbDefaultButton1, "Save Changes to Record?") = vbNo Then
  Me.Undo
 End If
Else
 If MsgBox("Would you like to save changes to this record?", vbQuestion   vbYesNo   vbDefaultButton1, "Save this Record?") = vbNo Then
  Me.Undo
 End If
End If

It works great if you use the navigation buttons. However, when you use the combo box to change records, and you say Yes to saving changes, the record will not change and then the program gets confused by a conflict between the new number and the existing number. The script breaks. When you try to change records again, you get the message "Update or CancelUpdate without AddNew or Edit", followed by "The value violates the validation rule for the field or record."

I want to keep the option to save before jumping records, and I like using a combo box to jump records. However, is there a way to fix this bug by modifying the code a little, or by modifying my form design?

CodePudding user response：

This setup will allow saving ONLY when someone using the Save button (btnSave) or a button I made called Save and Close (btnCloseForm). Other forms of form navigation will NOT save changes.

I kept my combobox code After Update as the embedded macro created by the form wizard to retrieve the first record with the SearchforRecord function using Where Condition == "[Project Number]="&"'"& [Screen].[ActiveControl]&"'". I'm sure this part can be re-created easily with VBA code instead.

The VBA code I used in my form to make it work:

Option Compare Database
Private Saved As Boolean

Private Sub btnCloseForm_Click()
Saved = True
DoCmd.RunCommand (acCmdSaveRecord)
DoCmd.Close
End Sub

Private Sub btnSave_Click()
If MsgBox("Would you like to save changes to this record?", vbQuestion   vbYesNo   vbDefaultButton1, "Save this Record?") = vbYes Then
 Saved = True
 DoCmd.RunCommand (acCmdSaveRecord)
 Me.btnSave.Enabled = False
 Saved = False
End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Saved = False Then
 Cancel = True
 Me.Undo
 Cancel = False
End If
End Sub

Private Sub Form_Current()
Me.btnSave.Enabled = False
Saved = False
End Sub

Private Sub Form_Dirty(Cancel As Integer)
Me.btnSave.Enabled = True
MsgBox ("Please remember to save changes using the Save button.")
End Sub

CodePudding user response：

I found a second solution for this problem. I made the combobox for navigation unbound, and just added a second text box to display the project number that was bound. Using this combination, I kept the BeforeUpdate catch-all prompt to ask the user if they want to save changes.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If MsgBox("Would you like to save changes to this record?", vbQuestion   vbYesNo   vbDefaultButton1, "Save Changes to Record?") = vbNo Then
 Me.Undo
 End If
End Sub