I came across a 10+year old posting in this forum (https://bytes.com/topic/access/answers/911063-how-create-automatic-dynamic-union-query).
The solution offered by @Steward Ross is fabulous and perfectly fits my current need. However, I need to slightly tweak the VBA in order to NOT process *string* values vs. *integers* (e.g., quantities) but instead process *string* values.
Instead of reiterating the need, I recommend to briefly scan through Stewards's recommendation.
Next, I replicated the original author's data set and then took Steward's VBA code and placed it into the attached DB "Version_01_Integer".
Upon opening the DB "Version_01_Integer", please do the following:
1. Click on command button "Convert Multi Fields..." in the form (open by default).
2. Compare tables [01_tblSource] and [02_tblDestination].
Again, the outcome is exactly as in the specified requirements. No change is needed.
Now, let's review version "Version_02_String"... this is the one I need some help with:
I'll summarize the changes I made:
1. With the exception of the autonumber [ID] field, all fields have data type = "Short Text".
2. In the form, I modified the function call start start in 2nd field and include altogether 5 fields:
Expand|Select|Wrap|Line Numbers
- blResult = fExtractProductQuantities("01_tblSource", "02_tblDestination", 2, 5, True)
4. Next, in the module, I modified the following lines of code
Expand|Select|Wrap|Line Numbers
- For intFieldCount = 0 To FirstProductFieldNo - 2
- 'strSQLCreate = strSQLCreate & rsSource.Fields(intFieldCount).Name & IIf(intFieldCount = 0, " INT ,", " VARCHAR(255), ") 'Original code
- strSQLCreate = strSQLCreate & rsSource.Fields(intFieldCount).Name & IIf(intFieldCount = 0, " VARCHAR(255) ,", " VARCHAR(255), ")
- Next
- 'strSQLCreate = strSQLCreate & "Product VARCHAR(255), Quantity INT);" 'Original code
- strSQLCreate = strSQLCreate & "Fieldname VARCHAR(255), Stringvalue VARCHAR(255));"
My question:
What additional VBA code modications are necessary so that I can transfer all data across all fields (except the autonumber ID field) into the destination table?
Thank you for your help in advance,
Tom
P.S. I have the 2 DBs in zipped format but did not see the option on how to attach them. Where do I find the link for "attachments".