Saving Bound tables manually (VBA)

Question

I have a form bound to a table, but when I save, for which I have a button, I need to do some manual flag setting. The saving works, but I noticed that because it's bound, it saves anyway. How can I prevent the automatic saving, although its bound? Is there a way of interrupting before_update() or something?

I saw this link: Can I add a UI action confirmation?

so I tried to set the form_beforedate() like below:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim mIsUserUpdate As Boolean 'Flag
If Not mIsUserUpdate Then Cancel = True
End Sub

In my module I have added , right at the top :

Private mIsUserUpdate As Boolean

and in my save button I have this function:

Private Sub cmdSave_Click()
Dim strName As String

strName = Me.Name
SaveTabs Forms(strName)

End Sub

where the Savetabs is in the module looking like this:

Function SaveTabs(frm As Form) As Integer

Set dbs = CurrentDb()

tbl = "SELECT " &.......
Set tdf = dbs.OpenRecordset(tbl)

ArrKnownFields = Getknownfields(tdf)

'Folgend, der Update-Befehl..
For Each ctrl In frm
    If IsInArray(ctrl.Name, ArrKnownFields) Then
        If Not IsNull(ctrl.Value) Then
            strSQL = strSQL & "[" & ctrl.Name & "] = '" & ctrl.Value & "',"
        Else
            strSQL = strSQL & "[" & ctrl.Name & "] = ' ',"
        End If
    End If
Next ctrl

If Forms!frmMainMenu.cboDatachoice.ListIndex = 0 Then
    strSQL = strSQL & "1_Val= '" & gUser & "'" 
    If Len(strSQL) > 0 Then
        strSQL = "UPDATE END_CFR_" & strName & " SET " & strSQL & " WHERE "
        
        If strName = "Col" Then
            strSQL = strSQL & "R_IDCC ='" & frm.txtID.Value & "'"
        ElseIf strName = "Fac" Then
            strSQL = strSQL & "R_IDFF ='" & frm.txtID.Value & "'"
        ElseIf strName = "Deb" Then
            strSQL = strSQL & "R_IDFD ='" & frm.txtID.Value & "'"
        End If
    End If

    **mIsUserUpdate = True**  'flag ON

    frm.Dirty = False
    CurrentDb.Execute strSQL, dbFailOnError

So because I'm setting "1_Val" with gUser, I'm having to do this manually.

Unfortunately, the frm.Dirty = False, forces the activation of form_beforeUpdate, but for some reason the mIsUserUpdate is suddenly false again, although I set it to true like above..

So what am I doing wrong? As I still get the error 2101 because frm.dirty= false is not working.

Before you ask, I have it bound so that I can see the number of datasets and use the arrows to scroll through datasets, plus it's easier to fill the various text fields and combo boxes when it's bound.

I'm using access 2021 by the way.

Thanks in advance

Answer 1

You can use the Form.Undo method to undo the changes in your form.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.Dirty Then
        If MsgBox("Save the changes?", vbYesNo + vbQuestion, "Save Changes") = vbNo Then
            Me.Undo
        End If
    End If
End Sub

Or by using your flag

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim mIsUserUpdate As Boolean

    'TODO: Set or clear the flag

    If Not mIsUserUpdate Then
        Me.Undo
    End If
End Sub