Results 1 to 10 of 10
  1. #1
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    Do while/until loop util

    Vlad developed the below code and it works great. But if you add more platform_id in it will count up. Like its designed to do. What I was trying to do with Do until platform_id <> platform_id of previous count, then reset icounter back to 1.


    If I put Do while and loop (3rd loop) around the below code it will lock up/run a looooooong time. How do I store the platform_id and use that to check it against the next platform_id? Then reset to new when icounter reserts to 1?

    I tried Dlookup and it would stay at the first record.

    I put
    sPlatformID = rstId("platform_id") at the beginning. That stores the first platform_id



    When I put at the end inside the outer loop:

    Code:
    If
    sPlatformID = rstChild![platform_id] then
    iCounter = iCounter + 1 Elseif
    sPlatformID <> rstChild![platform_id] then
    iCounter = iCounter + 1
    End If
    rstid.movenext
    in place of last
    iCounter = iCounter + 1
    rstId.MoveNext
    in the outside loop.


    I placed a msgbox in the loop to track splatformID and
    rstId("platform_id") and I watch it count up. When they don't match, the count does not reset to 1.


    Code:
    sSQL = "UPDATE Table1 SET Table1.EndCnt='0';"
    CurrentDb.Execute sSQL, dbFailOnError
    'Start sorting
    sSQL = "SELECT qryMEQUIPWithChild.* FROM qryMEQUIPWithChild Where [Row Type]='MEQUIP';"
    Set rstId = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset)
    'Parents with Children
    iCounter = 1
    Do Until rstId.EOF
    ' Parents
        sPlatformID = rstId("platform_id")
        sUniqueID = rstId("unique_id")
        rstId.Edit
        rstId("EndCnt") = iCounter
        rstId.Update
    ' Children
        sSQLChild = "SELECT Table1.* FROM Table1 WHERE Table1.parent_equipment_item_id = '" & sUniqueID & "' AND[Row Type]='MEQUIP' ORDER BY Table1.ID;"
        Set rstChild = CurrentDb.OpenRecordset(sSQLChild, dbOpenDynaset)
            Do Until rstChild.EOF
                iCounter = iCounter + 1
                rstChild.Edit
                rstChild("EndCnt") = iCounter
                rstChild.Update
                rstChild.MoveNext
            Loop
        iCounter = iCounter + 1
        rstId.MoveNext
    Loop
    Suggestions?
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    using code in loops to update records will SLOW your pc down.
    use an update query to run them all at once.

  3. #3
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Ranman256,
    and in these update queries you have to run lookup, dcount, and that slows it down considerably. I don’t see how you can do a specialized count of filtered data, then rerun another filter to recount data, then run another count on a filtered data, then there is the issue where you need to start a count where you have to find the parent with children, count the first parent, then count it’s children, and repeat the process for the next parent child, until all aren’t-child criteria is exhausted for that platform_id., then count the remaining childless parents in ascending unique_id order, then repeate for the next 978 platform_ids. With each restarting the count at 1.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I am not sure what you want or where you are headed.
    I added another field to Table1 named "platCnt" (Platform Count)
    I ran your existing code, then created a query to look at the results.

    Is the hierarchy platform_id----> unique_id ----> parent_equipment_item_id ???

    Looking at the image below at the top 6 rows, how do you want "platCnt" to be numbered? Like this?
    Click image for larger version. 

Name:	MEQUIP1.png 
Views:	17 
Size:	266.2 KB 
ID:	37908

  5. #5
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    ssanfu
    I am trying to get:
    Platform1
    …..Parent1 Count = 1
    ……….Child1 Count = 2
    ……….Child2 Count = 3
    …..Parent2 Count = 4

    ……….Child1 Count = 5
    ……….Child2 Count = 6....

    Reset

    Platform2
    …..Parent1 Count = 1

    ……….Child1 Count = 2
    ……….Child2 Count = 3
    …..Parent2 Count = 4

    ……….Child1 Count = 5
    ……….Child2 Count = 6....Loop until EOF.

    Above by the Column headers:

    Platform 1 = platform_id (Count = 0)
    Parent (unique_id) on platform_id where the parent_equipment_item_id =Platform_id (Count = 1)
    Child of parent (unique_id) on Parent where the parent_equipment_item_id= Child of parent (unique_id) (Count = 2)

    Next Child of parent (unique_id) on Parentwhere the parent_equipment_item_id = Child of parent (unique_id) (Count= 3)

    Parent (unique_id) on platform_id where the parent_equipment_item_id =Platform_id (Count = 4)

    Child of parent (unique_id) on Parent wherethe parent_equipment_item_id = Child of parent (unique_id) (Count = 5)
    Next Child of parent (unique_id) on Parent where theparent_equipment_item_id = Child of parent (unique_id) (Count = 6)


    Childless Parent (unique_id) where parent_equipment_item_id <> Child ofparent (unique_id) (Count = 7)

    Childless Parent (unique_id) whereparent_equipment_item_id <> Child of parent (unique_id) (Count =8)

    Until the platform_id does not have anymore unique_id where parent_equipment_item_id = Platform_id

    Then Reset count = 1
    Platform 1 = platform_id (Count = 0)
    Parent (unique_id) on platform_id where the parent_equipment_item_id =Platform_id (Count = 1)
    Child of parent (unique_id) on Parent where the parent_equipment_item_id =Child of parent (unique_id) (Count = 2)
    Parent (unique_id) on platform_id where the parent_equipment_item_id= Platform_id (Count = 3)
    Child of parent (unique_id) on Parent where the parent_equipment_item_id =Child of parent (unique_id) (Count = 4)
    Next Child of parent (unique_id) on Parent where theparent_equipment_item_id = Child of parent (unique_id) (Count = 5)


    Childless Parent (unique_id) where parent_equipment_item_id <> Child ofparent (unique_id) (Count = 6)

    Childless Parent (unique_id) whereparent_equipment_item_id <> Child of parent (unique_id) (Count =7)……….repeat unitl EOF.


    Click image for larger version. 

Name:	Dsired.jpg 
Views:	17 
Size:	197.8 KB 
ID:	37909


    I think I have it for all except the childless parents on the platform:


    Code:
    Dim rstId As DAO.Recordset, rstChild As DAO.Recordset, rstPar AsDAO.Recordset
    Dim sUniqueID As String, sPlatformID As String
    Dim sSQLPar As String, sSQLChild As String, sSQL As String
    Dim iCounter As Integer
    ' Reset EndCnt to 0
    sSQL = "UPDATE Table1 SET Table1.EndCnt='0';"
    CurrentDb.Execute sSQL, dbFailOnError
    ' Start sorting
    sSQL = "SELECT qryMEQUIPWithChild.* FROM qryMEQUIPWithChild Where [RowType]='MEQUIP' and Expr1 <> 0;"
    Set rstId = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset)
    iCounter = 1
    ' Parents with Children
    Do Until rstId.EOF
    ' Parents
        sPlatformID = rstId("platform_id")
        sUniqueID = rstId("unique_id")
        rstId.Edit
        rstId("EndCnt") = iCounter
        rstId.Update
    ' Children
        sSQLChild = "SELECT Table1.* FROM Table1 WHERETable1.parent_equipment_item_id = '" & sUniqueID & "' AND[RowType]='MEQUIP' ORDER BY Table1.ID;"
        Set rstChild = CurrentDb.OpenRecordset(sSQLChild,dbOpenDynaset)
            Do Until rstChild.EOF
            iCounter = iCounter + 1
                rstChild.Edit
                rstChild("EndCnt") =iCounter
                rstChild.Update
                rstChild.MoveNext
            Loop
            rstId.MoveNext
        If sPlatformID = rstId![Platform_id] Then
            iCounter = iCounter + 1
        ElseIf sPlatformID <> rstId![Platform_id] Then
            iCounter = 1
        ElseIf rstid.EOF ir rstChild.EOF Then
            Exit Do
        End If
    Loop
    End Sub
    But I get Run-time error '3021': No current record found." at the boldedcode above. I am surmising it is the rstChild.EOF or rstID.EOF

    Otherwise the counts for parent child relationship works perfectly fine

    Attached is what Vlad and I came up with so far.

    What is odd to me is that Excel with VBA can do this a lot faster.
    Attached Files Attached Files

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Again, I changed some field names to remove spaces and special characters.

    Still trying to understand your last post.
    Is this closer?
    Attached Files Attached Files

  7. #7
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Steve,
    I see you lined up the unnique_ids in ascending order and counted from there. That doesn't always work as the data input do not always do it in the correct sequesnce. Thus you will get radio channels before you get the radio mount. Or you will have some wierd other piece of equipment stuck in between. The code vlad and I did puts the radio mounts as the first Table2_equip_1, then the radios themselves in table2.equip_2.
    Table2-ssanfu_output is the outcome of your count sequence.
    Table2-Wayne_outcome is the outcome of mine and vlad count sequence.

    Compare record 21 in both the above tables.

    I do like the speed in which yours counts, vice the wait for ours.
    Attached Files Attached Files
    Last edited by Thompyt; 03-22-2019 at 12:41 PM.

  8. #8
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Duplicate since I figured out how to replace attachments.

  9. #9
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Vlad,ssanfu,
    Got it figured out:

    Code:
    ' Childless
            sSQLPar ="SELECT qryMEQUIPWithChild.* FROM qryMEQUIPWithChild WHERE [RowType]='MEQUIP' and qryMEQUIPWithChild.EndCnt = 0 ORDER BYqryMEQUIPWithChild.ID;"
            Set rstPar= CurrentDb.OpenRecordset(sSQLPar, dbOpenDynaset)
            Do UntilrstPar.EOF
               sPlatformID = rstPar![platform_id]
        If sPlatformID= rstPar![platform_id] Then
                   iCounter = rstPar![Expr2] + 1
        ElseIfsPlatformID <> rstPar![platform_id] Then
            iCounter =0
        End If
               rstPar.Edit
               rstPar![EndCnt] = iCounter
               rstPar.Update
               rstPar.MoveNext
            Loop

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Excellent!

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

Similar Threads

  1. Replies: 9
    Last Post: 03-07-2017, 02:49 PM
  2. Replies: 12
    Last Post: 06-05-2015, 04:27 PM
  3. Replies: 13
    Last Post: 08-20-2014, 09:17 AM
  4. Replies: 17
    Last Post: 04-07-2014, 07:48 PM
  5. Replies: 3
    Last Post: 03-10-2013, 07:04 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