Concat all Changes and Show in Save Confirmation Alert

[maxpirate]

How do i concat all changes made on a record and show it in the save confirmation alert?

** Edit** Was split from Customize save menu item in access data entry form

[TheSmileyCoder]

There are several approaches to doing this. One could be be to make a loop running through each control, recording the changes, adding them to some string, and finally displaying the string. What I have sometimes done is to simply highlight the fields changed.

Private Sub HighlightChanges(boolTrue)
    Dim ctrl As Control
    
    For Each ctrl In Me.Controls
        If (ctrl.ControlType = acComboBox Or ctrl.ControlType = acTextBox) And Not ctrl.Name = "cmb_MainGroup" Then

            If boolTrue Then
                    'True means highlight changes
                    If IIf(IsNull(ctrl.Value), "NULLButCheckMeAnyway", ctrl.Value) <> IIf(IsNull(ctrl.OldValue), "NULLButCheckMeAnyway", ctrl.OldValue) Then
                        'If it has changed from its originalvalue highligth it
                        ctrl.BorderColor = vbRed
                        ctrl.BorderWidth = 3
                    End If
                Else
                    'False means return to normal view
                    ctrl.BorderColor = 0
                    ctrl.BorderWidth = 1
            End If
            
        End If
    Next
End Sub

Then I simply call this function in beforeUpdate before I ask the user, and I call it again in Form_Current with the false argument "restting" everything back to normal.

[maxpirate]

when u call highlightchange s in before update what do yu pass as argument,...boo lean true?

[maxpirate]

i passed boolean true as argument and the highlight was not done
but the code gets executed where
# ctrl.BorderColo r = vbRed

[TheSmileyCoder]

What do you mean by executed?
And if you have made any changes to the code, please post the code here. Do you have unbound fields on the form?

Do you have calculated fields in the form?

[maxpirate]

ctrl.BorderColo r = vbRed when this line of code gets excuted there is no colour chages on the form. i don have unbound or calculated values on the form.

[TheSmileyCoder]

Im guessing you have border style set as transparent then :)

[maxpirate]

can u provide code for concatenating the changed values in string

[TheSmileyCoder]

Well that would be very form specific, for instance do you have memo fields in your tables? Making a change string on a memo field could be rather ugly.

Also how do you want to display changes on combobox's? Should they show the value change, which will often be some foreign key? (atleast in my designs), or should they first column? or? or?

Well I've thrown this together for you. It only handles the combobox, since that the most complicated issue. You can add code to handle the textbox and checkbox case yourself I hope.

Public Function fncChanges(myForm As Form) As String
Dim ctrl As Control
Dim strChanges As String
Dim intChanges As Integer
Dim intI As Integer

For Each ctrl In myForm.Controls
    If Not myForm.Dirty Then
        strChanges = "No changes Made"
        fncChanges = strChanges
        Exit Function
    End If
    
    'accombobox
    If ctrl.ControlType = acComboBox Then
        'Is it bound?
        If (ctrl.ControlSource & "") <> "" Then
            'Yes it is
            
            'Has anything changed?
            If (ctrl.OldValue & "") <> (ctrl.Value & "") Then
                'Does it have more then 1 column?
                If ctrl.ColumnCount = 1 Then
                    'It only has 1, probably a simple combobox
                    strChanges = strChanges & "Field [" & ctrl.ControlSource & "] has changed from {" & ctrl.OldValue & "} to {" & ctrl.Value & "}" & vbNewLine
                    intChanges = intChanges + 1
                Else
                    'It has more then 1
                    
                    'Are column widths specified?
                        If (ctrl.ColumnWidths & "") <> "" Then
                            'They are, retrieve first
                            
                            For intI = 0 To InStr(1, ctrl.ColumnWidths, ";")
                                If Split(ctrl.ColumnWidths, ";")(intI) <> 0 Then
                                    strChanges = strChanges & "Field [" & ctrl.ControlSource & "] has changed to {" & ctrl.Column(intI) & "}" & vbNewLine 'Note the ctrl.oldvalue wont do much good on a combobox
                                    Exit For
                                End If
                            Next
                            
                            
                            Else
                            'Haven't figured out have ot handle this case.
                            strChanges = strChanges & "Bad case handle on Field [" & ctrl.ControlSource & "]" & vbNewLine
                            intChanges = intChanges + 1
                        End If
                    
                    
                End If
            End If
            
            
        End If
    End If
Next
    fncChanges = strChanges
End Function

I have made it so that if hte combobox doesn't have a controlsource (unbound) its ignored. Also the combobox will "record" the changed value of the first visible field (i.e. column width > 0)

[maxpirate]

thanks for the code smiley one ..

[NeoPa]

I've had to split this into it's own separate thread as it was an entirely separate question. Please remember in future to keep questions in their own threads please.

You pleasant job now, Max, is to select whichever of TheSmileyOne's posts below you feel best captures the answer for this thread.