Solved Need to add new field to Log (table)

[EEH_Help]

Hello:

Several weeks ago, expert @MajP helped me create a log file. @MajP's solution works great!

At this time, however, I'd like to obtain some help with slightly modifying the existing VBA in order to insert a new field into the working routine. Attached are 2 ACCDB files.

Version #1 - Background:
- The purpose of the DB is to compare 2 tables (identical in structure) and then output any value difference in a form (subform).
- Upon opening the DB, the form "F01_MainMenu" opens by default.

Version #1 - Process:
1. Select any (or all) of the 3 values from the listbox.
2. Click on command button "View Change Log".
3. The subform now displays 4 records (incl. [LEGACY_FIELDNAME], [LEGACY_VALUE], and [TARGET_VALUE].
4. Click on command button "Clear Change Log" will then empty the log.

Again, this works great... *no* changes are required in "Version 1" ... now to Version #2 though.

Version #2 - Background:
1. Essentially, version 2 is a copy of version 1.
2. In this version, however, I have added one (1) new field [FIELDNAME_CATEGORY] in both tables [00_tbl_Fieldnames] and [01_tbl_Log_ValueChanges].
3. I added sample values of "TD", "LK", and "FF" to the existing 3 records in table "00_tbl_Fieldnames".

Version #2 -- Envisioned Change:
1. Same process execution applies. That is, upon selecting any/all of the 3 listbox values, click "View Change Log".
2. At this point, however, I am getting a compile error "Invalid qualifier".

I made in the following changes in the VBA. Unfortunately, these changes are resulting in the compile error. The changes were applied to module "modCompareTables" | sub "UpdateLog".

a. Added "Dim nFieldnameCategory As String"
b. Added "![FIELDNAME_CATEGORY] = nFieldnameCategory.Name" to the "With rs_ChangeLog" routine.

In VBA form:

Sub UpdateLog(TableFieldnames As String)

Dim nFieldnameCategory As String                                         '** This is the new field
     
'... more code

With rs_ChangeLog
.AddNew
   ![CHANGED_RECORD_ID] = rs_After![ID]
   ![FIELDNAME] = nField.Name
   ![FIELDNAME_CATEGORY] = nFieldnameCategory.Name               '** This is the new field
   ![VALUE_BEFORE] = nField.Value
   ![VALUE_AFTER] = rs_After.Fields(nField.Name).Value
   ![FIELDNAME] = TableFieldnames
.Update

'... more code

What I want to achieve:
- Upon clicking the command button in the form, I also want to display values "TD" or "LK" or "FF" in field [FIELDNAME_CATEGORY] in the subform.
- As per version #1, I currently display the fieldname, legacy value, and target value; however, having this one additional category field would be extremely useful information.

My question:
How should the above VBA be tweaked (sub "UpdateLog") so that the associated field categories (i.e., "TD" or "LK" or "FF") will be displayed for any of the 3 fieldnames?

 

[moke123]

When you mention a particular member if you tag their name with @ it will notify them of the post.
So to notify MajP you would type @MajP (his name will appear kind of like intellisense) and if you mouse over it, his profile info will pop up.

 

[EEH_Help]

Thank you @moke123...I appreciate the info. I edited the original post to include the tag.

 

[MajP]

  ![FIELDNAME_CATEGORY] = DLookup("FieldName_Category", "00_tbl_FieldNames", "FieldName = '" & nField.Name & "'")

To be clear, the log file code is not mine. I think I just wrapped one line of code with the NZ function.

 

[EEH_Help]

That worked great! Thank you, @MajP.