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!
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
Last edited: