Results 1 to 7 of 7

Thread: Repeating Macro

  1. #1
    Join Date
    Feb 2004
    Posts
    214

    Exclamation Unanswered: Repeating Macro

    I import an excel spreadsheet into Access. Then I run a macro that repeats 1000 times. The 1000 times is a ceiling number. I know there will never be more than 1000 records in there. Is there a way to have it repeat until there are no more records left as opposed to a static number.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What does your macro do? Is there a good reason this is not set based (SQL) or at least written in VBA?

  3. #3
    Join Date
    Feb 2004
    Posts
    214

    ...

    I have people working particular accounts out of the database. I get an excel download every day with new accounts. I import that spreadsheet and assign them evenly to the people working the accounts. I pull a top 1 query of all accounts that are unassigned and it is then assigned to the person with the least amount of accounts. I cycle this 1,000 times to make sure all of the accounts imported are assigned to someone. Sometimes the file has 700-900 accounts and other times it is 200-300. I would like to be able to repeat until there are no more records left to assign.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok.

    You'll find (in fact you might have already) that you'll usually get a bit of a blank look if you mention Macros here. Most of the regulars here avoid macros like the plague and as such we tend to be pretty hopeless at answering macro questions straight.

    Unfortunately without knowing more of the detail of the macro I can't suggest much constructive apart from translate your macro into VBA and run it from there. Looping is trivial in VBA (in fact just about any programming language) and limiting the loop to the number of rows is also trivial.

    If you look at the DoCmd object in VBA help you will see that its methods translate exactly one-to-one to the macro commands you are familiar with.

    HTH

  5. #5
    Join Date
    Feb 2004
    Posts
    214

    Talking ...

    Thank you for your help. I will try to look at moving it over to VBA.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by mr. blonde
    Thank you for your help. I will try to look at moving it over to VBA.
    You will never look back I promise

  7. #7
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    If you are running a macro as a RunMacro from another macro then you can stop the maco with a condition.

    The macro stops when the expression is false.

    For example

    [Forms]![YourFormName]![Number] Is Not Null

    That stops the macro when it has reached the end of the records...because the [number] field is now null.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •