Running submacro in a loop a variable number of times (1 Viewer)

S

Sdata

New member

Local time

Yesterday, 22:15

Joined

Aug 20, 2019

Messages

6

• Nov 20, 2019

• #1

I am trying to create a macro running a submacro that will repeat the submacro a variable number of times. I am not a programmer so I would like to stay within the macro format rather than code in VBA if possible.

The submacro has a query to reduce the number in RepeatCount by 1 each time the submacro runs. Here is the visual of the macro command.

RunMacro
Macro Name: MacroToRepeat
Repeat Count:
Repeat Expression = [RepeatCountTable]![RepeatCount] > 0

I tried many variations of the Repeat Expression but kept receiving the error “Microsoft Access cannot find the name ‘RepeatCountTable’ you entered in the expression.” Researching for a solution lead me to the Microsoft Access help website telling me “If you run a macro containing the RunMacro macro action in a library database, Access looks for the macro with this name in the library database and doesn’t look for it in the current database”

Is there an easy way to get around this error? Referencing libraries is way above my knowledge base.

Thank you.

 

theDBguy

I’m here to help

Staff member

Local time

Yesterday, 22:15

Joined

Oct 29, 2018

Messages

21,447

• Nov 20, 2019

• #2

Hi. If you're not using a library database, then this note shouldn't affect you. Where would the number of times to repeat the macro supposed to come from?

 

pbaldy

Wino Moderator

Staff member

Local time

Yesterday, 22:15

Joined

Aug 30, 2003

Messages

36,131

• Nov 20, 2019

• #3

I don't use macros but you can't get a table value that way. See if this works:

DLookup("[RepeatCount]", "[RepeatCountTable]")

 

vba_php

Forum Troll

Local time

Today, 00:15

Joined

Oct 6, 2019

Messages

2,884

• Nov 21, 2019

• #4

apparently you've already answered this question dbGuy:

http://www.utteraccess.com/forum/index.php?showtopic=2044101

 

theDBguy

I’m here to help

Staff member

Local time

Yesterday, 22:15

Joined

Oct 29, 2018

Messages

21,447

• Nov 21, 2019

• #5

vba_php said:

apparently you've already answered this question dbGuy:

http://www.utteraccess.com/forum/index.php?showtopic=2044101

Click to expand...

Hi. Thanks.

 

S

Sdata

New member

Local time

Yesterday, 22:15

Joined

Aug 20, 2019

Messages

6

• Nov 21, 2019

• #6

Thanks everyone for responding quickly.

I used Access to covert my macro to visual basic. However, when I attempt to type in DLookup("[RepeatCount]","[RepeatCountTable]") I get Compile error expected: =

DBguy- Before the submacro, a set of queries calculates the number of times I need to run the submacro and stores the number in the [RepeatCount] field. There is only one record in the table. The number of times changes and I was hoping to minimize the run time of the macro by only running the submacro as few times as necessary. The submacro runs a query [RepeatCount] = [RepeatCount]-1

I can see the problem selecting values from tables since each field can hold many values. Would using a Sum([RepeatCount]) get me anywhere since that can only hold a single value? Not sure if that is on the right track but thought I would ask and learn.
Not sure what I am missing?

 

pbaldy

Wino Moderator

Staff member

Local time

Yesterday, 22:15

Joined

Aug 30, 2003

Messages

36,131

• Nov 21, 2019

• #7

What does the VBA look like? You'd have to set something equal to the result of the DLookup, like:

VariableName = DLookup(...)

For x = 1 to VariableName
...

 

theDBguy

I’m here to help

Staff member

Local time

Yesterday, 22:15

Joined

Oct 29, 2018

Messages

21,447

• Nov 21, 2019

• #8

Sdata said:

DBguy- Before the submacro, a set of queries calculates the number of times I need to run the submacro and stores the number in the [RepeatCount] field. There is only one record in the table. The number of times changes and I was hoping to minimize the run time of the macro by only running the submacro as few times as necessary. The submacro runs a query [RepeatCount] = [RepeatCount]-1

Click to expand...

Would it be possible to use the SetLocalVar action to store that number in a local variable to make it easier to get the number from within the macro? Just curious...

 

S

Sdata

New member

Local time

Yesterday, 22:15

Joined

Aug 20, 2019

Messages

6

• Nov 21, 2019

• #9

pbaldy and DBguy- It looks like the problem was solved. It appears the macro commands provided in Access won't do the job but the the For Loop worked perfect using VBA. Here is the code I used if anyone needs a sample in the future:

Count = DLookup("[RepeatCount]", "[RepeatCountTable]")

For X = 1 To [Count]

DoCmd.OpenQuery "Pull New Active Strings", acViewNormal, acEdit
DoCmd.OpenQuery "Match Active Seller and Buyer Strings", acViewNormal, acEdit
DoCmd.OpenQuery "Remove Current Strings", acViewNormal, acEdit

Next X


DBguy- I did not spent much time testing SetLocalVar but it appears to have the same problem where Access can not find the table.

 

theDBguy

I’m here to help

Staff member

Local time

Yesterday, 22:15

Joined

Oct 29, 2018

Messages

21,447

• Nov 21, 2019

• #10

Hi. Glad to hear you got it sorted out. I prefer to use VBA too, but I also think a macro could work as well. For example, I think there is a SearchForRecord action that might work for it. Good luck with your project.

 

pbaldy

Wino Moderator

Staff member

Local time

Yesterday, 22:15

Joined

Aug 30, 2003

Messages

36,131

• Nov 21, 2019

• #11

Happy to help!