Hello experts:
I came across a 10+year old posting in another forum Bytes.com (https://bytes.com/topic/access/answe...ic-union-query).
For full disclosure, I posted a question in that forum, but apparently I can't attach any files which makes it difficult to follow along. I hope I'm not in violation of any rules in Accessforums.net by now posting the question here. If so, please forgive me in advance. Anyhow, here it is...
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 in Version_01.
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:
Code:
blResult = fExtractProductQuantities("01_tblSource", "02_tblDestination", 2, 5, True)
3. I modified table [02_tblDestination] and changed the data type to "Short Text" for [Fieldname] and [Stringvalue].
4. Next, in the module, I modified the following lines of code
Code:
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));"
Here's the problem though. Upon click the command button in the form, none of the data are never moved into table [02_tblDestination].
My question:
What additional VBA code modifications 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