470,590 Members | 2,371 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,590 developers. It's quick & easy.

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

3 2Bits
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:
Expand|Select|Wrap|Line Numbers
  1.     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

Expand|Select|Wrap|Line Numbers
  1.         For intFieldCount = 0 To FirstProductFieldNo - 2
  2.             'strSQLCreate = strSQLCreate & rsSource.Fields(intFieldCount).Name & IIf(intFieldCount = 0, " INT ,", " VARCHAR(255), ")              'Original code
  3.             strSQLCreate = strSQLCreate & rsSource.Fields(intFieldCount).Name & IIf(intFieldCount = 0, " VARCHAR(255) ,", " VARCHAR(255), ")
  4.         Next
  5.  
  6.         'strSQLCreate = strSQLCreate & "Product VARCHAR(255), Quantity INT);"                                                                     'Original code
  7.         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".
Jan 15 '22 #1
1 12363
jimatqsi
1,263 Expert 1GB
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
Jan 18 '22 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by pmatos | last post: by
3 posts views Thread by David P. Donahue | last post: by
reply views Thread by Karen | last post: by
3 posts views Thread by Krish | last post: by
232 posts views Thread by robert maas, see http://tinyurl.com/uh3t | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.