Experts:
I need some assistance with identifying a process flaw in an overall well-working function. Allow me to provide some background first.
Background:
- I need to compare two (2) tables: "Before" and "After".
- Initially, the "After" table is a replica of the "Before" table.
- At some point records will be updated only in the "After" table.
- Given that my actual tables may have 50+ fields and 20k plus records, I need to be able to readily identify what value was changed in the "After" table.
Attached are three (3) version of the same *sample* database. Please follow the steps as illustrated below:
Version_01:
===========
a. Open DB... in this version both tables "tbl_01_Before" and "tbl_02_After" are identical... that is, no changes have been applied to the "After" table.
b. Open form "F01_MainMenu" and click command button "View Log (Value Changes)".
c. Table "tbl_Log_ValueChanges" opens with zero (0) records.
d. Based c., this is the **correct** output given that both source tables are identifical.
Version_02:
===========
e. For testing purposes, I modified five (5) records in table [tbl_02_After]. To make it very obvious, I used 7-digit values (e.g., "1111111", "2222222", "3333333", "4444444", "5555555".
f. Open v02 and follow steps a:b.
h. Now, given the value changes, the log file shows those 5 records (incl. record ID, fieldname, before/after values).
i. AGAIN, this works great!!!
Now, the issue lies in version 03 (which is a copy of v02).
Version_03:
===========
j. Again, for testing purposes, I made two (2) additional changes to the table [tbl_02_After].
k. Specifically, I added values "88888888" in record ID #7 (field [INJ_INJURY_TYPE_TIER2_ID]) and "999999999" in record ID #15 (field [INJ_BODY_PART_TIER1_ID]).
l. Now, please note that in table [tbl_01_Before], the 'before' values for the "88888888" AND "999999999" were both = NULL.
m. So, since a value changed from NULL to something else, these two additional records should also be captured by the VBA function.
m. Unfortunately though, the log only shows the 5 records (version 2 change) but NOT those 2 records where value was previously = NULL.
My question:
How should the VBA be modified in module "modCompareTables" so that a value change from NULL to something else will also be displayed/updated in tbl_Log_ValueChanges.
P.S. Besides the 3 ACCDB versions, I have also included a PPT which illustrates the process for the 3 versions.
Thank you for your help in advance,
Tom
I need some assistance with identifying a process flaw in an overall well-working function. Allow me to provide some background first.
Background:
- I need to compare two (2) tables: "Before" and "After".
- Initially, the "After" table is a replica of the "Before" table.
- At some point records will be updated only in the "After" table.
- Given that my actual tables may have 50+ fields and 20k plus records, I need to be able to readily identify what value was changed in the "After" table.
Attached are three (3) version of the same *sample* database. Please follow the steps as illustrated below:
Version_01:
===========
a. Open DB... in this version both tables "tbl_01_Before" and "tbl_02_After" are identical... that is, no changes have been applied to the "After" table.
b. Open form "F01_MainMenu" and click command button "View Log (Value Changes)".
c. Table "tbl_Log_ValueChanges" opens with zero (0) records.
d. Based c., this is the **correct** output given that both source tables are identifical.
Version_02:
===========
e. For testing purposes, I modified five (5) records in table [tbl_02_After]. To make it very obvious, I used 7-digit values (e.g., "1111111", "2222222", "3333333", "4444444", "5555555".
f. Open v02 and follow steps a:b.
h. Now, given the value changes, the log file shows those 5 records (incl. record ID, fieldname, before/after values).
i. AGAIN, this works great!!!
Now, the issue lies in version 03 (which is a copy of v02).
Version_03:
===========
j. Again, for testing purposes, I made two (2) additional changes to the table [tbl_02_After].
k. Specifically, I added values "88888888" in record ID #7 (field [INJ_INJURY_TYPE_TIER2_ID]) and "999999999" in record ID #15 (field [INJ_BODY_PART_TIER1_ID]).
l. Now, please note that in table [tbl_01_Before], the 'before' values for the "88888888" AND "999999999" were both = NULL.
m. So, since a value changed from NULL to something else, these two additional records should also be captured by the VBA function.
m. Unfortunately though, the log only shows the 5 records (version 2 change) but NOT those 2 records where value was previously = NULL.
My question:
How should the VBA be modified in module "modCompareTables" so that a value change from NULL to something else will also be displayed/updated in tbl_Log_ValueChanges.
P.S. Besides the 3 ACCDB versions, I have also included a PPT which illustrates the process for the 3 versions.
Thank you for your help in advance,
Tom
