What delimiter do I use in the split() function for textbox text?

  • Thread starter Thread starter Christian Bahnsen
  • Start date Start date
C

Christian Bahnsen

Here’s the scenario: In Excel, a user selects several cells in a single
column and copies them to the clipboard. The user then opens a form in
Access and clicks a command button.

The Click event of the command button sets the focus on a text box on the
form and pastes the data from the clipboard into the text box. So far, so
good.

I then want to split the multiple lines of text from the textbox into a
one-dimensional array and then step through the array grabbing each element
and then programmatically create a record for each element in an Access
table. This is where I’m stuck.

The values pasted into the text box would appear similar to this:

00042
00043
00044
00045
00098
00099
00100
00101

My question: What delimiter do I use in the split() function for multiple
lines of text from a text box?

Here’s the code I’ve been experimenting with. The form I designed for
testing purposes has one text box, [txtIENs], and one command button, named
cmdProcessIENs. Below is the Click event with different variations I’ve
tried:

Private Sub cmdProcessIENs_Click()

' declare a dynamic array
Dim myarray()

' set the focus on the text box
[txtIENs].SetFocus

' paste the clipboard contents
DoCmd.RunCommand acCmdPaste

' commit the paste (optional if you will use the text property of the
field to read the contents, but not if you want to use the value property)
DoCmd.RunCommand acCmdSaveRecord

' if you don't specify, the value property is returned by default
' I'm simply testing here that I can return the contents of the text box

MsgBox [txtIENs]

' EVERYTHING IS WORKING FINE TO THIS POINT

'all of these tests threw the same error, a
'run-time error 13 -- type mismatch

'myarray() = Split("[txtIENs]", vbCrLf)
'myarray = Split("[txtIENs]", vbCrLf)
'myarray() = Split("[txtIENs]", "\r\n")
'myarray = Split("[txtIENs]", "\r\n")
'myarray = Split("[txtIENs]", "%0A")
'myarray = Split("[txtIENs]", vbCrLf)(0)

' this test threw a run-time error 9 -- subscript out of range error
'myarray = Split("[txtIENs]", vbCrLf)(1)



End Sub
 
Rob,

Thanks for your reply but I'd already tried that. If you look in all of the
variations I tried you'd see that one.
--
Christian Bahnsen


Robert Morley said:
I think what you're looking for is:

myarray = Split([txtIENs], vbCrLf)


Rob

Christian said:
Here’s the scenario: In Excel, a user selects several cells in a single
column and copies them to the clipboard. The user then opens a form in
Access and clicks a command button.

The Click event of the command button sets the focus on a text box on the
form and pastes the data from the clipboard into the text box. So far, so
good.

I then want to split the multiple lines of text from the textbox into a
one-dimensional array and then step through the array grabbing each element
and then programmatically create a record for each element in an Access
table. This is where I’m stuck.

The values pasted into the text box would appear similar to this:

00042
00043
00044
00045
00098
00099
00100
00101

My question: What delimiter do I use in the split() function for multiple
lines of text from a text box?

Here’s the code I’ve been experimenting with. The form I designed for
testing purposes has one text box, [txtIENs], and one command button, named
cmdProcessIENs. Below is the Click event with different variations I’ve
tried:

Private Sub cmdProcessIENs_Click()

' declare a dynamic array
Dim myarray()

' set the focus on the text box
[txtIENs].SetFocus

' paste the clipboard contents
DoCmd.RunCommand acCmdPaste

' commit the paste (optional if you will use the text property of the
field to read the contents, but not if you want to use the value property)
DoCmd.RunCommand acCmdSaveRecord

' if you don't specify, the value property is returned by default
' I'm simply testing here that I can return the contents of the text box

MsgBox [txtIENs]

' EVERYTHING IS WORKING FINE TO THIS POINT

'all of these tests threw the same error, a
'run-time error 13 -- type mismatch

'myarray() = Split("[txtIENs]", vbCrLf)
'myarray = Split("[txtIENs]", vbCrLf)
'myarray() = Split("[txtIENs]", "\r\n")
'myarray = Split("[txtIENs]", "\r\n")
'myarray = Split("[txtIENs]", "%0A")
'myarray = Split("[txtIENs]", vbCrLf)(0)

' this test threw a run-time error 9 -- subscript out of range error
'myarray = Split("[txtIENs]", vbCrLf)(1)



End Sub
Click to expand...
Click to expand...
 
Change your declaration to

Dim myarray As Variant

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Christian Bahnsen said:
Rob,

Thanks for your reply but I'd already tried that. If you look in all of
the
variations I tried you'd see that one.
--
Christian Bahnsen


Robert Morley said:
I think what you're looking for is:

myarray = Split([txtIENs], vbCrLf)


Rob

Christian said:
Here's the scenario: In Excel, a user selects several cells in a single
column and copies them to the clipboard. The user then opens a form in
Access and clicks a command button.

The Click event of the command button sets the focus on a text box on
the
form and pastes the data from the clipboard into the text box. So far,
so
good.

