looping macro

[boomtap via AccessMonster.com]

I have run into yet another hurdle. I am creating a macro in which I want
the following to happen:
-open form
-go to first record
-open query (append table)
-close query
-check if last record (stop macro if last record)
-go to next record
-go back to step 3 (open query).

I basically want the form to run page by page and run an append query.
I would really appreciate the help.

 

[tina]

well, you might try writing a multi-step macro to
1) turn off Warnings
2) open the Append query (you don't need to "close" an action query; the
OpenQuery action just runs the query, it doesn't return a dataset in a
window.)
3) turn on Warnings
4) go to next record
5) call itself, with a RunMacro action.

run the multi-step macro from another macro, as
1) open form
2) RunMacro (the above multi-step macro)

though i don't think there's any way to make it run smoothly. when the macro
tries to move to the next record, from the last record in the form, you're
going to get an error - and there's no graceful way to handle errors in
macros.

i'd suggest you use VBA. if you're not doing anything in the open form
except moving from record to record to run the action query, then i'd
dispense with the form altogether. you can use looping code in VBA: open a
recordset, set up a loop to move through each record in turn; within that
loop, run the Append query on each record.

hth

 

[boomtap via AccessMonster.com]

The form contains calculations that the append query uses.

well, you might try writing a multi-step macro to
1) turn off Warnings
2) open the Append query (you don't need to "close" an action query; the
OpenQuery action just runs the query, it doesn't return a dataset in a
window.)
3) turn on Warnings
4) go to next record
5) call itself, with a RunMacro action.

run the multi-step macro from another macro, as
1) open form
2) RunMacro (the above multi-step macro)

though i don't think there's any way to make it run smoothly. when the macro
tries to move to the next record, from the last record in the form, you're
going to get an error - and there's no graceful way to handle errors in
macros.

i'd suggest you use VBA. if you're not doing anything in the open form
except moving from record to record to run the action query, then i'd
dispense with the form altogether. you can use looping code in VBA: open a
recordset, set up a loop to move through each record in turn; within that
loop, run the Append query on each record.

hth

I have run into yet another hurdle. I am creating a macro in which I want
the following to happen:

[quoted text clipped - 8 lines]

I basically want the form to run page by page and run an append query.
I would really appreciate the help.

 

[Steve Schapel]

... - and there's no graceful way to handle errors in
macros.

.... until next year

 

[Steve Schapel]

Boomtap,

Whereas I agree with Tina's advice, in fact it is actually quite easy to
do this with a macro by moving from record to record through the form.

But I would be interested to know what calculations are being done on
the form, that you feel could not be done within the query itself. In
my opinion, based on what you have told us so far, it should be possible
to do this with one Append Query, to process all records in one hit.
So, if you'd like to provide some more details, we can have another look
at options.

 

[tina]

something else to look forward to, eh?

... until next year

 

[tina]

i'm really pretty rusty on macros, Steve. how do you avoid the error
generated by a GoToRecord | Next action when you're on the last record in
the recordset?

and i do agree with you that the whole thing can probably be handled by one
Append query; at the least, whatever calcs are taking place in the form
probably can be done at the query level instead.

 

[Steve Schapel]

Tina,

i'm really pretty rusty on macros, Steve.

This is not true, Tina. You're one of the experts .

how do you avoid the error
generated by a GoToRecord | Next action when you're on the last record in
the recordset?

From memory...
- Put an unbound textbox on the form, with its Default Value property
set to 0 - let's say you name this textbox RecordsProcessed
- Put a SetValue action in the macro as well, with arguments set like
this...
Item: [Processed]
Expression: [Processed]+1
- In the Repeat Expression argument of the RunMacro action in the
second macro, enter:

[RecordsProcessed]<=[Forms]![YourFormName].[RecordsetClone].[RecordCount]

 

[tina]

ah, very handy. thanks!

This is not true, Tina. You're one of the experts .

well, once upon a time i was the macro queen, but i completely defected to
VBA several years ago and very, very rarely visit the old country nowadays.

Tina,

i'm really pretty rusty on macros, Steve.

This is not true, Tina. You're one of the experts .

how do you avoid the error
generated by a GoToRecord | Next action when you're on the last record in
the recordset?

From memory...
- Put an unbound textbox on the form, with its Default Value property
set to 0 - let's say you name this textbox RecordsProcessed
- Put a SetValue action in the macro as well, with arguments set like
this...
Item: [Processed]
Expression: [Processed]+1
- In the Repeat Expression argument of the RunMacro action in the
second macro, enter:

[RecordsProcessed]<=[Forms]![YourFormName].[RecordsetClone].[RecordCount]

 

[Steve Schapel]

well, once upon a time i was the macro queen, but i completely defected to
VBA several years ago and very, very rarely visit the old country nowadays.

I haven't used a macro in my own work for many years. But I still hang
around here because I know how great they are for people who are coming
up the learning curve.
But with the new macro emphasis and functionality coming with 2007
Access, that may change again.