VBA modification to allow listbox's multi-select = "Extended" (1 Viewer)

EEH_Help

Member
Local time
Today, 19:30
Joined
Feb 15, 2020
Messages
32
Good morning:

I am curious to learn if someone has a specific recommendation for modifying existing (working) VBA code IOT to integrate two (2) independent processes. Attached are 2 sample DBs.

==== DB #1 ============================

"01 Loop" - Background:
- Table [tbl_Products].[PRODUCT_TABLE] contains 3 DISTINCT products.
- Total # of records = 9 where each "product" has 3 values in field [AFSAS_FIELDNAME].

"01 Loop" - Process:
- Open form "F01_MainMenu"; listbox "lstProducts" is set to multi-select = 'Extended'.
- Select any (one or more) items from the listbox and then click command button.
- For each item (product) selected in the listbox, 3 records will be appended to table [tbl_Validation].

... this works great and does NOT any modification.


==== DB #2 ============================

"02 Compare" - Background:
- Table [00_tbl_Products] contains 3 records (one for each "product").
- Table [01_tbl_Log_ValueChanges] is dynamically re-created (depending on value selection in form).
- 3 tables starting with prefix "tbl_"... these may be considered "after" tables.
- 3 tables starting with prefix "tbl_Step_4_" ... these may be considered "before" tables.

"02 Compare" - Process:
- Open form "F01_MainMenu"; listbox "lstProducts" is set to multi-select = 'None'.
- Select one (1) item from the listbox and then click command button.
- For any item selected from the listbox, [01_tbl_Log_ValueChanges] summarizes changes between the "before" and "after" table(s).

... this also works great and does NOT any modification.

=======================================

At this time, however, I'd like to tweak/modify the VBA of DB "02 Compare" (currently, with multi-select listbox = 'None') and include the method from DB "01 Loop" (with multi-select listbox = 'Extended').

Envisioned process:
1. In DB "02 Compare", open form F01_MainMenu.
2. Select one or more of the products (items) in the listbox; click command button.
3. Now, just like in DB #1, the function "scans" through the selected listbox items and **appends** matching records to table [tbl_Validation].
4. So, for DB #2, I also would like to **append** all records (with differences between "before" and "after" tables) to [01_tbl_Log_ValueChanges].
5. Ultimately, this would give me a complete view of all changes for the three product tables (before/after).

Example:
- If I select "INJURY" and "PERSON", I want to see the nine (9) records as illustrated in JPG "Example_01" (see .zip file).
- Alternatively, if I were to select "MISHAP" and "PERSON", I want to see seven (7) records as illustrated in JPG "Example_02" (see .zip file).

My question:
How does the code in module "modCompareTables" have to be modified to use an *append query* IOT to pass matching records into table [01_tbl_Log_ValueChanges] vs. always re-creating the table.

Any specific change recommendations would be greatly appreciated.

Cheers!
 

Attachments

  • DBs and JPGs.zip
    118.2 KB · Views: 573
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:30
Joined
May 21, 2018
Messages
8,463
I would do all of this with basically no code. I would build three union queries. Here is the first for injuries.
Code:
SELECT
  "Injury" as Category,
  "Inj_Injury_01" AS Field_Name,
  Before.INJ_INJURY_01 AS Before_Value,
  After.INJ_INJURY_01 AS End_Value
FROM
tbl_INJURY AS Before INNER JOIN tbl_Step_4_INJURY AS After ON Before.ID = After.ID
WHERE
Before.INJ_INJURY_01<>[after].[inj_Injury_01]
UNION
Select
  "Injury" as Category,
  "Inj_Injury_02" AS Field_Name,
  Before.INJ_INJURY_02 AS Before_Value,
  After.INJ_INJURY_02 AS End_Value
FROM
tbl_INJURY AS Before INNER JOIN tbl_Step_4_INJURY AS After ON Before.ID = After.ID
WHERE
Before.INJ_INJURY_02<>[after].[inj_Injury_02]
UNION
Select
  "Injury",
  "Inj_Injury_03" AS Field_Name,
  Before.INJ_INJURY_03 AS Before_Value,
  After.INJ_INJURY_03 AS End_Value
