Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2009
    Posts
    7

    Unanswered: Run Macro based on number of records

    I have a Inventory Table that grows and shrinks as we add or remove products from our snack bar.

    Currently to zero out a sales report, we click a button and it runs a macro called InventoryUpdate. What this does is take the Current Inventory and copies the value to Original Inventory thus showing no sales, because Current = Original.

    This macro has an action "RunMacro" which is named
    InventoryUpdate(repeat), the repeat count is hard coded to 93.

    This is the problem.. we add and delete products from time to time, If we go below 93 offered items, I get an error saying Category must be set (at the bottom of the form it has a blank record entry) where you select by drop down if it's a drink, snack, frozen or candy. If we have more than 93 then it won't update 94-120 or how many more we have.

    The question is, instead of repeating a seperate macro a hard coded number of times, can't it determine the number of records and repeat the marco that many times?

    Let me know if I need to go into any more detail.

    Thanks!!

    Joe

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Difficult to say without actually seeing the marco but if the macro just has 93repeating lines of the same code (which do the same thing on each line - just repeating the steps 93 times), I would make it so the macro does what it's supposed to for 1 record ("one time") and then add somewhere in code the number of times I want to execute that macro. For example:
    For X = 1 to HowManyTimes
    Call MacroName
    Next X

    But you have to be careful that the macro does not have code dependant upon the other repeating lines of code.

    Then if you want it to execute the macro X times where X is the number of records on that form, you can get X with the =Count(*) or =Count() command (or using dlookup, writing other code, etc.). But you'll most likely need to do a little bit of vba coding.

    Ex:

    (cmdExecuteMyMacroButton is a button on the form)

    Private sub cmdExecuteMyMacroButton_Click()
    dim RecCount as variant
    RecCount = count()
    For X = 1 to RecCount
    Call MacroName
    Next X
    Last edited by pkstormy; 08-04-09 at 21:10.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Aug 2009
    Posts
    7
    Thanks for the reply.

    The macro is in access not in VBA, but if it would be easier to work with in VBA then I am listening.

    I can do this in Excel but I am just getting my feet wet in Access.

    I re-wrote the macro as follows:
    Actions
    GoToRecord
    Object Type: Form
    Object Name: InventoryAdmin
    Record: First
    SetValue
    Item: [Forms]![InventoryAdmin]![Original Stock]
    Expression: =([Forms]![InventoryAdmin]![Current Stock])
    GoToRecord
    Record: Next

    However all this does is update the first item..

    Is there anything I can add to the expression to make it loop?

    or the last GoToRecord ?

    Thanks for looking.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ask any Access guru: Macros are to be avoided.

    My advice is to rewrite the macro in VBA where you can encase it in a looping structure.

    PK has given you a start.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Aug 2009
    Posts
    7
    I will have to look into how to manipulate a table via VBA, I know how to do it in Excel, but Access is pretty new to me. Thanks everyone.. if I ever get it working I will post the code so others can see how I did it.

Posting Permissions

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