473,287 Members | 1,418 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,287 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
  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

✓ answered by 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

1 13245
jimatqsi
1,271 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

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

Similar topics

2
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...
7
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...
3
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...
0
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...
4
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?...
7
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...
3
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,
232
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...
4
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...
9
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
2
isladogs
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...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...

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.