Compare Form with Table (1 Viewer)

kirkm

Registered User.
Local time
Today, 20:10
Joined
Oct 30, 2008
Messages
1,257
Just wondering how best this might be done.
Controls on my Form are bound to a query. When (if) they're updated I want to show any changes before they're applied.
As I understand it, the data is only updated when the Form is closed, or moved to a new Record, or Me.Dirty= False is applied.
So data can change on the Form and the underlying table isn't updated (yet)?

If correct my thinking was to open a recordset to the Forms Record Source and compare each field and its control value in a loop.
Can I use a loop index or the control names ? Or is this the wrong approach altogether?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:10
Joined
Oct 29, 2018
Messages
21,358
Hi. You said, "I want to show the changes." What does that mean? Show the changes where?
 

kirkm

Registered User.
Local time
Today, 20:10
Joined
Oct 30, 2008
Messages
1,257
Probably a Msgbox, or a popup Form. Just a prompt showing what's going to happen.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:10
Joined
Oct 29, 2018
Messages
21,358
Probably a Msgbox, or a popup Form. Just a prompt showing what's going to happen.
Okay, that's the what and where. Now, what's the when? As in, when do you want to show the changes? What do you mean by "what's going to happen?"
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:10
Joined
May 7, 2009
Messages
19,169
yes, you need to Loop to each control on the form and
the best event to do it is on the Form's BeforeUpdate:




=
Code:
private sub form_beforeupdate(cancel as integer)
    Dim ctl As Control
    Dim source As String
    Dim msg As String
    For Each ctl In Me.Controls
        If TypeOf ctl Is TextBox Or _
            TypeOf ctl Is ComboBox Or _
            TypeOf ctl Is ListBox Or _
            TypeOf ctl Is OptionButton Or _
            TypeOf ctl Is CheckBox Then
            On Error Resume Next
            source = ctl.ControlSource
            If Err.Number = 0 Then
                If Left(source, 1) <> "=" Then
                    If ctl.OldValue <> ctl.Value Then
                        msg = msg & ctl.Name & " OldValue = " & ctl.OldValue & ", NewValue = " & ctl.Value & vbCrLf
                    End If
                End If
            End If
            Err.Clear
            On Error GoTo 0
        End If
    Next
    If Len(msg) > 0 Then
        MsgBox msg
    End If
end sub
 

kirkm

Registered User.
Local time
Today, 20:10
Joined
Oct 30, 2008
Messages
1,257
Thanks arne, I shall implement exactly that, as long as the update can be cancelled. Is that what cancel as integer might do?
DBGuy, to be shown when the User clicks the Update button. A visual presentation of what will change (from the original values to the new) . The user can look at this, getting confirmation of the fields involved and/or be alerted to any unwanted changes that could be corrected or cancelled.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:10
Joined
May 7, 2009
Messages
19,169
you Assign True to Cancel, to cancel the Update.

Cancel = True

otherwise, no need to add code if you want the Update to push through.
Code:
private sub form_beforeupdate(cancel as integer)
    Dim ctl As Control
    Dim source As String
    Dim msg As String
    For Each ctl In Me.Controls
        If TypeOf ctl Is TextBox Or _
            TypeOf ctl Is ComboBox Or _
            TypeOf ctl Is ListBox Or _
            TypeOf ctl Is OptionButton Or _
            TypeOf ctl Is CheckBox Then
            On Error Resume Next
            source = ctl.ControlSource
            If Err.Number = 0 Then
                If Left(source, 1) <> "=" Then
                    If ctl.OldValue <> ctl.Value Then
                        msg = msg & ctl.Name & " OldValue = " & ctl.OldValue & ", NewValue = " & ctl.Value & vbCrLf
                    End If
                End If
            End If
            Err.Clear
            On Error GoTo 0
        End If
    Next
    If Len(msg) > 0 Then
        if MsgBox("Changes have been made to this record:" & vbcrlf & vbcrlf & _
                msg & vbcrlf & vbcrlf & "Save Changes?", _
                vbQuestion + vbYesNo) = vbNo Then
            Cancel = True
        End If
    End If
end sub
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:10
Joined
Oct 29, 2018
Messages
21,358
Thanks arne, I shall implement exactly that, as long as the update can be cancelled. Is that what cancel as integer might do?
DBGuy, to be shown when the User clicks the Update button. A visual presentation of what will change (from the original values to the new) . The user can look at this, getting confirmation of the fields involved and/or be alerted to any unwanted changes that could be corrected or cancelled.
Hi. Thanks for the clarification. @arnelgp's approach should get you there. Cheers!
 

kirkm

Registered User.
Local time
Today, 20:10
Joined
Oct 30, 2008
Messages
1,257
That's great. Thanks to you both. I'm keen to get this working.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:10
Joined
May 7, 2009
Messages
19,169
the code is for "Old" and "New" record, if you want to process only "old" records, add condition:

Private sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord = False Then
'the inner code here
End If
End Sub
 

kirkm

Registered User.
Local time
Today, 20:10
Joined
Oct 30, 2008
Messages
1,257
Interesting. In my Update button code I thought "Me.Dirty=False" would force a write to the table and call form_beforeupdate first.
But it doesn't. It does run when I close the Form, but I don't want to close the Form, and can't move to any Next Record. What might be a good solution?
 

kirkm

Registered User.
Local time
Today, 20:10
Joined
Oct 30, 2008
Messages
1,257
arne, does Me.NewRecord = False mean no changes have been made to the Form and it still contains the same values as the underlying table?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:10
Joined
May 7, 2009
Messages
19,169
it means the Record Pointer is on the "old record/ existing record". If you are on "New Record", this will be True.
it does not tell whether there there are changes on your record.
 

kirkm

Registered User.
Local time
Today, 20:10
Joined
Oct 30, 2008
Messages
1,257
OK arne. There's only one Record. That is, until the user gets another one themselves.
I'm having trouble calling sub form_beforeupdate as it errors with Argument not optional
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:10
Joined
May 7, 2009
Messages
19,169
this is the Change i made:

..
..
If (ctl.OldValue & "") <> (ctl.Value & "") Then
..
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim ctl As Control
    Dim source As String
    Dim msg As String
    For Each ctl In Me.Controls
        If TypeOf ctl Is TextBox Or _
            TypeOf ctl Is ComboBox Or _
            TypeOf ctl Is ListBox Or _
            TypeOf ctl Is OptionButton Or _
            TypeOf ctl Is CheckBox Then
            On Error Resume Next
            source = ctl.ControlSource
            If Err.Number = 0 Then
                If Left(source, 1) <> "=" Then
                    If (ctl.OldValue & "") <> (ctl.Value & "") Then
                        msg = msg & ctl.Name & " OldValue = " & ctl.OldValue & ", NewValue = " & ctl.Value & vbCrLf
                    End If
                End If
            End If
            Err.Clear
            On Error GoTo 0
        End If
    Next
    If Len(msg) > 0 Then
        If MsgBox("Changes have been made to this record:" & vbCrLf & vbCrLf & _
                msg & vbCrLf & vbCrLf & "Save Changes?", _
                vbQuestion + vbYesNo) = vbNo Then
            Cancel = True
            Me.Undo
        End If
    End If
End Sub
 

Users who are viewing this thread

Top Bottom