Repeat a macro using an expression

[Crazy AL]

Hi there,
I saw a request dated back to feb/March of 2006 that could possibly
solve my issue but kind of left me a little lost on how to do it. let me
back up and explain. (Don't worry, I'll keep it simple.)

I'm trying to get my macro to read thru a query to the end (last record)
however I always get an error. Tried to do some conditions based on NULL or
values but I always get the "You can't go to the specific record / You may be
at the end of a recordset" error message. How can I setup my macro to stop
looping thru when it reaches the end of entered records?

here is my macro so far.

open query
gotorecord (first record)
runmacro AAA (based on repeat expression) <----- my problem

AAA blah blah stuff (procedure of whatever)
goto record (next record)
--------------------------------------
What can I put in the repeat expression to control the looping?

I don't know Visual Basic or how to write modules, sorry

Help please!!!

 

[Steve Schapel]

Crazy,

- Replace the OpenQuery action with an OpenForm action, to open a
continuous view form based on your query
- Put an unbound textbox on the form, possibly in the header or
footer, 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: [RecordsProcessed]
Expression: [RecordsProcessed]+1
- In the Repeat Expression argument of the RunMacro action in the
first macro, enter the equivalent of:
[RecordsProcessed]<=[Forms]![YourFormName].[RecordsetClone].[RecordCount]

 

[Crazy AL]

I'm not at work today, but tomorrow, when I'm in, I will give it a try and
let you know. Thanks for the suggestion. Sounds promising!

Crazy AL

Crazy,

- Replace the OpenQuery action with an OpenForm action, to open a
continuous view form based on your query
- Put an unbound textbox on the form, possibly in the header or
footer, 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: [RecordsProcessed]
Expression: [RecordsProcessed]+1
- In the Repeat Expression argument of the RunMacro action in the
first macro, enter the equivalent of:
[RecordsProcessed]<=[Forms]![YourFormName].[RecordsetClone].[RecordCount]

--
Steve Schapel, Microsoft Access MVP

Hi there,
I saw a request dated back to feb/March of 2006 that could possibly
solve my issue but kind of left me a little lost on how to do it. let me
back up and explain. (Don't worry, I'll keep it simple.)

I'm trying to get my macro to read thru a query to the end (last record)
however I always get an error. Tried to do some conditions based on NULL or
values but I always get the "You can't go to the specific record / You may be
at the end of a recordset" error message. How can I setup my macro to stop
looping thru when it reaches the end of entered records?

here is my macro so far.

open query
gotorecord (first record)
runmacro AAA (based on repeat expression) <----- my problem

AAA blah blah stuff (procedure of whatever)
goto record (next record)
--------------------------------------
What can I put in the repeat expression to control the looping?

I don't know Visual Basic or how to write modules, sorry

Help please!!!

 

[Crazy AL]

Ok I'm at work at last (booooo weekend is over).
So I tried following your instructions. Couple of small questions.
1st: As I am launching the macro from a form, is it safe to presume I can
remove the "OPEN FORM" statement and include the [records_processed] field in
there?
2nd: You wrote

"enter the equivalent of:
[RecordsProcessed]<=[Forms]![YourFormName].[RecordsetClone].[RecordCount]"

what is the recordsetclone.recordcount?

Crazy,

- Replace the OpenQuery action with an OpenForm action, to open a
continuous view form based on your query
- Put an unbound textbox on the form, possibly in the header or
footer, 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: [RecordsProcessed]
Expression: [RecordsProcessed]+1
- In the Repeat Expression argument of the RunMacro action in the
first macro, enter the equivalent of:
[RecordsProcessed]<=[Forms]![YourFormName].[RecordsetClone].[RecordCount]

--
Steve Schapel, Microsoft Access MVP

Hi there,
I saw a request dated back to feb/March of 2006 that could possibly
solve my issue but kind of left me a little lost on how to do it. let me
back up and explain. (Don't worry, I'll keep it simple.)

