Loop through controls to edit

Question

Hi All,

 I have an unbound form (frmProperty) and a search form. I open the search form, select the desired record from the list box which inturn populates the property form.

On the search form I call a Sub to write some data (initial data) to a temp table for an audit. That now works fine. It gives me the initial values of each control written to the Temp Audit Table.

What I want to do now is write the data for each control when it is saved. i.e. any changes made will be written to the same table.

'EDIT' (will change name later) is passed when the sub is called from the search form:

Call AuditChanges(IDfield, "Edit")

After the user has edited records etc. and clicks the save button I want to save the new value in [AuditNewValue] alongside the old value.

I know what I have below under Case Else is wrong and doesn't work but I've tried everything I can think of.

I can loop through the controls and add a record in the AuditTemp table for each control along with the old value(initial value), but I want to now loop through the same controls and add the new value next to the old value.

Any assistance would be appreciated. I guess it's possible but beyond me.

The code I have is:

Public Sub AuditChanges(IDfield As Integer, UserAction As String)

    On Error GoTo AuditChanges_Err

    Dim rs As DAO.Recordset

    Dim ctl As control

    Dim datTimeCheck As Date

    Dim strUserID As String

    datTimeCheck = Now()

    strUserID = TempVars!tempLoginFullName

    Select Case UserAction

    Case "EDIT"

            For Each ctl In Screen.ActiveForm.Controls

                If ctl.Tag = "Audit" Then

                If IDfield > 0 Then

                  '  If Nz(ctl.Value) <> Nz(ctl.Oldvalue) Then

                        Set rs = CurrentDb.OpenRecordset("select * from tblAuditTemp;")

            With rs

                            .AddNew

                            ![AuditDteTm] = datTimeCheck

                            ![AuditUserNmID] = strUserID

                            ![AuditFormNm] = Screen.ActiveForm.Name

                            ![AuditAction] = UserAction

                            ![AuditRecordID] = IDfield

                            ![auditfieldnm] = ctl.StatusBarText

                            ![AuditOldValue] = ctl.Value

                            .Update

                        End With

                    End If

                End If

            Next ctl

Case Else

For Each ctl In Screen.ActiveForm.Controls

                If ctl.Tag = "Audit" Then

                If IDfield > 0 Then

                Set rs = CurrentDb.OpenRecordset("select * from tblAuditTemp")

            With rs

                        .edit

                        ![AuditDteTm] = datTimeCheck

                        ![AuditUserNmID] = strUserID

                        ![AuditFormNm] = Screen.ActiveForm.Name

                        ![AuditAction] = UserAction

                        ![AuditRecordID] = IDfield

                        ![auditfieldnm] = ctl.StatusBarText

                        ![AuditNewValue] = ctl.Value

                        .Update

            End With

                    End If

                End If

            Next ctl

    End Select

AuditChanges_Exit:

    On Error Resume Next

    rs.Close

    Set rs = Nothing

    Exit Sub

AuditChanges_Err:

    MsgBox Err.Description, vbCritical, "ERROR!"

    Resume AuditChanges_Exit

End Sub

Answer 1

Hi Tom,

The following is what I have come up with. It is still a work in progress but is working well on all forms and form any text/combo's

I know enough to get things working but not necessarily the best coding/practices so if you hae any suggestions it would be welcomed:

Table:

AuditIDS Autonumber

AuditDteTm Date/Time

AuditUserEditNm Short Text

AuditFormNm Number (will change to AuditFormNmID)

AuditRecordID Number

AuditOldValue ShortText

AuditNewVaue ShortText

AuditAction ShortText

I have the following code on the save button code and on the search form to call the Sub and record the values both when the user first populates the property form and once any changes have been made

Forms!frmproperty.SetFocus

 Dim IDfield As Integer

 Dim FormID As Integer

 FormID = 2

 IDfield = Forms!frmproperty.txtPropertyIDS

 Call AuditChanges(IDfield, "Edit", FormID)

On any fields on the property form I want to 'Audit' I have: (in this case on the property name field)

Status Bar Text: PropertyName

Tag: PropertyName

I can probably avoid using the Status bar text and only use the Tag? 

In either case, the tag and status bar text cannot contain spaces in the name

SUb Code:

Public Sub AuditChanges(IDfield As Integer, UserAction As String, FormID As Integer)

    On Error GoTo AuditChanges_Err

    Dim ctlA As control

    Dim datTimeCheck As Date

    Dim strUserID As String

    datTimeCheck = Now()

    strUserID = TempVars!tempLoginFullName

    Select Case UserAction

Case "EDIT"

Dim rs2 As DAO.Recordset

            For Each ctlA In Screen.ActiveForm.Controls

            Debug.Print ctlA.Name & ":" & ctlA.ControlType

                If ctlA.Tag > "" Then

                If IDfield > 0 Then

                            Set rs2 = CurrentDb.OpenRecordset("select * from tblAuditTemp;")

                        With rs2

                            .AddNew

                            ![AuditDteTm] = datTimeCheck

                        '    ![AuditUserInitNm] = strUserID

                           ' ![AuditFormNm] = Screen.ActiveForm.Name

                           ![AuditFormNm] = FormID

                            ![AuditAction] = UserAction

                            ![AuditRecordID] = IDfield

                            ![auditfieldnm] = ctlA.StatusBarText

                            If ctlA.ControlType = 111 Then

                            ctlA.SetFocus

                            ![AuditOldValue] = ctlA.Text

                            Else

                            ![AuditOldValue] = ctlA.Value

                            End If

                            .Update

                        End With

                    End If

                End If

            Next ctlA

            rs2.Close

Set rs2 = Nothing

Case "Saved"

    Dim ctl As control

        For Each ctl In Screen.ActiveForm.Controls

        Dim rs1 As DAO.Recordset

                Set rs1 = CurrentDb.OpenRecordset("select * from tblAuditTemp")

                    rs1.MoveFirst

                Do While Not rs1.EOF

                        If rs1![auditfieldnm] = ctl.Tag Then

                            rs1.Edit

                            rs1![AuditUserEditNm] = strUserID

                        If ctl.ControlType = 111 Then

                            ctl.SetFocus

                        rs1![AuditNewValue] = ctl.Text

                            Else

                        rs1![AuditNewValue] = ctl.Value

                        End If

                    rs1.Update

                        End If

                    rs1.MoveNext

                Loop

                  '  rs1.Close

                  '  Set rst = Nothing

                Next ctl

                rs1.Close

Set rs1 = Nothing

            Case Else

End Select

AuditChanges_Exit:

    On Error Resume Next

Because some of the fields will be combo's and some text I have had to add:

If ctl.ControlType = 111 Then

                            ctl.SetFocus

                        rs1![AuditNewValue] = ctl.Text

which checks to see if it is a combo (which is 111) and if it is, gives me the text value rather than the ID, which it would do if I had just ctl.value. Also, using ctl.text will not work unless the field has focus so I added ctl.setfocus

I haven't had any problems with what I have and it seems to work well for any fields (text or combo)on any form with just the addition (and slight modification) of the save and search code and adding tag and status bar text

As I said, any suggestions on how to better my code would be welcomed.

Answer 2

If you tell us what you did to fix it, for the benefit of the community.

Answer 3

Would the moderator please mark this as answered/not required. I now have it working perfectly.

Answer 4

Thanks Tom, I am aware of methods I could use for another (bound form) database but it doesn't help with what I presently have.

Any assistance with the question I have actually posted would be appreciated

Answer 5

Unbound frmProperty is a mistake. You're doing way too much work.

Audit Trail is now handled with data macros in the BE. Read up on it.