Results 1 to 5 of 5

Thread: loop in macro

  1. #1
    Join Date
    Feb 2004
    Posts
    139

    Unanswered: loop in macro

    I am trying to set up a macro to loop within a macro. I have the loop macro under the run macro action and want to set the repeat count to a field on the main form that is currently open. It will not let me set the field as the count. It says that it must be a number. However, a number will appear in this field when the macro runs. Here is how I have the repeat count set.

    "[Forms]![ss order form]![hotkey]"

    Should I express it in a different way? Another thought was using the expression field. I could have it maybe do a sum of the QTY field and PRODID field of the current form to determine how many times the part number has been entered with the above field being the number of times it should be entered. However, I do not know how to set the expression to accomplish this task. The data is stored in the ss order details table in the prodid and qty field for the current order number.

    Appreciate any help!
    B&R

  2. #2
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151
    Wouldn't it be easier to use a loop within a VBA module calling a macro? That way you could grab your variable a lot easier.

    My 2cents
    --If its free, take it for what its worth!

  3. #3
    Join Date
    Feb 2004
    Posts
    139
    Do you mean, convert this macro to a VBA module and then call up this module? If I converted the macro, how would I specify to use the hotkey field for the number of repeats?

    Thanks for your help!
    B&R

  4. #4
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151
    That is one way or...
    I'm assuming you probably have a button and on the click event it calls this particular macro. If so, have it do something like this.

    The button click stores the number of repeats into a variable and then creates a loop such as....

    do while count < my_repeat_variable

    docmd.run_my_macro

    loop

    Maybe not the best explanation but I hope you get the gist.

    Stu
    --If its free, take it for what its worth!

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    just for fun...
    ...in a copy of your work, convert the macro to VBA and post the resulting code.
    it is no issue at all to pick up data from forms with VBA using the exact expression you give in #1

    izy
    currently using SS 2008R2

Posting Permissions

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