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

Posted on 2008-11-14
Last Modified: 2013-11-27
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
Question by:PDSWSS
    LVL 14

    Accepted Solution

    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.



    Author Comment

    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


    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now