Need to modify existing code in order to transfer *string* values (vs. numeric values)

[skydivetom]

Hello experts:

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:

    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

        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 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".

[jimatqsi]

skydivetom,
I think you might benefit from beginning anew. Evaluate what you want to accomplish and how to state that clearly.

Most experts here avoid downloading and opening attached files, for security reasons.

Jim