I'm trying to get my macro to read thru a query to the end (last record)
however I always get an error. Tried to do some conditions based on NULL or
values but I always get the "You can't go to the specific record / You may be
at the end of a recordset" error message. How can I setup my macro to stop
looping thru when it reaches the end of entered records?

here is my macro so far.

open query
gotorecord (first record)
runmacro AAA (based on repeat expression) <----- my problem

AAA blah blah stuff (procedure of whatever)
goto record (next record)
--------------------------------------
What can I put in the repeat expression to control the looping?

I don't know Visual Basic or how to write modules, sorry

Help please!!!

 

[Steve Schapel]

Crazy,

You will need to have a form open that shows the records that you want
to process. If this is the form that you are launching the macro from,
then no, you don't need an OpenForm action in the macro.
[RecordsetClone].[RecordCount] is a built-in property of the form that
returns the number of records in the recordset that is equivalent to the
table or query that the form is based on.

--
Steve Schapel, Microsoft Access MVP

Ok I'm at work at last (booooo weekend is over).
So I tried following your instructions. Couple of small questions.
1st: As I am launching the macro from a form, is it safe to presume I can
remove the "OPEN FORM" statement and include the [records_processed] field in
there?
2nd: You wrote

"enter the equivalent of:
[RecordsProcessed]<=[Forms]![YourFormName].[RecordsetClone].[RecordCount]"

what is the recordsetclone.recordcount?

 

[Crazy AL]

Hi Steve,
It's not working, thru no lack of effort from your part. Could be I'm just
not doing something right. Let me relist the macro for you. It's pretty
short.


setvalue
Item:[Forms]![Update Printed Label Form]![Records_processed]
expression:0

Setvalue
item:[Forms]![Update Printed Label Form]![Records_processed]
expression:[Forms]![Update Printed Label
Form]![Records_processed]+1

