Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643

    Error using Collection

    I have a procedure where I loop through a recordset, test for a condition, and construct a string of the results.
    Since the results often contain duplicates I send the resultant string to a procedure which iterates through the string
    using a collection with a key to weed out the duplicates.

    This worked great but I decided to eliminate passing the string to the collection procedure and to use a collection instead of the string.

    This didn't work and I was receiving odd errors trying to iterate through the collection. A .count was showing the collection to have the correct number of elements


    Code:
                If rs!SomeField = strChk Then
                    On Error Resume Next
                    MyCol.Add rs!SomeField, CStr(rs!SomeField)
                    On Error GoTo 0
                End If
    What did work was assigning the recordset value to a variable and using that in the collection .add statement.


    Code:
            MyVar = rs!SomeField
    
                If MyVar = strChk Then
                    On Error Resume Next
                    MyCol.Add MyVar, CStr(MyVar)
                    On Error GoTo 0
                End If
    This works great but I am at a loss to understand why.
    Any thoughts?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    remove the : On Error GoTo 0, and it will TELL you the error.
    but if
    CStr(MyVar) tries to add a key that already exists, it will fail. (or null)


  3. #3
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    The problem wasn't with the loop. It ran ok and if I did a MyCol.count it showed that the correct number of elements were added to the collection.
    The problem was it would error when trying to iterate through the collection with a for...each loop or a For i = loop.
    One error was "No current record" which didnt make sense.
    When I added the variable, no more errors. Just seems odd.

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    The error might not be in the code snippet shown at all. We don't see the declaration for the dataset, the check for eof, the .movenext or the declaration of the collection and MyVar. Plenty of places for an error. Having On Error Resume Next implies that you expect an error and want to ignore it. As ranman noted, take that out and something else may be revealed.

  5. #5
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    The whole idea of using a collection is to ignore the duplicates, thus the on error resume next. There are also several more if ... then statements in the procedure so the on error goto 0 is to reset the error handler. Not sure its needed but it doesnt make a difference either way.

    Originally I would loop through the recordset and would concatenate a string which may look like 1,1,2,3,4,5,1,3,2,4,6,3,3. I would then pass that string to a function which used a collection, setting a key to eliminate the duplicates.
    Code:
                If LvDist(strChk, rs!PartyID) < 4 Then
                   strOut = strOut & rs!PartyID & ","
                End If
    I then changed the code to
    Code:
                If LvDist(strChk, rs!PartyID) < 4 Then
                    On Error Resume Next
                    MyCol.Add rs!PartyID, CStr(rs!PartyID)
                End if
    The collection was being populated as MyCol.Count would show the correct number.

    If I tried
    Code:
    For each varItem in MyCol
    debug.print varItem
    Next
    
    or 
    
    For i = 1 to MyCol.count
    debug.print MyCol(i)
    Next i
    the debug.print line would error "no current record" A few other error messages popped up but I dont remember them.
    As soon as I assigned the recordset value to a variable it worked without issue. I'm just baffled why using a variable works and using rs!Somefield doesn't.

  6. #6
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Heres an example of the error.
    the only difference is one test uses a variable, the other doesn't.

    If i remove the on error goto 0 in the recordset version it doesn't error nor does it execute the For each loop

    error_example.accdb

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you want only one field, then why not use DISTINCT in the SQL so there won't be any duplicates?

    SELECT DISTINCT txt FROM Table1;

    Unfortunately, that won't fix the "recordset" code but does make the On Error Resume Next unnecessary for the other.

    What is the ultimate goal? Does this simple sample truly reflect what you are trying to accomplish?

    I have never used Collection code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Code:
     oCol.Add rs![txt].Value, rs![txt].Value    '<---
    Try this.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yep, that works.

    I always thought Value was default property, but apparently not for recordset fields.

    Why use CStr()? It works but don't see need.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I believe it returns the no current record error because the item being added is an object, not a string. Try oCol.Add CStr(rs!txt), CStr(rs!txt)
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Quote Originally Posted by Micron View Post
    I believe it returns the no current record error because the item being added is an object, not a string. Try oCol.Add CStr(rs!txt), CStr(rs!txt)
    Yep, that works too.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Okay, so either .Value OR CStr(). One makes the other unnecessary. Because this works:

    oCol.Add rs!txt.Value, CStr(rs!txt)

    Wait, so does:

    oCol.Add rs!txt.Value, rs!txt
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Quote Originally Posted by June7 View Post
    Okay, so either .Value OR CStr(). One makes the other unnecessary. Because this works:

    oCol.Add rs!txt.Value, CStr(rs!txt)

    Wait, so does:

    oCol.Add rs!txt.Value, rs!txt
    \

    I'm gonna memorize this and be ready when it comes up again!

  14. #14
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    When you type a variable as variant, then convert to a string (Cstr)
    or get the .Value property value from a field or control (.Value property value is either numeric or string, but can be interpreted as a string by a variant variable)
    you are adding a string to the collection, which is certainly no longer an object.

    Not that this is better, but here's how I would modify the test2 sub:
    Code:
    Public Sub test2()
    
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim strSql As String, strItem As String, strKey As String
        Dim oCol As Collection
        Set oCol = New Collection
    
        strSql = "select * from Table1"
        Set db = CurrentDb()
        Set rs = db.OpenRecordset(strSql)
    
        Do Until rs.EOF
          On Error Resume Next
          strItem = rs!txt
          strKey = rs!txt
          oCol.Add strItem, strKey
          On Error GoTo 0
          rs.MoveNext
        Loop
    
        MsgBox "recordcount = " & rs.RecordCount & "  oCol count = " & oCol.Count
        Me.lstTest.RowSource = ""
        
     Do Until n = oCol.Count
        n = n + 1
        Debug.Print oCol.Item(n)
        Me.lstTest.AddItem oCol.Item(n)
    Loop
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    
    End Sub
    I also don't have an issue with Resuming Next in this case. It's a fairly common practice in dealing with collections AFAIC. Also when adding custom properties to databases. In those cases, you get an error if the property already exists so you move on.

  15. #15
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    I believe it returns the no current record error because the item being added is an object, not a string. Try oCol.Add CStr(rs!txt), CStr(rs!txt)
    I think your right. I always thought .value was the default but since you can have a collection of objects, it makes sense.

    The actual procedure is part of a name search procedure. It takes either a first name, last name, or full name and loops through the names in the database.
    The names are compared for an exact match, a match based on the Levenschtein distance, and a soundex match. Obviously any exact match will have the same levenschtein distance and will also be a soundex match. As a result the collection would contain a lot of duplicates but by assigning the collection key the duplicates are ignored.
    I use this procedure whenever entering names to eliminate adding duplicate people. If it returns no matches a form is opened to enter a new person. If there are matches it opens a form with a list of the people found along with some secondary identifiers such as date of birth, address, city, state, etc. You can then select the appropriate match, if applicable, or opt to enter a new person.
    My original method took about 5 to 10 seconds to loop over a thousand names. With some tweaks and using the collection it now takes about 1-2 seconds.

    Thanks All, this was driving me crazy. I feel better now

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 04-20-2018, 04:45 AM
  2. Error "item not found in this collection"
    By andy49 in forum Queries
    Replies: 7
    Last Post: 11-12-2016, 12:46 PM
  3. Run-Time Error '3265': Item Not Found In This Collection
    By Voodeux2014 in forum Programming
    Replies: 3
    Last Post: 01-29-2016, 09:04 AM
  4. Forms collection vs. AllForms collection
    By Access_Novice in forum Programming
    Replies: 1
    Last Post: 01-03-2016, 12:30 AM
  5. Replies: 3
    Last Post: 11-16-2012, 10:15 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums