Page 1 of 3 123 LastLast
Results 1 to 15 of 31

Repeat macro until record count of query = 0 - Repeat Expression syntax

  1. #1
    janbrown56's Avatar
    janbrown56 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    18

    Repeat macro until record count of query = 0 - Repeat Expression syntax

    Greetings,



    I have a macro that needs to run repeatedly until the number of records in a query =0 but I can't seem to get the Repeat Expression set properly.

    My query is called sqMatchCount:
    SELECT Count(sqCompare_Parts_Matched_1st.BRP_Entry_Num) AS MatchCount
    FROM sqCompare_Parts_Matched_1st;

    The theory is if I don't have any more matches then I do not want the macro to run any more. In other words, MatchCount will = 0 when there are no more matches.

    I have tried a variety of syntax using DCount in the Repeat Expression:

    =DCount("*","sqMatchCount") ...... Macro runs in a continuous loop
    DCount("*","sqMatchCount")=0 ...... Macro does not run (although I thought a couple times it actually DID run with this syntax)
    DCount("MatchCount","sqMatchCount")=0 ...... Macro does not run
    =DCount("MatchCount","sqMatchCount")=0 ....... Macro does not run
    =DCount("MatchCount","sqMatchCount") ....... Macro runs in a continuous loop

    What IS the proper way to write this Repeat Expression for the RunMacro Action if I want it to run as long as sqMatchCount.MatchCount>0?

    Thanks in advance for any help you can give me.

    Jan

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,842
    What is changing the count of this query?
    How are the records being updated so that the count changes?
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  3. #3
    redbull's Avatar
    redbull is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Mar 2012
    Location
    Missouri
    Posts
    480
    Seems like you might need an IF statement..

    Code:
    Private Sub Form_Current()
    
     Dim rs As Recordset
     Set rs = Me.RecordsetClone
    
        rs.MoveFirst
        If rs.RecordCount < 1 Then
            MsgBox "No results found! Please try again."
            Cancel = True
            Exit Sub
        End If
     Else docmd.runquery"I think"   
    End Sub
    If you could post a larger piece of the code block on here I might be able to dive a littler deeper. I am VERY shaky with record sets and queries in general so I could use the brain work as well. I found the above code posted Here.

    I am sure the code above will not 100% solve your issue, but it should point in the right direction... without knowing much about it I assume you could use

    Code:
    If rs.RecordCount > 1 Then
    docmd.runquery"I think"    
    Do Until rs.RecordCount < 1 Then
    Loop
    End If
    --Edit Point--
    To quote someone I saw online "This Advice and code is Largely untested"
    Last edited by redbull; 08-09-2012 at 08:30 AM. Reason: Disclaimer

  4. #4
    janbrown56's Avatar
    janbrown56 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    18
    Hi Joe,

    For clarification I am doing this in a macro (not good at code so trying to use the RunMacro Repeat Expression).

    This is the process:

    Select Query table 1 and table 2 for matches. If count >0 (this is where I need to know what syntax to use for the macro Repeat Expression) then run the repeat macro that does:
    1. OpenQuery - append query - append to table 3
    2. OpenQuery - delete query - delete from table 1
    3. OpenQuery - delete query - delete from table 2

    When there are no more matches I need the repeated macro to stop. From my research it should be able to be done using DCount in the RunMacro Repeat Expression. I just cannot figure out the exact syntax.

    Thanks

  5. #5
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,842
    Can you post the VBA code/loop you have?
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  6. #6
    redbull's Avatar
    redbull is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Mar 2012
    Location
    Missouri
    Posts
    480
    Quote Originally Posted by JoeM View Post
    Can you post the VBA code/loop you have?
    Second this motion.

  7. #7
    janbrown56's Avatar
    janbrown56 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    18
    Thanks Joe and redbull,

    I bit the bullet and added code rather than using the RepeatExpression but it is not running enough times. Here is the VBA code for the macro that I set up to run the other macro until a condition is met:

    Option Compare Database

    '------------------------------------------------------------
    ' mcrComparedParts_Part_2
    '
    '------------------------------------------------------------
    Function mcrComparedParts_Part_2()
    On Error GoTo mcrComparedParts_Part_2_Err

    Do While DCount("*", "sqCompare_Parts_Matched_1st") > 0
    DoCmd.RunMacro mcrComparedPartsRepeated
    Loop
    Beep
    MsgBox "Part 2 (repeating) complete.", vbOKOnly, ""


    mcrComparedParts_Part_2_Exit:
    Exit Function

    mcrComparedParts_Part_2_Err:
    MsgBox Error$
    Resume mcrComparedParts_Part_2_Exit

    End Function

    After that macro ran I ran the sqMatchCount query and the MatchCount value is 170, which means there are still matching records that need to be processed. Based on testing the macros manually and noting record counts after each time I know that the macro ran only one time. So now I think my VBA code must be incorrect.

    Thanks again.

  8. #8
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,842
    I think it is because it may be calculating the count once, and then not recalculating it for the duration of your loop.
    Maybe try it this way:
    Code:
    Do 
        If DCount("*", "sqCompare_Parts_Matched_1st") > 0 Then
            DoCmd.RunMacro mcrComparedPartsRepeated
        Else
            Exit Loop
        End If
    Loop
    Also, is there some reason why all the records can't be corrected at once, instead of doing a loop?
    If you are unsure, post the part of the code that fixes them.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  9. #9
    redbull's Avatar
    redbull is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Mar 2012
    Location
    Missouri
    Posts
    480
    Received a syntax error, silly space on Exitloop. I was in the middle of typing this up when I saw your fast response lol.

    Code:
    Do
        If DCount("*", "sqCompare_Parts_Matched_1st") > 0 Then
            DoCmd.RunMacro mcrComparedPartsRepeated
        Else
            ExitLoop
        End If
    Loop

  10. #10
    janbrown56's Avatar
    janbrown56 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    18
    Current VBA code - still only runs 1 time:

    Do
    If DCount("*", "sqMatchedCount") > 0 Then
    DoCmd.RunMacro mcrComparedPartsRepeated
    Else
    ExitLoop
    End If
    Loop

    To answer Joe's question "Also, is there some reason why all the records can't be corrected at once, instead of doing a loop?" - What I am trying to accomplish is rather convoluted and cannot be done in one step. I have orders for parts in 2 different data sources and they need to be compared. Each order has multiple delivery schedule dates (up to 30 or more) but the number of schedule dates won't be the same for each data source and the dates may or may not agree. For each data source I have an order table to load their data into. I append each PO/Part/Date/Qty to their respective tables and assign unique ID's to each record so I know which ones I'm matching, merging, transferring and need to be removed. First I match up PO/Part by the dates and those that are the same get merged and transferred to a third table and then removed from the 2 order tables. Then I query the PO/Part and First Date of each of the 2 orders, match, merge and transfer those to the third table and delete them from the order tables. After each move I query how many PO/Parts match. I need to do this until there are no more matches (the parts left in order table A have no records in order table B or vice versa). This why I need to loop. I hope this helps.

    Jan

  11. #11
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,842
    Not sure I understand why it isn't working, but then again, I do not have access to your database to review your queries and other VBA code.
    But maybe something like this will work for your loop:
    Code:
    Dim myCount as Long
    Dim i as Long
    
    '   Get total cound of matched records we need to process for loop counter
        myCount=DCount("*","sqrCompare_Parts_Method_1st")
    
    '   Return count (just in testing, for your own piece of mind!)
        MsgBox myCount
    
    '   Create a loop to run your macro the number of times determined in our count
        For i = 1 to myCount
            DoCmd.RunMacro mcrComparedPartsRepeated
        Next i
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  12. #12
    redbull's Avatar
    redbull is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Mar 2012
    Location
    Missouri
    Posts
    480
    Possible to try this?

    Code:
    If DCount("*", "sqMatchedCount") > 1 Then
    DoCmd.RunMacro mcrComparedPartsRepeated
    do until DCount("*", "sqMatchedCount") < 1
    Loop
    End If
    
    So this code should, keep running until the sqMatchedCount") < 1

    Question...

    If you F8 though the code, does the loop command at least send the code back up to the "
    If DCount("*", "sqMatchedCount") > 1" line?


  13. #13
    janbrown56's Avatar
    janbrown56 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    18
    JoeM and redbull,

    I had a couple things wrong with my code: I was referencing the wrong query - sqMatchCount only has one record, which is the Count of records in query sqCompare_Parts_Matched_1st so I changed my code to do DCount on sqCompare_Parts_Matched_1st. I was also missing a macro name argument and fixed that. Because I really haven't done much with code I wasn't familiar with debugging with F8 so it helped tremendously. I also turned off error handling because I wasn't seeing the errors.

    This was the code:
    ------------------------------------------------
    If DCount("*", "sqCompare_Parts_Matched_1st") > 1 Then
    DoCmd.RunMacro "mcrComparedPartsRepeated", , ""
    Do Until DCount("*", "sqCompare_Parts_Matched_1st") < 1
    Loop
    End If
    ------------------------------------------------

    At this point the macro still ran only once when I ran it through Access. I used the F8 in VB and it ran the statements like this:
    ********
    If DCount("*", "sqCompare_Parts_Matched_1st") > 1 Then
    DoCmd.RunMacro "mcrComparedPartsRepeated", , ""
    Do Until DCount("*", "sqCompare_Parts_Matched_1st") < 1
    Loop
    Do Until DCount("*", "sqCompare_Parts_Matched_1st") < 1
    Loop
    Do Until DCount("*", "sqCompare_Parts_Matched_1st") < 1
    Loop
    Do Until DCount("*", "sqCompare_Parts_Matched_1st") < 1
    Loop
    Do Until DCount("*", "sqCompare_Parts_Matched_1st") < 1
    Loop
    Do Until DCount("*", "sqCompare_Parts_Matched_1st") < 1
    Loop
    *******
    and it just looped. I checked the tables after a few loops and the counts were not changing. I realized that there was nothing to actually DO in the loop so I added the run macro command. THAT worked while using F8 in VB. It went through about 26 times and then ended and all records were appended to the table. Then I closed VB and ran the macro from Access. It ran ONCE. Why would it loop through while debugging in VB but not if I just Run the Macro?

    Remember, I'm a code newbie so I may be missing something really simple. BTW, I did save the VB code.

    When I look at the design view of Macro mcrComparedParts-Part2 there is one Action of RunMacro, Macro Name=mcrComparedPartsRepeated, Repeat Count and Repeat Expression are both blank. I highlight Macro mcrComparedParts-Part2 and go to Tools>Macro>Visual Basic Editor and the code in mcrComparedParts-Part2 is as follows:
    --------------------------------------------------
    Option Compare Database

    '------------------------------------------------------------
    ' mcrComparedParts_Part_2
    '
    '------------------------------------------------------------
    Function mcrComparedParts_Part_2()

    If DCount("*", "sqCompare_Parts_Matched_1st") > 1 Then
    DoCmd.RunMacro "mcrComparedPartsRepeated", , ""
    Do Until DCount("*", "sqCompare_Parts_Matched_1st") < 1
    DoCmd.RunMacro "mcrComparedPartsRepeated", , ""
    Loop
    End If

    End Function
    ------------------------------------------------------------

    The code for Macro mcrComparedPartsRepeated is as follows:
    ------------------------------------------------------------
    Option Compare Database

    '------------------------------------------------------------
    ' mcrComparedPartsRepeated
    '
    '------------------------------------------------------------
    Function mcrComparedPartsRepeated()

    ' appComparedPartsMatched1st - BRP and P21 Order Lines that match on Plant/Part/Po/Line/ and put the first date on each together
    DoCmd.OpenQuery "appComparedPartsMatched1st", acViewNormal, acEdit
    ' qdelOrdersBRP-IfInComparedPartsTable - Delete the lines from Orders_BRP that were appended above
    DoCmd.OpenQuery "qdelOrdersBRP-IfInComparedPartsTable", acViewNormal, acEdit
    ' qdelOrdersP21-IfInComparedPartsTable - Delete the lines from Orders_P21 that were appended above
    DoCmd.OpenQuery "qdelOrdersP21-IfInComparedPartsTable", acViewNormal, acEdit

    End Function

    '------------------------------------------------------------

    I'm so close I can almost taste it! I really appreciate your perseverence and help.

    Jan

  14. #14
    redbull's Avatar
    redbull is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Mar 2012
    Location
    Missouri
    Posts
    480
    Hmm, I bet I know why!! It can't run the query while it is open... Or something to that effect...

    After this conf call I will post something I hope fixes the issue.

  15. #15
    janbrown56's Avatar
    janbrown56 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    18
    Redbull,

    When I ran the macro the query nor anything else was open, not even the macro. I simply double clicked on it to run it.

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

Similar Threads

  1. How to repeat data?
    By djclntn in forum Forms
    Replies: 4
    Last Post: 06-29-2012, 05:08 PM
  2. Do not repeat pictures
    By gabrielharo in forum Forms
    Replies: 1
    Last Post: 06-12-2012, 04:58 PM
  3. Repeat Expressions
    By welshmpf in forum Access
    Replies: 8
    Last Post: 09-06-2011, 09:41 AM
  4. IIF Expression - No Repeat Please
    By turnbuk in forum Queries
    Replies: 2
    Last Post: 08-11-2011, 02:50 PM
  5. Problem with Trying to Repeat Records in VBA
    By P5C768 in forum Programming
    Replies: 9
    Last Post: 09-22-2010, 12:09 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
  •  
Tech Forums: Microsoft Office Forums