Help with looping VBA

Question

Hi.

I need to loop through a table choosing records and moving them to another table.  I have over 250,00 rows and doing the whole table at once chokes the app.  I've decided to loop through and break the data down into smaller bites.

What I need to do is have a loop inside a loop.  The internal loop (after the FOR statement below) needs to be repeated 13 times and the external loop needs to be repeated until a table called tblRouteList has no more null values in the field "Done".

Please see the code below.  I tried doing this with macros calling each other but it bails out after 10 cycles with error 2525.

Thanks in advance

Function mcrResetNextRoute()

On Error GoTo mcrResetNextRoute_Err

    DoCmd.OpenQuery "qryTruncateService_Location", acViewNormal, acEdit

    DoCmd.OpenQuery "qryUpdateDoneRoute", acViewNormal, acEdit

    DoCmd.OpenQuery "qryTruncatetblNextRoute", acViewNormal, acEdit

    DoCmd.OpenQuery "qryAppendNextRoute", acViewNormal, acEdit

    DoCmd.OpenQuery "qryAppendtoService_Location", acViewNormal, acEdit

    DoCmd.OpenQuery "qryResetPhaseNumber", acViewNormal, acEdit

    For i = 1 To 13

    DoCmd.OpenQuery "qryAppendTotblPhases", acViewNormal, acEdit

    DoCmd.OpenQuery "qryUpdateDelDate", acViewNormal, acEdit

    DoCmd.OpenQuery "qryDeleteRecords", acViewNormal, acEdit

    DoCmd.OpenQuery "qryUpdatePhase", acViewNormal, acEdit

    DoCmd.OpenQuery "qryIncrementPhaseNumber", acViewNormal, acEdit

    Next i

mcrResetNextRoute_Exit:

    Exit Function

mcrResetNextRoute_Err:

    MsgBox Error$

    Resume mcrResetNextRoute_Exit

End Function

Answer 1

First don't use Docmd.OpenQuery if you want to do this unattended. Use CurrentDB.Execute instead.

Second, you create a Recordset and then use a Do While loop to move through each record.

Answer 2

I do want to learn more about loops and understand how they work.  Can you recommend a good book(s) or sites?

Yes mine <shameless plug>. While the title refers to Access 2007, VBA has not changed since then so the lessons carry forward. You can find a link to the book at my website listed in my profile.

Answer 3

I do want to learn more about loops and understand how they work.  Can you recommend a good book(s) or sites?

Answer 4

Looping and conditionals are 75% of programming. If you want to do any sort of automation you need to understand the concepts.

Answer 5

Hi Scott.

Thanks for the response.  Looping is very new to me.  In fact, this is the first time I've tried it.  I did some research and the since I know exactly how many times I need the loop I've moved to a For...Next construction.

I'll let you know how it turns out and post the code (assuming it works!).