I then want to split the multiple lines of text from the textbox into a
one-dimensional array and then step through the array grabbing each
element
and then programmatically create a record for each element in an Access
table. This is where I'm stuck.

The values pasted into the text box would appear similar to this:

00042
00043
00044
00045
00098
00099
00100
00101

My question: What delimiter do I use in the split() function for
multiple
lines of text from a text box?

Here's the code I've been experimenting with. The form I designed for
testing purposes has one text box, [txtIENs], and one command button,
named
cmdProcessIENs. Below is the Click event with different variations I've
tried:

Private Sub cmdProcessIENs_Click()

' declare a dynamic array
Dim myarray()

' set the focus on the text box
[txtIENs].SetFocus

' paste the clipboard contents
DoCmd.RunCommand acCmdPaste

' commit the paste (optional if you will use the text property of
the
field to read the contents, but not if you want to use the value
property)
DoCmd.RunCommand acCmdSaveRecord

' if you don't specify, the value property is returned by default
' I'm simply testing here that I can return the contents of the
text box

MsgBox [txtIENs]

' EVERYTHING IS WORKING FINE TO THIS POINT

'all of these tests threw the same error, a
'run-time error 13 -- type mismatch

'myarray() = Split("[txtIENs]", vbCrLf)
'myarray = Split("[txtIENs]", vbCrLf)
'myarray() = Split("[txtIENs]", "\r\n")
'myarray = Split("[txtIENs]", "\r\n")
'myarray = Split("[txtIENs]", "%0A")
'myarray = Split("[txtIENs]", vbCrLf)(0)

' this test threw a run-time error 9 -- subscript out of range
error
'myarray = Split("[txtIENs]", vbCrLf)(1)



End Sub
Click to expand...
Click to expand...
Click to expand...
 
All the variations you had had the field enclosed in quotes, which it
shouldn't be. Doug's right, though, the declaration is the larger problem,
which I'd missed.


Rob
 
Mr. Steele,

Thanks for your reply. Thanks also to Rob Morley. Changing the declaration
was part of the solution. The other part was not enclosing [txtIEN] in
quotation marks in the split() function.

This is the code that works to split the text into a one-dimensional array:

Private Sub cmdProcessIENs_Click()

Dim myarray As Variant

' set the focus on the text box
[txtIENs].SetFocus

' paste the clipboard contents
DoCmd.RunCommand acCmdPaste

' commit the paste
DoCmd.RunCommand acCmdSaveRecord

' splits the text into a one-dimensional array
myarray = Split([txtIENs], vbCrLf)

End Sub

--
Christian Bahnsen


Douglas J. Steele said:
Change your declaration to

Dim myarray As Variant

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Christian Bahnsen said:
Rob,

Thanks for your reply but I'd already tried that. If you look in all of
the
variations I tried you'd see that one.
--
Christian Bahnsen


Robert Morley said:
I think what you're looking for is:

myarray = Split([txtIENs], vbCrLf)


Rob

Christian Bahnsen wrote:
Here's the scenario: In Excel, a user selects several cells in a single
column and copies them to the clipboard. The user then opens a form in
Access and clicks a command button.

The Click event of the command button sets the focus on a text box on
the
form and pastes the data from the clipboard into the text box. So far,
so
good.

I then want to split the multiple lines of text from the textbox into a
one-dimensional array and then step through the array grabbing each
element
and then programmatically create a record for each element in an Access
table. This is where I'm stuck.

The values pasted into the text box would appear similar to this:

00042
00043
00044
00045
00098
00099
00100
00101

My question: What delimiter do I use in the split() function for
multiple
lines of text from a text box?

Here's the code I've been experimenting with. The form I designed for
testing purposes has one text box, [txtIENs], and one command button,
named
cmdProcessIENs. Below is the Click event with different variations I've
tried:

Private Sub cmdProcessIENs_Click()

' declare a dynamic array
Dim myarray()

' set the focus on the text box
[txtIENs].SetFocus

' paste the clipboard contents
DoCmd.RunCommand acCmdPaste

' commit the paste (optional if you will use the text property of
the
field to read the contents, but not if you want to use the value
property)
DoCmd.RunCommand acCmdSaveRecord

' if you don't specify, the value property is returned by default
' I'm simply testing here that I can return the contents of the
text box

MsgBox [txtIENs]

' EVERYTHING IS WORKING FINE TO THIS POINT

'all of these tests threw the same error, a
'run-time error 13 -- type mismatch

'myarray() = Split("[txtIENs]", vbCrLf)
'myarray = Split("[txtIENs]", vbCrLf)
'myarray() = Split("[txtIENs]", "\r\n")
'myarray = Split("[txtIENs]", "\r\n")
'myarray = Split("[txtIENs]", "%0A")
'myarray = Split("[txtIENs]", vbCrLf)(0)

' this test threw a run-time error 9 -- subscript out of range
error
'myarray = Split("[txtIENs]", vbCrLf)(1)



End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top