VBA code for concatenation (1 Viewer)

[ScottXe]

I would like to enter a couple of alphanumeric groups into a field on an input form. After I enter an alphanumeric group, I hit the enter and the data will add into the field and refresh to empty box ready for next entry. If I continue to enter another group and hit enter, the next group will be added to original field with a comma and a space in between. Can someone help build the VBA in after update event to accomplish the task. Thanks!

 

[ypma]

Hello Scottxe, must point out I am not a expert in VBA, but enjoy playing with it.

My initial thoughts are that your field to hold your series of numbers needs to be a text box to allow a comma . "txtmainnumgroup"

Then and extra two unbound text boxes , one to enter new data the other as a storage for your existing data from the bound txtmainnumgroup .

Then in the txtinput field

Before update
# Me.txtparking = Me.txtmainnumgroup & "," & Txtinput#

After update
#Me.txtmainnumgroup = txtparking
me.txtinput = " "#

Which seems to work apart from the comma that is inserted with the first number .

so you get ,123,456,789
Hope this give you something to consider as said I am no expert so there may be a better way to achieve you goal.

IF this does not make sense could post a demo access 2010.
Regards Ypma

 

[RainLover]

I would like to enter a couple of alphanumeric groups into a field on an input form. After I enter an alphanumeric group, I hit the enter and the data will add into the field and refresh to empty box ready for next entry. If I continue to enter another group and hit enter, the next group will be added to original field with a comma and a space in between. Can someone help build the VBA in after update event to accomplish the task. Thanks!

If you use the After Update event it means that the data has been written to the table and most likely you are now somewhere different to where you should be.

If you use Before Update then you can interrupt the code adjust it if required and continue on.

Go to Google and have a little read about this.

Finally the actual code can vary depending on what you have to start with. Not a big difference but you do need to be aware of this.

 

[RainLover]

Your original question is a bit unclear.

Can you re-explain in plain English.

 

[ScottXe]

Hello Scottxe, must point out I am not a expert in VBA, but enjoy playing with it.

My initial thoughts are that your field to hold your series of numbers needs to be a text box to allow a comma . "txtmainnumgroup"

Then and extra two unbound text boxes , one to enter new data the other as a storage for your existing data from the bound txtmainnumgroup .

Then in the txtinput field

Before update
# Me.txtparking = Me.txtmainnumgroup & "," & Txtinput#

After update
#Me.txtmainnumgroup = txtparking
me.txtinput = " "#

Which seems to work apart from the comma that is inserted with the first number .

so you get ,123,456,789
Hope this give you something to consider as said I am no expert so there may be a better way to achieve you goal.

IF this does not make sense could post a demo access 2010.
Regards Ypma

Hi Ypma,

Thanks for your help and guidance. Could you please attached accede for Access 2007 as I could not open it due to unrecognised database format.

Scott

 

[ScottXe]

If you use the After Update event it means that the data has been written to the table and most likely you are now somewhere different to where you should be.

If you use Before Update then you can interrupt the code adjust it if required and continue on.

Go to Google and have a little read about this.

Finally the actual code can vary depending on what you have to start with. Not a big difference but you do need to be aware of this.

Hi RainLover,

Thanks for your explanation the differences between before update and after update. That is to say that I need to enter all groups from before update and use after update at the last group to write the complete result into the table.

Scott

 

[RainLover]

I could not open it either.

One should always test any upload by doing a download and proving that it still works as intended.

 

[ScottXe]

Your original question is a bit unclear.

Can you re-explain in plain English.

Hi RainLover,

In simply, I would like to enter a couple of groups into a field such as:-

LTH3WH15,
STH5,
A7HDMI15E

The groups may be from 1 to 18 but most of them are 1-3 groups.

The result should look: LTH3WH15, STH5, A7HDMI15E

Scott

 

[RainLover]

Hi RainLover,

Thanks for your explanation the differences between before update and after update. That is to say that I need to enter all groups from before update and use after update at the last group to write the complete result into the table.

Scott

Not quite. Just use the before update. The after update has nothing to do with saving.

Best to do a search as this has been explained better by others.

 

[RainLover]

Try this;

Meals: [Breakfast] & ", " & [Lunch] & ", " & [Dinner]

Your sample had a group of 3 so that is what I catered for. If you want anything up to 18 groups then I do not know how to do that.

But I can say that I feel that your design is incorrect. I would need to see the table before commenting any further.

If some one has a better solution then please jump in.

 

