Why am I getting a Run-time error '3265' - "Item not found in the collection" error message?

In a previous post -I had asked the following question and rockiroad  provided the code below -
I hope that Rockiroad will answer this question so I can give him the appropriate points.

We have an Access 2000 database that contains a table with a column of subject's first, middle (or initial or no middle name), last names. For security reasons, we need to change all  first, middle, last names to initials. What VBA code would be used to carry out this task - using a random group of 3 letters followed by a $ and the first initial,
a random group of 4 letters followed by # and the mid initial and a random group of three letters followed by * and the initial for the last name.

I get a  Run-time error '3265' - "Item not found in the collection"  when I run the code below and the following code is highlighted -

Debug.Print rs!firstName & " " & rs!LastName, newName

What would cause this and how do I correct it?

Public Sub UpdateNames()

    Dim rs As DAO.Recordset
    Dim fullName() As String
    Dim newName As String
    Dim rndWord As String

    'Create a recordset
    Set rs = CurrentDb.OpenRecordset("tblSCJ")

    'Iterate thru it
    Do While rs.EOF = False

        rndWord = GenerateRandomName
        'This assumes name has been separated by a space, if not replace " " with the char of your separator
        fullName = Split(rs!fullName, " ")
        newName = Left$(rndWord, 3) & "$" & Left$(fullName(0), 1) & Mid$(rndWord, 4, 4) & "#" & Left$(fullName(1), 1) & Right$(rndWord, 3) & "*"
        If UBound(fullName) >= 2 Then
            newName = newName & Left$(fullName(2), 1)
        End If
        rs!fullName = newName
        'Mark record for update
        Debug.Print rs!firstName & " " & rs!LastName, newName

    Set rs = Nothing
End Sub

Public Function GenerateRandomName() As String

    Dim lAscDigFirst As Long
    Dim lAscDigCount As Long
    Dim lAscChrFirst As Long
    Dim lAscChrCount As Long
    Dim lAscShift    As Long
    Dim sRandom As String
    Dim lChar As Long
    Dim lNo As Long
    lAscDigFirst = 48
    lAscDigCount = 10
    lAscChrFirst = 65
    lAscChrCount = 26
    lAscShift = lAscChrFirst - (lAscDigFirst + lAscDigCount)
    iLen = Abs(iLen)
    sRandom = Space(10)
    While lNo < 10
        lChar = lAscDigFirst + (Rnd() * (lAscDigCount + lAscChrCount - 1))
        If lChar >= lAscDigFirst + lAscDigCount Then
            lChar = lChar + lAscShift
        End If
        Mid(sRandom, lNo + 1) = Chr(lChar)
        lNo = lNo + 1
    GenerateRandomName = sRandom
End Function
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Access complains because the recordset does not have a field "firstName" , "LastName", or both.  Check tblSCJ to see if those exist and are spelled correctly.  You may have made a change in the table's field names and now you need to update the code to match.



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PDSWSSAuthor Commented:
I am relatively new to DAO -

The the first name, middle name and last name are in a field named   "fullName"

Should the code then read     Debug.Print rs!fullName, newName

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.