Go Back   Access World Forums > Microsoft Access Discussion > Macros

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-20-2019, 02:14 PM   #1
Sdata
Newly Registered User
 
Join Date: Aug 2019
Location: Oregon
Posts: 6
Thanks: 4
Thanked 0 Times in 0 Posts
Sdata is on a distinguished road
Running submacro in a loop a variable number of times

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.

Sdata is offline   Reply With Quote
Old 11-20-2019, 02:20 PM   #2
theDBguy
I’m here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,653
Thanks: 58
Thanked 1,460 Times in 1,441 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Running submacro in a loop a variable number of times

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?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-20-2019, 02:56 PM   #3
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,399
Thanks: 13
Thanked 4,125 Times in 4,057 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Running submacro in a loop a variable number of times

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

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

__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 11-20-2019, 05:31 PM   #4
vba_php
Software Engineer
 
vba_php's Avatar
 
Join Date: Oct 2019
Location: Iowa City, IA, USA
Posts: 701
Thanks: 84
Thanked 79 Times in 79 Posts
vba_php is on a distinguished road
Re: Running submacro in a loop a variable number of times

apparently you've already answered this question dbGuy:

http://www.utteraccess.com/forum/ind...wtopic=2044101
vba_php is offline   Reply With Quote
Old 11-20-2019, 05:57 PM   #5
theDBguy
I’m here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,653
Thanks: 58
Thanked 1,460 Times in 1,441 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Running submacro in a loop a variable number of times

Quote:
Originally Posted by vba_php View Post
apparently you've already answered this question dbGuy:

http://www.utteraccess.com/forum/ind...wtopic=2044101
Hi. Thanks.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-21-2019, 08:18 AM   #6
Sdata
Newly Registered User
 
Join Date: Aug 2019
Location: Oregon
Posts: 6
Thanks: 4
Thanked 0 Times in 0 Posts
Sdata is on a distinguished road
Re: Running submacro in a loop a variable number of times

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?
Sdata is offline   Reply With Quote
Old 11-21-2019, 08:32 AM   #7
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,399
Thanks: 13
Thanked 4,125 Times in 4,057 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Running submacro in a loop a variable number of times

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
...

__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
Sdata (11-21-2019)
Old 11-21-2019, 08:46 AM   #8
theDBguy
I’m here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,653
Thanks: 58
Thanked 1,460 Times in 1,441 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Running submacro in a loop a variable number of times

Quote:
Originally Posted by Sdata View Post
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
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...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Sdata (11-21-2019)
Old 11-21-2019, 01:54 PM   #9
Sdata
Newly Registered User
 
Join Date: Aug 2019
Location: Oregon
Posts: 6
Thanks: 4
Thanked 0 Times in 0 Posts
Sdata is on a distinguished road
Re: Running submacro in a loop a variable number of times

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.
Sdata is offline   Reply With Quote
Old 11-21-2019, 02:00 PM   #10
theDBguy
I’m here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,653
Thanks: 58
Thanked 1,460 Times in 1,441 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Running submacro in a loop a variable number of times

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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-21-2019, 02:21 PM   #11
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,399
Thanks: 13
Thanked 4,125 Times in 4,057 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Running submacro in a loop a variable number of times

Happy to help!

__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Reply

Tags
macro loop

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Defining how many times to loop. TobyMace Modules & VBA 7 09-19-2018 02:12 AM
Running queries multiple times in VBA Richhol65 Modules & VBA 4 12-10-2014 02:33 AM
Loop not looping correct # of times WineSnob Modules & VBA 4 05-01-2012 10:19 AM
macro cannot run for more than 20 times through a loop Richard Flame Macros 1 04-15-2009 02:07 PM
Need to loop .wav file three times mshelley1 General 1 05-22-2006 04:43 AM




All times are GMT -8. The time now is 04:57 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World