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.Edit
        rs!fullName = newName
        rs.Update
       
        'Mark record for update
        Debug.Print rs!firstName & " " & rs!LastName, newName
       
        rs.MoveNext
    Loop

    rs.Close
    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)
   
    Randomize
    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
    Wend
   
    GenerateRandomName = sRandom
 
End Function
PDSWSSAsked:
Who is Participating?
 
pteranodon72Connect With a Mentor Commented:
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.

HTH,

pT
0
 
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

Thanks,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.