Adding parsed data to a table

[Frank A]

Overview:

I am using Access 2003 / VBA for the following problem.

I have two tables:

1. tblAttend
fields:
id - autonumber
attendees - memo {name: txtattendees}

2. tblNames
fields:
id - autonumber
name - text {name: txtname}
atten_fk - number

txtattendees and txtname are the names of the respective text boxes in the
corresponding forms. The main form is call 'frmAttend1' and the sub form is
called regretably, 'tblNames Subform'.

There is a one to many relationship:
from tblAttend.id <> tblNames.atten_fk

I have created a form where tblNames is a subform of tblAttend. The
attendees field has a list of names that have been pasted in. This data acts
as one long string where there are names in the format Last, First. Also
some of the data has blank lines and some of the line have a single word
entry that is not needed.

The approach I took was to parse the list in the attendees field and put
each name into an array. Then to take each element in the array and add it
to a record in the tblNames table.

The initial code is linked to a command button and is as follows:

Private Sub cmdAddRec_Click()

SplitString (txtattendees)

End Sub
-----------------------------------------------------------------------------
The next code to run is as follows:

Public Sub SplitString(sMyString As String)
Dim MyArray() As String
Dim ctr As Integer
Dim sItem As Variant

MyArray = Split(sMyString, "")

For Each sItem In MyArray
DoCmd.GoToRecord , , acNewRec
[tblNames SubForm]!txtname.SetFocus
[tblNames SubForm]!txtname = sItem
Next
End Sub

But when I run it I get an error:

Run-time error'3163':
The field is too small to accept the amount of data you attempted to add.
Try inserting or pasting less data.


Any ideas?

Thank you