FROM
tbl_INJURY AS Before INNER JOIN tbl_Step_4_INJURY AS After ON Before.ID = After.ID
WHERE
Before.INJ_INJURY_03<>[after].[inj_Injury_03]
Code:
Category    Field_Name    Before_Value    End_Value
Injury    Inj_Injury_01    10    11
Injury    Inj_Injury_01    15    10
Injury    Inj_Injury_01    15    14
Injury    Inj_Injury_02    9    10
Injury    Inj_Injury_03    75    135
Injury    Inj_Injury_03    99    135

I do the same for the other two tables. You can then union that together into one big query
category, Field Name, Before value, after value

Now select from that query the specific categories and do a simple insert query. Then you have 1 or two lines of code for the whole thing. Or you coud run individual inserts off each Category table. Either way remove 99% of the code.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:30
Joined
May 21, 2018
Messages
8,463
Since your naming convention is very consistent doing this all with find and replace is real easy.
Here are the other two queries
Code:
SELECT
  "Mishap" as Category,
  Before.ID as CHANGED_RECORD_ID,
  "MSHP_CATEGORY" AS Field_Name,
  Before.MSHP_CATEGORY AS Before_Value,
  After.MSHP_CATEGORY AS End_Value
FROM
tbl_Mishap AS Before INNER JOIN tbl_Step_4_Mishap AS After ON Before.ID = After.ID
WHERE
Before.MSHP_CATEGORY<>[after].[MSHP_CATEGORY]
UNION
Select
  "Mishap" as Category,
  before.ID as CHANGED_RECORD_ID,
  "MSHP_CITY" AS Field_Name,
  Before.MSHP_CITY AS Before_Value,
  After.MSHP_CITY AS End_Value
FROM
tbl_MISHAP AS Before INNER JOIN tbl_Step_4_MISHAP AS After ON Before.ID = After.ID
WHERE
Before.MSHP_CITY<>[after].[MSHP_CITY]
UNION Select
  "Mishap",
    before.ID as CHANGED_RECORD_ID,
  "MSHP_DATE" AS Field_Name,
  Before.MSHP_DATE AS Before_Value,
  After.MSHP_DATE AS End_Value
FROM
tbl_MISHAP AS Before INNER JOIN tbl_Step_4_MISHAP AS After ON Before.ID = After.ID
WHERE
Before.MSHP_DATE<>[after].[MSHP_DATE];
Code:
SELECT
  "PERSON" as Category,
  Before.ID as CHANGED_RECORD_ID,
  "PERS_NAME" AS Field_Name,
  Before.PERS_NAME AS Before_Value,
  After.PERS_NAME AS End_Value
FROM
tbl_PERSON AS Before INNER JOIN tbl_Step_4_PERSON AS After ON Before.ID = After.ID
WHERE
Before.PERS_NAME<>[after].[PERS_NAME]
UNION
Select
  "PERSON" as Category,
  before.ID as CHANGED_RECORD_ID,
  "PERS_AGE" AS Field_Name,
  Before.PERS_AGE AS Before_Value,
  After.PERS_AGE AS End_Value
FROM
tbl_PERSON AS Before INNER JOIN tbl_Step_4_PERSON AS After ON Before.ID = After.ID
WHERE
Before.PERS_AGE<>[after].[PERS_AGE]
UNION Select
  "PERSON",
    before.ID as CHANGED_RECORD_ID,
  "PERS_ZIPCODE" AS Field_Name,
  Before.PERS_ZIPCODE AS Before_Value,
  After.PERS_ZIPCODE AS End_Value
FROM
tbl_PERSON AS Before INNER JOIN tbl_Step_4_PERSON AS After ON Before.ID = After.ID
WHERE
Before.PERS_ZIPCODE<>[after].[PERS_ZIPCODE];

Code:
Select * from qryInjuryChanges
Union Select * from qryMishapChanges
UNION SELECT * from qryPersonChanges
Order by 1,2

So this gives you a single query that you can view or if needed import into a table
Query1 Query1

