By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,045 Members | 1,372 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,045 IT Pros & Developers. It's quick & easy.

Logging Changes to Table

P: 109
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 multidimensional 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:
Expand|Select|Wrap|Line Numbers
  1. Dim ctl As Control
  3. For Each ctl in Me.Controls
  4.   insert code here
  5. 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:
Expand|Select|Wrap|Line Numbers
  1. Dim ctl As Control, n(1 To 2, 1 To 100) As Variant, i As Byte
  2. i = 1
  4. For Each ctl in Me.Controls
  5.   n(1, i) = ctl.Value
  6.   n(2, i) = ctl.Name
  7. 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.
Aug 3 '10 #1
Share this Question
Share on Google+
2 Replies

Expert 5K+
P: 8,706
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.
Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next
  2. Dim ctl As Control
  3. Dim strBuild As String
  4. Dim varControls As Variant
  6. For Each ctl In Me.Controls
  7.   strBuild = strBuild & Nz(ctl.Value, "N/A") & "," & ctl.Name & ","
  8. Next
  10. strBuild = Left$(strBuild, Len(strBuild) - 1)
  12. varControls = Split(strBuild, ",")
  14. 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.
Aug 3 '10 #2

P: 109
@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*

  1. ModificationID - AutoNumber(Long) (Primary Key)
  2. intComplaintNumber - 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:

Expand|Select|Wrap|Line Numbers
  1. ModificationID,intComplaintNumber,strAuthorName,strFieldName,dtModDate,txtModTime
  2. 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:

Expand|Select|Wrap|Line Numbers
  1. ModificationID,intComplaintNumber,strAuthorName,strFieldName,dtModDate,txtModTime
  2. 1,1234,"Person A","Field 1 | Field 2 | Field 4 | ",12/24/2005,"18:52:36"
  3. 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:
Expand|Select|Wrap|Line Numbers
  1. ModificationID,intComplaintNumber,strAuthorName,strFieldName,dtModDate,txtModTime
  2. 1,1234,"Person A","Field 1 | Field 2 | ",12/24/2005,"18:52:36"
  3. 3,1234,"Person A","Field 3 | Field 4 | ",12/25/2005,"00:12:18"

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.
Aug 3 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.