Help with Array (1 Viewer)

[access7]

Hi All

I have the following code on one of my forms which will help me to keep track of any changes that a user makes... this is working perfectly at the moment - a huge thanks to this forum where the code came from (have tweaked to fit my db).

The only thing is, I do not want it to look at ALL controls, I would like to add in an array so it only loops through certain controls on the form. I have tried to put an array in but I cannot for the life of me get it to work? I have arrays with other things that work OK but this one is giving me a huge headache.

I was hoping someone may be able to point me in the right direction to how I would get an array to work with this code....


Public Function AuditTrail(frm As Form)
On Error GoTo Error_Handler
Dim sAuditTable As String
Dim lsSQL As String
Dim sTable As String 'Table where the record is being edited
Dim ctrl As Control 'The control in the form being edited
Dim sFrom As String 'Original Data in the control
Dim sTo As String 'What the original data was changed to
Dim AuditCtrls As Variant

' Skips this procedure if a new record is being entered in the form

If frm.NewRecord = True Then
Exit Function
End If

' Runs through each control on the form and checks for edits/changes

For Each ctrl In frm

Select Case ctrl.ControlType 'Only checks data entry type controls.
Case acTextBox, acComboBox, acListBox, acOptionGroup

sFrom = Nz(ctrl.OldValue, "Null")
sTo = Nz(ctrl.Value, "Null")

If sFrom <> sTo Then

' Gets the required Info

sTable = frm.RecordSource

lsSQL = ""
lsSQL = lsSQL & " INSERT INTO Tbl_Amendments (CompanyRef, OnForm, FieldName, OldValue, NewValue, Who, DateChanged ) "
lsSQL = lsSQL & "VALUES ('" & ICompanyRef & "', '" & frm.Name & "', " & _
"'" & ctrl.Name & "', '" & sFrom & "', '" & sTo & "', '" & SUser & "', '" & Now() & "')"

CurrentDb.Execute (lsSQL)

End If
End Select

Next ctrl

Error_Handler_Exit:
Exit Function

Error_Handler:
MsgBox ("Error No: " & Err.Number & vbCrLf & vbCrLf & "Error Description: " & Err.Description)
Err.Clear
Resume Error_Handler_Exit

End Function

 

[sparks80]

Hi,

Rather than looping through all of the controls on the form, you can loop through each item in the array, and reference the control you want through the controls collection.

I don't have time to correct the full code at present, but I hope this gives you the idea:

Sub ReferenceControlsByArray()
  Dim ControlNameArray As Variant
  Dim ctl As Control
  Dim I As Integer
 
  ControlNameArray = Array("ControlName1", "ControlName2", "ControlName3")
 
  For I = 0 To UBound(ControlNameArray) - 1
    Set ctl = Controls(ControlNameArray(I))
    ' Get new and old value from control etc.
  Next
End Sub

 

[access7]

Appears to be working now, HUGE thank you - you wouldn't believe how long I've looked at it and how many times I've tried... and now, it's just clicked

 

[access7]

Not sure if you will be able to help with this one... just an after thought, while this appears to be working the control name doesnt mean much to the user - instead of ctrl.Name is there anyway of inserting the controls caption instead?
Thanks

 

[access7]

Nevermind... I have chosen to refer to .controlsource instead... that suits me fine. It just prevents the user from having to see 'txtfees' for example (now it just says 'fees', or 'cboprovider', now it just says 'provider'