Split User Input into 2 fields in DB Table

[Matthew Staats]

Can someone tell me how to split a user input from a text box into two fields in a database?

for example - user input xxx0001

xxx goes into field 1, 0001 goes into field 2 when user clicks the submit button.

 

[CyberLynx]

Is the contents of this Text Box to be stored into to Form Fields on the Same Form, into the same Table bound to the Form, or into two fields within a separate Table altogether?

To separate the Two items:

Dim ItemA As String
Dim ItemB As String
Dim TxtContent As String

TxtContent = Nz(Me.MyTextBoxName, "")
If TxtContent <> "" Then
   ItemA = Left$(TxtContent, 3)
   ItemB = Mid$(TxtContnet, 4)
End if

MsgBox "First Part is: " & ItemA & vbCr  & _
       "Second Part is: " & ItemB

.

 

[Matthew Staats]

Into the same table bound to the form. However I am marginally new to Access so I will put this out there, the Form is bound to the table however the Text box is unbound. I put in the code to separate the two fields and that worked like a champ, and as I am sure you are aware of it did not write to the database though. Any future assistance you could offer would be appreciated.

Thanks

 

[CyberLynx]

If not already in Form, create two more Text Boxes. Name each of the two Text Boxes to represent the two Fields in Table you want this split data to go into. You can set the Visible property to No for each Text box if you do not want them visible i the Form. Set the Control Source property for each Text Box to the Field Names in table where the Split data will be Stored into.

Now, let's say the two Text Boxes are named txtField1 and txtField2. In the OnClick event of your Submit button, have this code in there:

Dim TxtContent As String

TxtContent = Nz(Me.MyTextBoxName, "")  [COLOR="DarkGreen"]'The TextBox which contains the string to Split.[/COLOR]
If TxtContent <> "" Then
   Me.txtField1 =  = Left$(TxtContent, 3)
   Me.txtField2 = Mid$(TxtContnet, 4)
End if

Done.

.

 

[Matthew Staats]

It appears to be working however I am not sure if I am doing something wrong. It will separate the two fields and field1 will write to the database while field two will not. here is what the code is for my submit button:

Private Sub Command16_Click()
On Error GoTo Err_Command16_Click

Dim TxtContent As String

TxtContent = Nz(Me.Text32, "")
If TxtContent <> "" Then
Me.Target_Fault_ID_Prefix = Left$(TxtContent, 6)
Me.Target_Fault_ID_Suffix = Mid$(TxtContnet, 4)
End If

DoCmd.GoToRecord , , acNewRec

Exit_Command16_Click:
Exit Sub

Err_Command16_Click:
MsgBox Err.Description
Resume Exit_Command16_Click

End Sub

The data I am entering is DRHW080005 and it will separate the DRHW08 and place it in the right field however the 0005 will not work. Is this something that I am doing wrong if so please let me know.

 

[CyberLynx]

Just to point out the obvious for now....you spelled the Variable wrong in:

Me.Target_Fault_ID_Suffix = Mid$(TxtContnet, 4)

It should be:

Me.Target_Fault_ID_Suffix = Mid$(TxtContent, 7)

Notice I changed the starting point for the Mid function from 4 to 7? If you want to split the string (say, DRHW080005) into DRHW08 and 0005 then the Mid function must start from the 7th character, not the 4th character.

Also keep in mind, both Fields in table must be of Text DataType otherwise it will not keep the prefixing zeros.