I am running some VBA code that splits a name field into FirstName, MiddleNames and LastName. The code uses a recordset that has 410,000 records.
The code seems to work fine until about 300,000 records and then I get the error:
Invalid argument Err No 3001.
After this error occurs and I stop the program, I cannot insert values into any of the new fields, I get the same error. Even if I close the table (from which the records are taken) and then reopen the table and try to copy the table I get the same error.
This is the code I am using:
Private Sub cmdDonorNameSplit_Click()
Dim dbMain As DAO.Database
Dim rsPOTDONORS As DAO.Recordset
Dim myMiddleNames As String
Dim myName() As String
Dim i As Integer
Set dbMain = CurrentDb
Set rsPOTDONORS = dbMain.OpenRecordset("POT_DONORS", dbOpenDynaset, dbConsistent, dbOptimistic)
Do While Not .EOF
If !Field3 Like "INA*" Or !Field3 Like "INU*" Or !Field3 Like "SIA*" Or !Field3 Like "SIU*" Or !Field3 Like "VOU*" Or !Field3 Like "DRU*" Then
myName = Split(!Field1, " ", -1, vbTextCompare)
If UBound(myName) <> 0 Then !FirstName = myName(0)
![LastName/OrgName] = myName(UBound(myName))
myMiddleNames = ""
For i = 1 To (UBound(myName) - 1)
myMiddleNames = myMiddleNames & " " & myName(i)
If myMiddleNames <> "" Then !MiddleNames = myMiddleNames
![LastName/OrgName] = !Field1
MsgBox "The Donor Name Split is complete!"
The error always occurs on the .Update statement after the For...Next loop.
Can someone please tell me how to fix this problem?