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

Posted on 2008-11-14
Medium Priority
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

pteranodon72 earned 2000 total points
ID: 22964394
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

ID: 22964540
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
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.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

864 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