Splitting cell data (1 Viewer)

[David_P]

Good day to you all,

I have been trying to figure out a problem and can't seem to find the solution.

I have a data table that has 3 parts to it in one cell. I want to be able to break the part into individual cells on a form so as to edit the data in that part without having to edit the whole cell.

For example: the data looks like this: T00000E99999IN
and I want the form to look like this where the red I can edit and it saves the changes when I go to the next record:
Task Employee Status
T00000 E99999 IN


I would be consider a beginner at acces but understand the basics.

Thanks for your help.

David

 

[Steve R.]

The first question is there an obvious format of some sort that allows you to "break" the data into three pieces.

I tend to use the LEN() function and the MID() to parse my way through the string. Depending on the format, you could use LIKE or inStr function too.

 

[David_P]

The data is coming from a cell called BarCode from the table called Checkpoint. I can display the data by using the Mid string operation but won't let me edit the informatioin in the cell on the form.

David

 

[Steve R.]

Are you accessing the BarCode information in the table through a form? Do you have a textbox control bound the the cell BarCode?

Since you intend to split the data in BarCode into three variables, create three unbound textbox controls on your form so that you can observe how your data is getting placed into each variable.

 

[David_P]

I created a form to edit the table Checkpoint. On the form I created 3 text boxes, one for each part of the cell BarCode to be able to edit each part seperate when it needs to be.

How would I create a variable for each text box for each part?

 

[Steve R.]

Ok, TextBox1 should be bound to the the control "BarCode". I hope that you see the value of the barcode. The remaining text boxes need to be unbound. Syntax for MID "Mid(string, start[, length])" You can set the values of Textbox2,Textbox3,Textbox4 along this line.

me.textbox2=Mid(me.textbox1,1,3)

The sample is a sample that you will have to adapt for all three boxes. If done correctly each textbox should only have a segment of the barcode.

 

[David_P]

I have tried the code you probided but Ma getting the #Name? error int he text box.
I entered in the following code in the Control Source Property sheet box: me.Employee_Name=Mid(me.Bar_Code,6,5) but it gets changed to:
[me].[Employee_Name]=Mid([me].[Bar_Code],6,5) where textbox2 is Employee_Name (the text box the code is in) and textbox1 is Bar_Code (the text box that is bound to BarCode)

 

[SOS]

I entered in the following code in the Control Source Property sheet box:

Then you entered it in the wrong place. It doesn't go in the control source property. It goes in the VBA CODE WINDOW.

Check this out to see how to get there.

 

[Steve R.]

My apologies, but I seldom use the Control Source Property box. If you use the textbox's Control Source Property, the syntax for Textbox2 would be:

=Mid(bar_code,6,5)

Also please be aware that all that is being done, at this time, is splitting the barcode into three elements, nothing is being saved or modified.

 

[David_P]

Ok, I put the code in the Form Property under the On Current Event and got the Form to seperate the data like I want it to. I can change the data in the Form's text box but it doesn't change the data in the table. Am I forgetting a step somewhere?

 

[SOS]

Am I forgetting a step somewhere?

The control source of the text box needs to be bound to the field in the table.

 

[David_P]

So do I need another Function in the Control Source for the text box I am changing?

 

[SOS]

So do I need another Function in the Control Source for the text box I am changing?

You select the field from the drop down in the control source of the text box you want to save the data. The code sets the value of the text box but the field being selected in the control source (not a formula) is what saves it to the table.

 

[Steve R.]

David, why are you doing this project?

 

[David_P]

SOS-When I changed the Control Source to the to the Table field, it change the data in the table to what is in the Split cell so I only get that part saved to the table not all 3 parts.

Steve R.-It is for a work project. I myself could edit the full cell in the table but I am trying tomake a form so it is user friendly.

 

[SOS]

SOS-When I changed the Control Source to the to the Table field, it change the data in the table to what is in the Split cell so I only get that part saved to the table not all 3 parts.

Steve R.-It is for a work project. I myself could edit the full cell in the table but I am trying tomake a form so it is user friendly.

David: This is getting frustrating a bit. Do you know how to use Access at all? It is very simple. Each text box is bound to a SEPARATE field. That way the data gets saved to each field. Each text box also would be getting its value set by VBA code. But you would need to have a separate bit of code to set each text box's value:

Me.TextBox1 = Left(Me.YourFullField,4)
Me.TextBox2 = Mid(Me.YouFullField, 5, 5)
Me.TextBox3 = Right(Me.YourFullField, 6)

Those are bogus formulas as I don't have time to go figure each out for you right now or even look back to see if they are listed by SteveR.

 

[David_P]

SOS-Yes, I do know how to use Access (beginner level or a little higher) but it has been awhile since I used it and this is getting into using VBA which I am just starting to learn. I am a fast learner thought and am good with understanding how programs work.

I know it is frustrating. I have been working on the solution for over a week.

So far I have created the following Event and placed it on the Form Properties Sheet under On Current:
Public Sub Form_Current()
Me.Task_Code_Name = Left(Me.Bar_Code_Name, 5)
Me.Employee_Number_Name = Mid(Me.Bar_Code_Name, 6, 5)
Me.Status_Name1 = Mid(Me.Bar_Code_Name, 11, 3)
End Sub

This splits the data into the cells I want. So now it looks like this:

 

[Steve R.]

I believe that we are done. I would strongly recommend that you get an Access book that includes the use of VBA. Forum members are here to help, but it also requires that potential questions be researched for answers before asking. A basic Access reference book would have covered your question