Re: What delimiter do I use in the split() function for textbox te

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" wrote:

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


.