[RainLover]

ypme

I did not intend to push you out.

If you have something then please jump in.

I think we are going to end up with a Cross Tab query which I am not very good at. Perhaps you could handle that part of the solution..

 

[ypma]

My upload was in access 2010 and was tested prior to posting.
I have created a demo in office 2003 and zipped it ,which hopefully you will be able to open . You will have to click out of the INPUT field to activate the after update event '
Note the temp field can be hidden
I hope I have understood your question , please keep me informed .

Ypma

 

[RainLover]

ypma

The testing I was suggesting was to upload the Database and display it as though you have finished your post. Then download it to a Temp drive of your own and test that it opens and does what you want. If you have done this then you are one of the better few who does this.

Back to your problem. You have two only fields. The Autonumber is just a identifier so it has nothing to do with the problem.

So we are left with just one field.

I don't know where to go to from here. Where are these 18 that you want concatenated. You have given me nothing to go by.

It would be nice if you had more descriptive names. Imagine coming back to this in a year or two. You would have no idea what was going on.

Exit Sub
End If

The End Sub does what it says. It closes the code, so anything after this will not execute.

Would you like to try again so any reader would know what is what.

 

[ypma]

RainLover: It does not look as if anyone has opened my zip demo, Which hopefully will demonstrate where I am going with this question.
In my demo only one bound field is required which stores the concatenated groups which could be up to a total of 255 characters.
When the first group is entered into the input text box the group is passed to the bound text box. When the second number is entered , the group in the bound text boxeis passed to the parking textbox prior to the new group being passed to the bound text box. Then the result of catenation of the parking textbox and the bound box give you a result in the bound text box : 123BC, 456AD. This is then repeated as long you don't exceed the 255 characters .

Hope this show you how I am thinking , Please open my zipped attachment and let me know if I am off track .

Regards Ypma

 

[Gasman]

ypma,

I had a quick peek at it. It does not clear the input field on the very first data entry, but works after that except that I couldn't get it to stay in the input field unless I set focus to that txtInputNum on the GotFocus event of the next field.?

But I believe it was doing what ScottXe was asking other than that.

 

[ypma]

Gasman; Thank you for taking a look, I expected it to need some tweaking and will take another look , if Scott comes back to me on the points you highlighted . Thanks again .

Regards Ypma

 

[RainLover]

ypma

I downloaded both attachments. The first I could not open because I only have Version 2003, and I opened the second successfully. The second did not show at that time a count of downloads.

I still do not see the purpose of the Database.

What do these figures represent. Cars, sheep, weights, age?

When do they get entered. One after the other or when certain conditions are met.

The following which I posted ages ago should be what you are looking for.

Meals: [Breakfast] & ", " & [Lunch] & ", " & [Dinner]

There are some additional lines that execute the code but essentially that is it,

 

[ScottXe]

RainLover: It does not look as if anyone has opened my zip demo, Which hopefully will demonstrate where I am going with this question.
In my demo only one bound field is required which stores the concatenated groups which could be up to a total of 255 characters.
When the first group is entered into the input text box the group is passed to the bound text box. When the second number is entered , the group in the bound text boxeis passed to the parking textbox prior to the new group being passed to the bound text box. Then the result of catenation of the parking textbox and the bound box give you a result in the bound text box : 123BC, 456AD. This is then repeated as long you don't exceed the 255 characters .

Hope this show you how I am thinking , Please open my zipped attachment and let me know if I am off track .

Regards Ypma

Hi Ypma,

Thanks for your demo program which is what I need for the improvement of current data entry.

I notice two minor queries. Is the parking box necessary? It looks the same as the result box. The comma and space separator is not consistent in all groups. Normally the second group lacked of a space.

Scott

 

[ypma]

Hi Scott , This solution requires a temp field to hold the current group numbers which will be concatenated with the new input number to form the latest result grouping .
" Will see if it can be done without the temp field"
The demo does require some tweaking and I will take another look , I was concerned that I had misunderstood what you wanted to achieve but your latest indicates we are getting there .

My latest method gets rid of the temp field , so you were right it can be done without it.

There is a minor bug ,don't click into the result field or you will get a duplicate . click into the id field where the input box is cleared

Let me know if this is OK

 

[RainLover]

Guys

I believe that I have misunderstood the problem.

Therefore some of my advice is incorrect.

YPMA has the correct understanding and is just a little way off solving this problem.

I shall now proceed to stand aside and watch you sort things out.