Results 1 to 10 of 10
  1. #1
    mcucino is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Location
    Providence, RI
    Posts
    74

    Rs.find method

    Hello all, I am having trouble navigating a recordset. This is a recursive situation where there is a list of IDs in the LineItemT and some of them depend on other line items in the same table (as many levels deep as the user wants, usually only two or three). The dependencies are tracked in a table, DependenciesT with a structure of [ID], [ComponentID]. I am trying to assign a sequence number to each line item in LineItemT (Seq) by looping through the recordset to the lowest level item (one with no dependencies), assigning a Seq, and then move to the next record. The issue I am having is that rs.find "id = " & componentID is not actually moving the recordset to that position so I'm stuck in an infinite loop. Is there another step I need to take to get the recordset to actually move to that record? (PS, I know the sequence is being assigned backwards and there are a variety of issues with the code structure, I just want to figure out the navigation first. I'll take any other advice, though.)



    Code:
    Public Sub AssignOrder()
    Dim db As dao.Database
    Dim rs As dao.Recordset
    
    
        Set db = CurrentDb
        RunSQL "UPDATE DependenciesQ SET seq = 0;"
        Set rs = db.OpenRecordset("SELECT * FROM DependenciesQ where seq = 0;")
        
        CalcSequence = 1
        Stop
        rs.MoveFirst
        Do Until rs.EOF
            If rs!Seq > Calc.CalcSequence Or rs!Seq = 0 Then
                rs.Edit
                rs!Seq = Calc.CalcSequence
                rs.Update
                CalcSequence = Calc.CalcSequence + 1
            End If
            If DCount("*", "DependenciesQ", "ID = " & rs!ComponentID) = 0 Then
               'End of the line - no dependencies that remain unassigned
                rs.Requery
                rs.MoveNext
            ElseIf rs!ID <> rs!ComponentID Then
                'find the component's own dependencies
                rs.FindFirst "ID = " & rs!ComponentID
            End If
        Loop
        
        rs.Close
        db.Close
        
        Set rs = Nothing
        Set db = Nothing
    
    
    End Sub

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    do you want findfirst? or findnext?

  3. #3
    mcucino is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Location
    Providence, RI
    Posts
    74
    I tried it both ways - same result
    Click image for larger version. 

Name:	rs.findfirst.JPG 
Views:	18 
Size:	59.9 KB 
ID:	37355

  4. #4
    mcucino is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Location
    Providence, RI
    Posts
    74
    And this is what the data looks like in regard to specifically this ID and Component IDs:

    Click image for larger version. 

Name:	components.JPG 
Views:	17 
Size:	15.4 KB 
ID:	37356

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Could you provide sample data as text in post? Or attach a file. Never mind, think I can work with the image. Should there be Seq field in that table?

    Compile fails on RunSQL line. Now it fails on CalcSequence and Calc. Do we need some more code?

    Is DependenciesQ a query? What is the SQL statement?
    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.

  6. #6
    mcucino is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Location
    Providence, RI
    Posts
    74
    I can't get into the database now because I'm running some tests, but DependenciesQ joins LineItemsT with DependenciesT. Seq is located in LineItemsT. The only other relevant code (I think) is dimming the global variable "CalcSequence" at the module level (for this you could probably just dim it right in the function), and you can remove the "Calc." in front of anything (I'm just lazy and like the intellisense pop up - calc is the name of the module this is in).

    As soon as the database is done running the current test, I'll put together more info for you. I appreciate you taking the time to look at it!

  7. #7
    mcucino is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Location
    Providence, RI
    Posts
    74
    Okay, I thought I had it figured out but I do not. Please help! I made an example database. The form, TestF, has a button that is supposed to identify the original line item that each refers to (for example, if 10 depends on 9 which depends on 8 which doesn't depend on anything, then 10 ultimately depends on 8)RecursiveFunction.accdbRecursiveFunction.accdb

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Data in db does not look anything like posted sample.

    So code updates Done field but what do you want to do with the 8? There is no Seq field. There is no DependenciesT table and no query.

    I don't see how you determine 8 doesn't depend on anything.
    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.

  9. #9
    mcucino is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Location
    Providence, RI
    Posts
    74
    I couldn't provide my actual DB so I tried to make a quick demo. It just prints what the original component is using debug.print. I'm sorry I didn't clarify, I used 0 for the component ID to indicate that the item did not depend on any other items. Really none of this matters, though, because rs.findnext isn't working at all - it's not actually moving the recordset to the indicated record, it's just causing an infinite loop. There's got to be something stupid I'm missing here, all I want this example database to do is move to the record where the id = componentID until component ID = 0, then mark it as done and move next, and I can't seem to do that.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Code won't work because FindNext and FindFirst move pointer to another record so when executing MoveNext the pointer is not on the starting record. Next record is never set as the MainItem. Infinite loop.

    Data in database doesn't make sense. There are no ID groups as shown in post.
    Last edited by June7; 02-13-2019 at 03:43 AM.
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 04-16-2018, 09:15 PM
  2. Replies: 3
    Last Post: 07-26-2017, 08:14 AM
  3. Replies: 6
    Last Post: 06-25-2016, 02:56 PM
  4. Replies: 7
    Last Post: 05-12-2016, 06:02 PM
  5. Which method is better?
    By undrcvr in forum Database Design
    Replies: 3
    Last Post: 05-24-2010, 12:46 PM

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