Good morning:
Attached ACCDB includes a mechanism to compare 2 tables (identical in structure) and output whether or not a value has changed from tbl_Legacy_Date (aka "Before" table) to tbl_Target_Data (aka "After" table).
This process works fine as long as I am NOT comparing a memo (long text) field.
Upon opening the DB, please do the following:
1. By default, DB opens with form "F01_MainMenu"
2. In the listbox, select either [AGE] OR [FIRST_NAME] OR both [AGE] AND [FIRST_NAME]. At this time (for testing only) do NOT select value [MISHAP_ANALYSIS] just yet.
3. Once either [AGE] OR [FIRST_NAME] have been selected, click command button "View Change Log".
4. The subform will now display 2 records for field [AGE] and 4 records for field [FIRST_NAME] and display the change in the next 2 columns.
Given the both source tables include 200 records, this works great as I can quickly identify what value change occurred. For testing purposes, please not that it is NOT important to identify the record ID number.
Ok, now to the problem...
- First, let's empty the change log by clicking on command button "Clear Change Log".
- Now, select value [MISHAP_ANALYSIS] from the listbox.
- Then, click on command button "View Change Log".
- At this time, I am getting VBA run-time error '3163'.
- Click on debug, brings me to line "![VALUE_BEFORE] = nField.Value".
- Finally, click CTRL+ALT_DEL to shut down Access... then re-open it.
Fixing the error:
- This error is caused given that fieldname [MISHAP_ANALYSIS] has a data type = "Long Text"
- Now, please open module "modGlobalCode" and locate function "IdentifyTablesChanges" and scroll down to "With tblDef"
Here, I have changed the following from:
Set nField = .CreateField("VALUE_BEFORE", dbText, 255)
Set nField = .CreateField("VALUE_AFTER", dbText, 255)
To:
Set nField = .CreateField("VALUE_BEFORE", dbMemo, 64000)
Set nField = .CreateField("VALUE_AFTER", dbMemo, 64000)
Unfortunately, the above change from dbText to dbMemo does not work. I'm not sure whether or not I should have included the "64000" or not.
Does anyone have a suggestion as to how the VBA needs to be modified so that I can execute the comparison for field [MISHAP_ANALYSIS] as well?
Thank you,
Tom