473,326 Members | 2,114 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Logging Changes to Table

thelonelyghost
109 100+
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
  2.  
  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
  3.  
  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
2 1781
ADezii
8,834 Expert 8TB
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
  5.  
  6. For Each ctl In Me.Controls
  7.   strBuild = strBuild & Nz(ctl.Value, "N/A") & "," & ctl.Name & ","
  8. Next
  9.  
  10. strBuild = Left$(strBuild, Len(strBuild) - 1)
  11.  
  12. varControls = Split(strBuild, ",")
  13.  
  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
thelonelyghost
109 100+
@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. 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"
  3.  
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"
  4.  
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"
  4.  
</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.
Aug 3 '10 #3

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

Similar topics

3
by: LC | last post by:
hi, i worry about people doing something they shouldn't to my db and I would like to track any structural changes (who and which)to my db. I am using oracle 8.0.6.0.0 and 9.2.0.2.0. regards,...
4
by: Lukas Bradley | last post by:
It appears as if my master is not logging changes to any databases. When I use mysqlbinlog against the current (and previous) logs, I get the following: dollars@nat451:/var/log/mysql$...
1
by: Ezekiël | last post by:
hello, I was wondering how to track all changes on tables by using some sort of a history table. What i would like is a generic history table where i can see who updated, inserted, deleted or...
7
by: Carmine | last post by:
I need to import a huge .ixf file from a pc to db2 v7 zos. Is there a way to stop archive logging while the data is being imported? If not, is it safe to delete archive logs that haven't...
11
by: RdR | last post by:
Hi, I am using Q Replication, I need to set to logging to capture changes on a table but the table has more than 18 characters for the name, I looked at the docs, it mentioned that table names...
5
by: ronin 47th | last post by:
Hi group, In one of the books 'Gurus Guide to Transact SQL' i found this info: ------------------------------------------------------------ TRUNCATE TABLE empties a table without logging row...
1
by: Ronj | last post by:
I need to be compliant with law that keeps a history of changes to tables as records are edited. What is the best way to do this ? I was thinking a SQL trigger on each table that writes to another...
6
by: Ian Boyd | last post by:
Every time during development we had to make table changes, we use Control Center. Most of the time, Control Center fails. If you try to "undo all", it doesn't, and you end up losing your identity...
3
by: McKirahan | last post by:
I have a Web page that uses some JavaScript and CSS. I have specified a three column table with a fixed-width left and right side; the center colum expands to fill the page. Within this...
1
by: EgoSum | last post by:
I make dataset in c# and after that add the column into table in database. Although I expand dataset with it, app dont recognize added column. It's haped to me several times. What can I do to...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.