Logging Changes to Table

[thelonelyghost]

Hello everybody, lonelyghost again. No major outline here since it's just a simple question that I have no idea how to phrase for a search engine.

Program: Access 2000
OS: Windows XP Professional SP3
Problem: Recording the value and name of each control to a multidimensiona l array.

Does anyone know an easy way to cycle through all of the controls on a form in VB? I already know how to do this:

Dim ctl As Control

For Each ctl in Me.Controls
  [I]insert code here[/I]
Next

What I tried (and failed at doing) was setting ctl.Value equal to an entry in an array. The array should be fine since I'm VERY experienced with those. Here's what I tried:

Dim ctl As Control, n(1 To 2, 1 To 100) As Variant, i As Byte
i = 1

For Each ctl in Me.Controls
  n(1, i) = ctl.Value
  n(2, i) = ctl.Name
Next

It stopped with an error indicating it didn't know what ctl.Value was. I've gotten this to work in other forms as a button that clears the contents of all text and combo boxes, but I don't know what's wrong with this one.

[ADezii]

I'm not exactly sure what your ultimate goal here is, but I wrote some sample code that will place Form Control Value and Name Pairings into a Variant Array. There are several ways to accomplish this, but this is the first one that I came up with.

On Error Resume Next
Dim ctl As Control
Dim strBuild As String
Dim varControls As Variant

For Each ctl In Me.Controls
  strBuild = strBuild & Nz(ctl.Value, "N/A") & "," & ctl.Name & ","
Next

strBuild = Left$(strBuild, Len(strBuild) - 1)

varControls = Split(strBuild, ",")

Debug.Print "Number of Controls: " & (UBound(varControls) + 1) / 2

P.S. - Line #1 is the critical one that will eliminate the Error that you are receiving, since not all Controls have a 'Value' Property.

[thelonelyghost]

@ADezii: Ah, then if the array varControls starts at index 0, all of the even indexes (and zero) will be the values and the odd indexes will be the names?

I see that I forgot to state the overall purpose of this array. It is used to compare each value of each control to a previously collected set (to test each field if it's 'Dirty'), then add the field name to a string written to one delimited field in a table. Let me give an example to help describe the use. *deep breath*

<EXAMPLE>

1. ModificationID - AutoNumber(Long ) (Primary Key)
2. intComplaintNum ber - Number(Long)
3. strAuthorName - Text(50)
4. strFieldName - Text(125)
5. dtModDate - Date/Time(Short Date)
   Default: =Format(Now()," MM/DD/YYYY")
6. txtModTime - Text(8)
   Input Mask: 00:00:00;0;#
   Default: =Format(Now()," HH:MM:SS")

Person A creates a new case (record) of a customer complaint, the data entry form updates the customer complaint records while on the back-end logging who changed what. The date and time are automatically recorded and the complaint number (12345), author's name ("Person A"), and fields affected (all of them) are inserted as a new record. Here's how it would look in CSV format:

ModificationID,intComplaintNumber,strAuthorName,strFieldName,dtModDate,txtModTime
1,1234,"Person A","Field 1 | Field 2 | Field 3 | Field 4 | ",12/24/2005,"18:52:36"

Person B decides to change Field 3, therefore the records would look like this:

ModificationID,intComplaintNumber,strAuthorName,strFieldName,dtModDate,txtModTime
1,1234,"Person A","Field 1 | Field 2 | Field 4 | ",12/24/2005,"18:52:36"
2,1234,"Person B","Field 3 | ",12/24/2005,"18:57:06"

Person A decides to update some information on Field 3 and 4, ergo:

ModificationID,intComplaintNumber,strAuthorName,strFieldName,dtModDate,txtModTime
1,1234,"Person A","Field 1 | Field 2 | ",12/24/2005,"18:52:36"
3,1234,"Person A","Field 3 | Field 4 | ",12/25/2005,"00:12:18"

</EXAMPLE>

The point of the process is to only keep who did the latest revision to each field for every given record.

Method of Retrieval: Setting each control's value to a given index of an array. I then set the control names aside for the values after editing that contrast with the values before editing. Those control names are then translated into the names of the fields they affect.

Method of Storage: To concatenate all field names to one string, delimiting them by pipes "|". It can then be easily searched for using LIKE "*FieldName |*", and so on and so forth.

What I need help with is my method of retrieval by getting the values of each control for the before and after.

*gasp* Long-winded as that was, does that help you better understand what I'm trying to accomplish?

P.S. Before editing, the user presses a button to enable certain fields. After editing, the same button is pressed to disable the fields again. The commands are polar opposites in what they disable/enable but "After Editing" also runs an INSERT INTO, UPDATE, and/or DELETE query to log changes.