CategoryCHANGED_RECORD_IDField_NameBefore_ValueEnd_Value
Injury
4​
Inj_Injury_011514
Injury
8​
Inj_Injury_011510
Injury
8​
Inj_Injury_02910
Injury
8​
Inj_Injury_0399135
Injury
12​
Inj_Injury_011011
Injury
12​
Inj_Injury_0375135
Mishap
6​
MSHP_CATEGORY5175
Mishap
7​
MSHP_DATE7/3/20217/14/2021
Mishap
9​
MSHP_CATEGORY9975
Mishap
9​
MSHP_CITYChicagoSeattle
PERSON
2​
PERS_AGE5522
PERSON
4​
PERS_ZIPCODE6961869617
PERSON
7​
PERS_NAMEHarrison FordJohn Wayne

Now looking at your image I have switched the before and after tables. You can switch that in the queries by simply changing the alias of the table names. You would have to do this in all three lines of each query.
so everywhere (example_
tbl_INJURY AS Before INNER JOIN tbl_Step_4_INJURY AS After ON Before.ID = After.ID
to
tbl_INJURY AS AFTER INNER JOIN tbl_Step_4_INJURY AS BEFORE ON Before.ID = After.ID
 
Last edited:

EEH_Help

Member
Local time
Today, 19:30
Joined
Feb 15, 2020
Messages
32
MajP:

Thank you for the very detailed response... I truly appreciate it.

In principal, your line of thinking is accurate and would (does) give the results as envisioned.

In actuality, however, the hard-coding of the fields (inside the VBA) is not practical. You can blame me for not including a reference such as "these are only sample fields... in reality, any of these tables may have 30 or more fields).

Presently, the VBA of DB #2 is 100% dynamic. That is, if I inserted any number of fields, any value changes would automatically be picked up between the before/after tables.

So, ultimately, I would truly prefer another option which does NOT require me to list all available fields (exceeding 300+ fields across roughly 16 tables).

So, again, your solutions appears to be working ok w/ dealing with a limited set of fields and small number of tables. Again, I take the responsibility for NOT stating that my actual project has many more fields and additional before/after tables.

Mea culpa!!

Any additional thoughts that would allow me to continue using a dynamic process (not requiring to list specific fields for n tables) would be greatly appreciated.

Cheers!!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:30
Joined
May 21, 2018
Messages
8,463
I would run this code logging all changes for all tables, but add the Category field like I did. This would be your productTableName
I would use the 00_tbl_Products..

Loop this little table getting the ProductName and calling
CompareTwoTables(ProductTable as string)

Now this code will run for all tables. And you can use the ProductTable name as the name of the Category. You will have one single table with all changes for all tables, but now it can be filtered by Category (productTable name)

Now you have a single table with all changes. If you index the fields making a unique index (change_record_ID, FieldName, Value_Before, Value_After) then you can never add a duplicate to this table. This way you can rerun the code, but only add updates without creating duplicates. Then you can use your listbox only as a filter. Filter the big table category on Person or Injury. Or filter on Person and Mishap, etc, etc, etc. You do not need to run the code each time to see a group of changes, just filter the table.
 
Last edited:

EEH_Help

Member
Local time
Today, 19:30
Joined
Feb 15, 2020
Messages
32
MajP -- thank you for the additional feedback.

Goal is to tweak the code w/o having to link field names.

Naturally, I can add an "admin" field which can be added to the VBA. Either way, would it be possible to post a DB #3 version that include your proposed changes? That is, I'm not 100% confident I'm fully tracking on all of the proposed changes.

Thank you in advance.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:30
Joined
May 21, 2018
Messages
8,463
I will do a demo. I was just suggesting you add a field called Category to your table. This would have values like Injury, Person, Mishap. Then you can just filter on those.
 

EEH_Help

Member
Local time
Today, 19:30
Joined
Feb 15, 2020
Messages
32
"Awesome sauce"... thank you (in advance) for your kind support. :)
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:30
Joined
May 21, 2018
Messages
8,463
Your code currently deletes the log table if it exists and then builds the Log table by scratch. It removes the table every time and then recreates it. Is there a reason for this? I could see this if the fields were dynamic, but they are not. I assume you did this on purpose, so what was the thought process for doing this. Is this because you do not want to continually add and delete for size constraints? Can you simply build the table once and then add updates to the table? Do you have to delete the table? Is this because you are getting all new before and after tables and the tables do not have previous data? I do not get the process.

