Repeating a Macro using Macro Expression Syntax
Karin Solomon
I need to discover - as a matter of urgency - the syntax for repeating an
Access macro X number of times.
The function of the macro is to fill a table with X number of numbered
rows - for this I have written an Update and Append query and combined them
in a macro - it is this macro that I need to repeat
I cannot use Repeat Count - the user will want to repeat the macro (add a
different number of rows) each time it is run.
What I would like to do is allow the user to enter the number of rows
required on a form, then click on a button to trigger the macro which will
add so many rows to the table.
Can someone tell me the syntax I should use in Repeat Expression?
The Form on which the user will enter the no. of rows required is called:
Shelves
The Field on the Form into which the required no. will be typed is called:
ShelveCounter
The Table to which new rows will be added is called: CurrentSet
Thanks
Karin
Kenneth Snell
handle it in my database.
Essentially, I have a hidden textbox (formatted to general number) on my
form that will be a counter for the repeating of the macro. Call if
txtCounter. Make it invisible.
Assuming that the user of your form enters a number in the ShelveCounter
control and then clicks on a command button to run the macro, set up this
macro attached to the "onclick" event of the command button:
MacroFormPrep
Action: SetValue
Item: Forms!Shelves!txtCounter
Expression: Forms!Shelves!ShelveCounter
Action: RunMacro
Macro Name: (your macro that fills the table with rows)
Repeat Count: (blank)
Repeat Expression: Forms!Shelves!txtCounter=0
Then, put the following step as the first step in your macro that adds the
rows to the table:
Action: SetValue
Item: Forms!Shelves!txtCounter
Expression: Forms!Shelves!txtCounter - 1
What this does is uses the hidden textbox txtCounter to count how many times
your "add rows" macro has run. It is used as a decrement counter.
Good luck.
Watch that you reset/blank out the value in the hidden textbox txtCounter
"Karin Solomon" <ka...@words-out.co.za> wrote in message
news:3cc17d91$0$2...@hades.is.co.za...
Steve Schapel
Add another action to the end of your macro, being...
SetValue
Item: [ShelveCounter]
Expression: [ShelveCounter]-1
Make the Repeat Expression of your RunMacro macro to...
[ShelveCounter]>0
- Steve Schapel, Microsoft Access MVP
Kenneth Snell
the txtCounter textbox.....sometimes cut-and-paste is a bad thing!
"Kenneth Snell" <ksn...@comcast.net> wrote in message
news:O7CuQXJ6BHA.1944@tkmsftngp03...
Karin Solomon
Thanks for your quick response.
I have since worked out a solution, but will test your method as well to see
which gives me the quickest result.
Very interesting to compare tricks, isn't it!
K.
Steve Schapel
How can we compare tricks if you don't tell us what yours is?
- Steve Schapel, Microsoft Access MVP
Steve Schapel
Great response, and if I'd known you were "on the job", I would have
not bothered with my other post which more or less parallels your idea
(although you use an extra step in the process). Only one problem I
see... Repeat Expression should be >0 rather than =0 I think?
- Steve Schapel, Microsoft Access MVP
Karin Solomon
This is the solution I've come up with - may be useful to someone along the
line:
1.) An input form opens for the user to specify the Start Number and End
Number of records they want to add
(eg. records numbered 1000 to 2500)
2.) A click on button "Continue" activates a macro that repeats:
- an Update query that uses Start Number + 1 to number rows
- an Append query that creates an writes the numbered rows to a Final Table
3) The repeat ends when the Record Number equals the End Number specified by
the user. The statement I used in the Macro's Repeat expression is:
[Forms]![Final Table]![Row Number]<[Forms]![User Input Form]![End Number]
4) This solution requires me to specify a Repeat Count. I'm using 50 000
records as a limit - very unlikely that this number will be reached before
the above statement is false but, should this happen, the user can simply
run the above sequence again from the input screen and add whichever records
are missing.
Happy coding!
K.
Kenneth Snell
apology!
Thanks for your compliment about my response. I added the extra step in my
process only so that the number entered on the screen by the user would not
be changed by Access as it did the macro. My intent in my many databases is
to always leave the original value showing so that the user can see if an
error was made on the input if something goes wrong. Hence, my use of the
hidden textbox for the counting decrementation.
But I was pleased to see that my idea paralleled (actually, as I am the
novice, let me say "followed in the path of") your reply. I am learning!
"Steve Schapel" <sch...@mvps.org.ns> wrote in message
news:ovd3cucoe3bg9qcbq...@4ax.com...
Steve Schapel
That's a very interesting idea. Unfortunately, it won't work in its
present form. There is at least one more step you will need to put in
(which maybe you have, but just didn't mention). Your Repeat
Expression obviously depends on the value on the Final Table form.
This won't work. You will need to have a control on the form that
shows the Max() of [RowNumber], and this will need to get updated
every time a new record is added, which means you will need to use a
Requery action to requery the Final Table form after every Append
Query instance.
This, of course, is very inefficient. I appreciate that apparently
you are pleased to have worked out a solution, but I would really
reconmmend you go with more like Kenneth's idea, which is a standard
procedure.
- Steve Schapel, Microsoft Access MVP
Karin Solomon
Current state of my solution:
On his form, the user specifies a [Start Number] and an [End number], used
to:
- Number records as they are appended to the final table, and
- Used as a control for stopping the Repeat Macro.
As the Update query runs, the Start Number is updated, is appended to the
Final Table, and the macro stops when Repeat Expression: [Start Number]<[End
Number] proves false. This works in its current form - no requery action
specified. Did you mean it wouldn't work effectively, or not at all?
I did forget to mention something: The user, not knowing that I'm using
[Start Number]+1 in the Update query, will specify [Start Number] as say,
100. The query will then number the first row that is appended as 101, not
100! So, I've put in an initial Append query that runs once to append the
first record to the final table, numbered with [Start Number] currently on
the user input screen, before it gets updated by the Update-Append sequence
until [Start Number]<[End Number] is false. This way there is no need for
an invisible Counter field on the form.
I am incredibly chuffed to have found this User Group - supportive and
encouraging. I'll make contributions where I can.
Karin
Karin Solomon
Current state of my solution:
On his form, the user specifies a [Start Number] and an [End number], used
to:
- Number records as they are appended to the final table, and
- Used as a control for stopping the Repeat Macro.
As the Update query runs, the Start Number is updated, is appended to the
Final Table, and the macro stops when Repeat Expression: [Start Number]<[End
Number] proves false. This works in its current form - no requery action
specified. Did you mean it wouldn't work effectively, or not at all?
I like Kenneth's idea of a decrement counter but I'm greedy, you see.
Not only do I want X number of rows to be added, but I want the rows to be
numbered according to the user's specifications, which could be anything
from
390 - 647 or 1 - 400 or whatever.
Steve Schapel
On Sun, 21 Apr 2002 08:14:43 +0200, "Karin Solomon"
<ka...@words-out.co.za> wrote:
>As the Update query runs, the Start Number is updated, is appended to the
>Final Table, and the macro stops when Repeat Expression: [Start Number]<[End
>Number] proves false. This works in its current form - no requery action
>specified. Did you mean it wouldn't work effectively, or not at all?
I meant it wouldn't work at all, but I was referring to your previous
idea of...
[Forms]![Final Table]![Row Number]<[Forms]![User Input Form]![End
Number]
... which is quite a different concept, unless I've misunderstood,
which is quite a possibility.
What you are now doing is actually a variation on the theme of what
Kenneth and I were suggesting, i.e. countdown or countup based on the
unbound range criteria control.
So, congratulations. Looks like a workable system now.
Mind you, now that I know a bit more, such as the need for
user-specified beginning and ending record numbers, and the possible
numbers of records involved, I have another idea! Don't know whether
you want to know it, but anyway, here goes...
Put another table in your database, with just one field, Number data
type, and put as many records in there as you need, with numbers from
1 to the maximum you would expect the range of appends to cover. It
doesn't matter if this is a big number, Access can handle it,
(although I would probably use Excel's auto-fill to create the
numbers, and then import, to save time setting up the table.)
Then, simply add this table to whatever you have currently got set up
for your one-by-one append query, and then in the criteria of the
field from this table, put...
Between [Forms]![User Input Form]![Start Number] And [Forms]![User
Input Form]![End Number]
Save yourself a *lot* of trouble and time, just run this Append Query
and add all your required rows at once. What do you think? On
reflection, I think I would certainly do it this way myself.
>I am incredibly chuffed to have found this User Group - supportive and
>encouraging. I'll make contributions where I can.
It'll be great to have your participation. There are a number of
microsoft.public.access.* newsgroups, of which this one is not the
most active, but we all seem to learn a lot.
Steve Schapel
Just to clarify, the alternative solution I suggested is of course,
incomplete, as without knowing the details of your append query, I
can't be specific. If there's a table on which the existing append is
based, then using my idea would also involve a criteria on its primary
key in order to restrict the output to just one set of the cartesian
product. But it will definitely work, and be soooo much faster.
- Steve Schapel, Microsoft Access MVP
On Sun, 21 Apr 2002 20:59:10 +1200, Steve Schapel
<sch...@mvps.org.ns> wrote:
>Put another table in your database, with just one field, Number data
>type, and put as many records in there as you need, with numbers from
>1 to the maximum you would expect the range of appends to cover.
>
Karin Solomon
The idea of having in the database a table containing all the numbers I may
ever need was the first one I considered when I started on this problem.
But my gut said: No! No! No! and that's when I got entangled with the
repeating macro story.
I still feel the same about it and, to be honest, neither am I 100% at ease
with what I've got now! I'm going to leave it for a day or two and then go
back to the drawing-board. Access has never disappointed me when I was
prepared to search a little more.
My main reservation about the current solution and the filled table standing
at the ready is: "as many numbers as you'll ever need". The table will
have a limit and my current solution has a limit in its repeat count query.
I KNOW I'll never need more numbers that than, but that has a certain Y2K
ring to it, don't you think? And its just not being fair to Access who, I
believe, has a solution containing no restrictions, if I am prepared to go
and find it.
My second reservation is having to keep in the database a table containing
350 000 records/numbers that may be used just once a year. It feels
unwieldy.
Finally, remember that the records must not only be added, but need to be
numbered asccording to user specifications. My users may require only 200
records to be added, but may need them to be numbered from 400 001 to 400
200. What then?
I may sound like a doom-prophet but am actually enjoying myself immensely,
looking forward to the party I'm going to throw when I've got this one
sorted. Thank you very much for staying with me on this: I will always
want to hear what you think.
Karin.
Steve Schapel
A agree that it is sometimes very satisfying to go chasing after a
solution, and to feel sure that keeping on digging around will reveal
the "answer". And obviously I share your enthusiasm about Access.
My comments on a few of your points:
- Yes, a table with 350000 records which hardly ever get used is
unwieldy. But, once it's set up, it causes no problems. And because
it's only one numerical field, it won't take up much space. And, most
importantly, unwieldiness is relative, and compared with running an
Update Query 350000 times and an Append Query 350000 times, compared
with one Append Query once, well....
- I noticed that your current method includes a repeat count entry
for your RunMacro macro, as well as the repeat expression entry. I
would have thought the repeat count was redundant?
- The table of numbers would only need to have as many as the number
of records you wanted to append (so 350000 would possibly be 'more
than enough'?). The rest of it can be taken care of within the Append
Query. For example, if the user enters number of records and starting
number, the append query can have...
RecordNumber: [TableNumber]+[Forms]![InputForm]![StartNumber]
(assumes [TableNumber] starts at 0)
and then the criteria for [TableNumber] would be...
<=[Forms]![InputForm]![RecordsToAdd]
- If the user enters a number of records on InputForm which is
greater than the number in NumbersTable, you can throw up a message
box at that point to get them to reduce it.
- Steve Schapel, Microsoft Access MVP