473,226 Members | 1,405 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,226 software developers and data experts.

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
  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,

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

✓ answered by jimatqsi

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.


1 13234
1,271 Expert 1GB
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.

Jan 18 '22 #2

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

Similar topics

by: qazmlp | last post by:
I have got the following strings: 121721234567890 13 1217212345 129 Is it possible to do descending sort for these strings based on their numeric values? After sorting, they need to figure...
by: pmatos | last post by:
Hi all, I've been having questions about strings, references, initializations... I've created code (which will not compile due to a reference problem) and I'd like comments on why this won't...
by: David P. Donahue | last post by:
I'm trying to come up with the best (fastest, as this code will be run often) way to accomplish the following: I have a dynamic string array X (as one grabbed from a GetFiles() or...
by: Karen | last post by:
Hi, I would like to be able to load an existing piece of code and then add a statement to a method within this. There are plenty of examples of creating the entire code from scratch but none...
by: Sam | last post by:
Hi everyone I have a question regarding string format. If I want to write a set of numeric values from arrays to a text file with the "right alignment" format as below, can it be done?...
by: Andrea | last post by:
Hi there - I'm hoping someone can help me; I've been struggling with this for a few days! :-) I have a webpage that is comprised of many forms containing questions. As the user answers one...
by: Krish | last post by:
I want to have an enum that has a list of numeric values. How do I achieve this? Something like this ... Public Enum AllowedNumbers { 111, 222, 333,
by: robert maas, see http://tinyurl.com/uh3t | last post by:
I'm working on examples of programming in several languages, all (except PHP) running under CGI so that I can show both the source files and the actually running of the examples online. The first...
by: Doug Gray | last post by:
Folks, I am looking for a fast but most importantly a bullet proof method to pass and NMEA data stream (GPS output) ascii numeric strings. The best I can offer is: def fint(a): try: return...
by: engteng | last post by:
How do I convert string to numeric in VB.NET 2003 ? Example convert P50001 to 50001 or 50001P to 50001 but if P is in middle then not convert. Regards, Tee
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.