My confusion is that you are asking to modify something that you obviously coded on purpose to do specifically the opposite. You purposely coded this to recreate the table each times instead of simply appending to the existing table.
How does the code in module "modCompareTables" have to be modified to use an *append query* IOT to pass matching records into table [01_tbl_Log_ValueChanges] vs. always re-creating the table.
 

EEH_Help

Member
Local time
Today, 19:30
Joined
Feb 15, 2020
Messages
32
MajP:

You are absolutely correct w/ your assessment of the 2 DB *examples*.

Generally speaking, DB #1 and DB #2 (by themselves) are two completely different processes. Allow me to recap purpose/process for both example files:

DB #1:
- I found a generic template of DB#1 which has a sole purpose which is as follows:
- Compare "Before" table with "After" table and summarize the differences in the [01_tbl_Log_ValueChanges].

DB #2:
- Completely independent from DB #1, I previously used this process to either output PDF files (or in this case append records) based on the selected values in the listbox.
- As you have noticed, I can merely append a single product, n products, or even all products via a single execution.
- Again, the "scanning through the listbox" works well for a number of processes.

New Method (DB #3):
- Basically, I would like the "best of both worlds" (DBs) so to speak.
- That is, I want to be able to identify the differences between a "Before" and "After" tables.
- In this case though, I have *multiple" before/after tables.
- So, while I can review them individually, my line of thinking was to apply the "scan through listbox" (DB #2) and **somehow** integrate these 2 solutions.

Recap:
- So, you are absolutely correct, I would NOT want to delete table [01_tbl_Log_ValueChanges] every time and re-build it from scratch.
- Instead, I would want the existing table structure and them merely keep on appending any matching records based on n items selected from the listbox.
- I would imagine, deleting *all records** from table [01_tbl_Log_ValueChanges] upon opening the form would be easy and ensure that records from a previous execution wouldn't be left in the table.

I think the additional information further expanded on the topic ensuring that I still can a) identify all differences between each table pair and b) append as many records as products/items were selected in the listbox.

Again, thank you for the follow-up and continued assistance. In case additional questions pop-up, I will do my very best to provide an answer ASAP.

Final words... whatever the solution may be, it's needs to remain dynamic enough so that it's completely agnostic to the "fields to be compared" and "tables included" as long as any new table is listed in table [00_tbl_Products]... which drives the listbox.

Cheers!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:30
Joined
May 21, 2018
Messages
8,463
See if this does it.
1. If there are records in the log it prompts you if you want to clear
2. Simplified the logging. Yours was kind of complicated
3. You can pick 1 or more product tables to log using an extended list box
4. Will not add duplicate records.

For demo purposes I put the log on the form, so you can see what happens when you execute.
 

Attachments

  • MajP Compare.accdb
    1.1 MB · Views: 567

EEH_Help

Member
Local time
Today, 19:30
Joined
Feb 15, 2020
Messages
32
MajP:

Wow!!! This is absolutely beautiful!!!

I had a quick look and completed the following validation tests:

1a. Added new before/after tables *without* changes. No records shown... great!
1b. Made a few changes to the new before/after table. All changes are reflected... great!
2. Added new field to an existing before/after table. All changes are reflected... great!

I will continue to review the code tomorrow IOT further familiarize myself with the new "UpdateLog" function.

For now, however, I'm already 99.9% confident that your solution works **exactly** as envisioned.

I truly thank you for your dedicated assistance and help in this matter... this will be a great addition to the existing project.

Btw, I really like the subform as part of the form.

Again, thank you, MajP for you help. :)

P.S. I realize I left off one query in the "Compare" file this morning. That is, I linked a query to table [01_tbl_Log_ValueChanges] without the LOG_ID and CHANGED_RECORD_ID. See code below.