openquery (if I don't open the query, I
get an error the the next action
query name : Printed Query
view: datasheet
data mode: edit

gotorecord
Obj type: query
Obj name: Printed Query
Record: First
Offset:
runmacro
macro name : Resetprintflag.readrecord
Repeat Expression: [Forms]![Update Printed Label
Form]![Records_processed]<=[Forms]![Update Printed Label
Form].[RecordsetClone].[RecordCount]

close
query

 

[Crazy AL]

Hey Steve,
Did you forget me? Hehehehe...

Still in need of your genious, please.

Hi Steve,
It's not working, thru no lack of effort from your part. Could be I'm just
not doing something right. Let me relist the macro for you. It's pretty
short.


setvalue
Item:[Forms]![Update Printed Label Form]![Records_processed]
expression:0

Setvalue
item:[Forms]![Update Printed Label Form]![Records_processed]
expression:[Forms]![Update Printed Label
Form]![Records_processed]+1

openquery (if I don't open the query, I
get an error the the next action
query name : Printed Query
view: datasheet
data mode: edit

gotorecord
Obj type: query
Obj name: Printed Query
Record: First
Offset:
runmacro
macro name : Resetprintflag.readrecord
Repeat Expression: [Forms]![Update Printed Label
Form]![Records_processed]<=[Forms]![Update Printed Label
Form].[RecordsetClone].[RecordCount]

close
query
.
.
.

readrecord (stuff in macro)
gotorecord
Object type: query
Object name: printed query
Record: next
Offset:

Crazy,

You will need to have a form open that shows the records that you want
to process. If this is the form that you are launching the macro from,
then no, you don't need an OpenForm action in the macro.
[RecordsetClone].[RecordCount] is a built-in property of the form that
returns the number of records in the recordset that is equivalent to the
table or query that the form is based on.

--
Steve Schapel, Microsoft Access MVP

Ok I'm at work at last (booooo weekend is over).
So I tried following your instructions. Couple of small questions.
1st: As I am launching the macro from a form, is it safe to presume I can
remove the "OPEN FORM" statement and include the [records_processed] field in
there?
2nd: You wrote
"enter the equivalent of:
[RecordsProcessed]<=[Forms]![YourFormName].[RecordsetClone].[RecordCount]"

what is the recordsetclone.recordcount?

 

[Crazy AL]

Ok is there anyone else out there who can help me? It seems I have been
abandonned.

Hey Steve,
Did you forget me? Hehehehe...

Still in need of your genious, please.

Hi Steve,
It's not working, thru no lack of effort from your part. Could be I'm just
not doing something right. Let me relist the macro for you. It's pretty
short.


setvalue
Item:[Forms]![Update Printed Label Form]![Records_processed]
expression:0

Setvalue
item:[Forms]![Update Printed Label Form]![Records_processed]
expression:[Forms]![Update Printed Label
Form]![Records_processed]+1

openquery (if I don't open the query, I
get an error the the next action
query name : Printed Query
view: datasheet
data mode: edit

gotorecord
Obj type: query
Obj name: Printed Query
Record: First
Offset:
runmacro
macro name : Resetprintflag.readrecord
Repeat Expression: [Forms]![Update Printed Label
Form]![Records_processed]<=[Forms]![Update Printed Label
Form].[RecordsetClone].[RecordCount]

close
query
.
.
.

readrecord (stuff in macro)
gotorecord
Object type: query
Object name: printed query
Record: next
Offset:

Crazy,

You will need to have a form open that shows the records that you want
to process. If this is the form that you are launching the macro from,
then no, you don't need an OpenForm action in the macro.
[RecordsetClone].[RecordCount] is a built-in property of the form that
returns the number of records in the recordset that is equivalent to the
table or query that the form is based on.

--
Steve Schapel, Microsoft Access MVP

Crazy AL wrote:
Ok I'm at work at last (booooo weekend is over).
So I tried following your instructions. Couple of small questions.
1st: As I am launching the macro from a form, is it safe to presume I can
remove the "OPEN FORM" statement and include the [records_processed] field in
there?
2nd: You wrote
"enter the equivalent of:
[RecordsProcessed]<=[Forms]![YourFormName].[RecordsetClone].[RecordCount]"

what is the recordsetclone.recordcount?

 

[Steve Schapel]

Crazy,

Sorry you've felt abandoned. I've been on vacation to Australia.

To be honest, I can't see how what you've got here is related at all to
the advice I gave in my earlier replies.

So, let's take it one step at a time... Do you have a form which is
based on the query, to show the records you want to loop through? If
so, what is the name of this form? Is this the form that you will
launch the macro from, via a Click event of a command button?

 

[Crazy AL]

Hey Steve,
HOw was it down under? Just to let you know, don't sweat it. Actually
another MVP answered my question in another post similarly related to this
one. I was going about it the wrong way. Basically I removed about 90% of
my garbage and replaced it with an update query.

Gotta love it when things fall into place

But thanks nonetheless.

Cheers!

Crazy AL

Crazy,

Sorry you've felt abandoned. I've been on vacation to Australia.

To be honest, I can't see how what you've got here is related at all to
the advice I gave in my earlier replies.

So, let's take it one step at a time... Do you have a form which is
based on the query, to show the records you want to loop through? If
so, what is the name of this form? Is this the form that you will
launch the macro from, via a Click event of a command button?

--
Steve Schapel, Microsoft Access MVP

Hi Steve,
It's not working, thru no lack of effort from your part. Could be I'm just
not doing something right. Let me relist the macro for you. It's pretty
short.


setvalue
Item:[Forms]![Update Printed Label Form]![Records_processed]
expression:0

Setvalue
item:[Forms]![Update Printed Label Form]![Records_processed]
expression:[Forms]![Update Printed Label
Form]![Records_processed]+1

openquery (if I don't open the query, I
get an error the the next action
query name : Printed Query
view: datasheet
data mode: edit

gotorecord
Obj type: query
Obj name: Printed Query
Record: First
Offset:
runmacro
macro name : Resetprintflag.readrecord
Repeat Expression: [Forms]![Update Printed Label
Form]![Records_processed]<=[Forms]![Update Printed Label
Form].[RecordsetClone].[RecordCount]

close
query
.
.
.

readrecord (stuff in macro)
gotorecord
Object type: query
Object name: printed query
Record: next
Offset: