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

  • Thread starter Christian Bahnsen
  • 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
 
R

Robert Morley

I think what you're looking for is:

myarray = Split([txtIENs], vbCrLf)


Rob
 
C

Christian Bahnsen

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...
 
D

Douglas J. Steele

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...
 
R

Robert Morley

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
 
C

Christian Bahnsen

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I use an Add In to suppress the outlook security warning? 5

Top