Now, based on the included sample data, the query may not come across as powerful; however, in my actual log file [01_tbl_Log_ValueChanges] I have seen cases where I had more than 10,000 change records (including LOG_ID and CHANGED_RECORD_ID). Thus, having the query below would give me only the net changes (i.e., applied business rules).

I will see what's the best place (e.g., another command button on the form) in order to execute this query (macro-level view). But again, your solution works perfectly!!!!!


Code:
SELECT [01_tbl_Log_ValueChanges].FIELDNAME, [01_tbl_Log_ValueChanges].VALUE_BEFORE, [01_tbl_Log_ValueChanges].VALUE_AFTER
FROM 01_tbl_Log_ValueChanges
GROUP BY [01_tbl_Log_ValueChanges].FIELDNAME, [01_tbl_Log_ValueChanges].VALUE_BEFORE, [01_tbl_Log_ValueChanges].VALUE_AFTER;
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:30
Joined
May 21, 2018
Messages
8,463
Now, based on the included sample data, the query may not come across as powerful; however, in my actual log file [01_tbl_Log_ValueChanges] I have seen cases where I had more than 10,000 change records (including LOG_ID and CHANGED_RECORD_ID). Thus, having the query below would give me only the net changes (i.e., applied business rules).
In a case like this where you do not want to log the same record twice such as re-running the code, there is an easy trick.
In this example I check before logging to see if there is already an existing record with the same Changed_Record_ID and FieldName. With thousands of record this extra check could add a lot of time. If instead you index the log table making Changed_Record_ID and FieldName a unique index then you cannot add a duplicated record. If you do an insert query using CurrentDB.Execute and it tries to enter a duplicate it simply fails and goes to the next record. No error message is given unless you specifically ask for it. So this is a simple way to eliminate duplicates without having to check first. In other words if not a duplicate it will get logged and if a duplicate nothing happens.
 

EEH_Help

Member
Local time
Today, 19:30
Joined
Feb 15, 2020
Messages
32
MajP - great. I'll look into making Changed_Record_ID and FieldName a unique index once I migrate the solution into my actual DB.

I'll keep you posted.
 

EEH_Help

Member
Local time
Today, 19:30
Joined
Feb 15, 2020
Messages
32
MajP:

First of all, let me re-iterate that I really really like your new version. (y)

Once I integrated my actual data into this solution, I experienced an issue though. Hopefully INDEXING may solve it. Attached are 3 DBs... they are as follows:

*** "Comparison Report v01 (Original)" *************
- This is my (original) version.
- Upon opening form "F01_MainMenu", select, e.g., "AIRCRAFT".
- Next, click on the 1st command button "View Change Log (records)" ... this will bring up 16,415 records.
- Finally, click on the 2nd command button "View Change Log (BRs)" ... this will then bring up the query (w/o LOG_ID & CHANGED_RECORD_ID).
- The total # of records (based on query = 7 records). Ultimately, this is the more important view given I can validate which "business rule" has been executed (or not).


*** "Comparison Report v02 (MajP)" *************
- This (your) version includes now the same 6 before/after tables (per my original version).
- Here, the MOST IMPORTANT THING to note is that, e.g., table [tbl_Step_4_AIRCRAFT] only contains eight (8) fields. All of these fields are populated in this "before" table.
- The processing time is very acceptable... it takes approximately 12 seconds when selecting "AIRCRAFT" from the listbox and then populating the subform.
- Now, I did notice that, I'm returning 17,003 records (vs. 16,415 based on my original version). I'll get back to that in a moment though.


*** "Comparison Report v03 (MajP)" *************
- Now, in this version I only included one before/after table for "Aircraft".
- The key-take away from this version is that table [tbl_Step_4_AIRCRAFT] includes 91 fields where many fields, however, are NOT populated.
- Ultimately, this is how some of my tables are structured. There's no need to go into details as to why so many fields are empty.
- Now, when executing "AIRCRAFT", I never get any records displayed in the subform. I let the function run for more than a minute but nothing happened.

Recap:
- Based on version 03 (the one where the table [tbl_Step_4_AIRCRAFT] includes more than 90 fields with many having no data) is the version that I need to use though.
- So while this new process works great with sample data & tables where all fields are populated, it appears it does NOT work when tables include fields that are NOT populated.
- Ultimately, I don't even need to see the 17,003 (or 16,415) records... instead the DISTINCT records (w/o LOG_ID & CHANGED_RECORD_ID) would be more than sufficient.
- So, just like in my original version, once I clicked on the 2nd command button, the query displayed only 7 records (where the change log included 16,415 records). The 7 records basically indicate that a "business rule" has been applied (something was changed)... as pointed out in the PPT, those 7 records tell me much more than the 16k or 17k records.

My question:
How would the code be modified (in version 03) so that the subform only returns those 7 distinct records (i.e., records w/o LOG_ID & CHANGED_RECORD_ID) but while still having to scan through, e.g., all 91 fields?

Thank you,
EEH

P.S. I have also attached a PPT (3 slides) that summarizes the information for each version.
 

Attachments

  • 3 DB Versions and 1 PPT.zip
    1 MB · Views: 485

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:30
Joined
May 21, 2018
Messages
8,463
My question:
How would the code be modified (in version 03) so that the subform only returns those 7 distinct records (i.e., records w/o LOG_ID & CHANGED_RECORD_ID) but while still having to scan through, e.g., all 91 fields?
It is working. There are no results because the two tables are identical. You can verify be changing a value in one of the tables and rerunning. You did a copy and paste without changing any values.
 

Attachments

  • Comparison Report v03 (MajP V2).accdb
    4.9 MB · Views: 551
Last edited:

EEH_Help

Member
Local time
Today, 19:30
Joined
Feb 15, 2020
Messages
32
MajP:

"Sleep deprivation" is a "b i _ _ h"... sorry... didn't mean to be vulgar. I was working it in the middle of the night (on a few hours of sleep) and I did NOT import "table pairs" for which business rules had been applied.

I starting re-checking it about 30 min ago and noticed my error. I was about to post my mistake in a moment. So, I am very sorry for the confusion. Your solution works great!

There's one last thing I need to figure out
- It may take up to a minute until a large table (pair) has been scanned and populates the findings in the sub form. Time is perfectly fine.
- Now, in the event there are "no changes", there's no feedback mechanism... in other words, a user may think "oh, maybe it's still searching for the differences".

Work-around:
- I added the following message box into the "UpdateLog" function, incl. the boolean to prevent the message to pop up multiple times when same function is called repeatedly.

Code:
Sub UpdateLog(ProductTable As String)

    Static b As Boolean    'Should prevent the message box to pop up multiple times

    '... bunch of VBA code



    If DCount("*", "01_tbl_Log_ValueChanges") = 0 Then
   
        'As declared b will be false
        If b Then
           'Do nothing
        Else
           MsgBox "There are no changes between the tables!"
        End If
   
        b = Not b

    End If

End Sub

Although I included the temp/global variable (b), the message box still pops up twice (if I were to select 3 products with no changes).

My final question:
How can I prevent the message box to show up multiple times when I select more than one (1) product from the listbox and each of the selected products does NOT have any changes? That is, whether I select 1 or 10 tables (where tables have no changes), I only want to see the message box once.


Thank you again for all of your help!!!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:30
Joined
May 21, 2018
Messages
8,463
See this version. It has a progress meter and provide a message with the execution time and number of fields processed. Also changed the logic to make it much faster. Went from 8 seconds to 1 second.
 

Attachments

  • Comparison Report v03 (MajP V3).zip
    218.5 KB · Views: 529

EEH_Help

Member
Local time
Today, 19:30
Joined
Feb 15, 2020
Messages
32
Oh, wow!!! To quote McDonald's (fast food)... "I'm loving it!!!"

This is absolutely stunning... thank you so much for providing me this latest version.

Final question (I hope)... what's the purpose of "Query1"? Must I keep it or can it be deleted?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:30
Joined
May 21, 2018
Messages
8,463
That query added random changes to the table to verify there was at first no changes. You can delete.
 

Users who are viewing this thread

Top Bottom