RunMacro Command

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

I am attempting to use the RunMacro cmd to append records
to a table a specified number of times. Everything works
except the "Repeat Expression." Using the "Count" works
but I can't get ANY expression to let it run. Anyone got
any ideas? Samples of expressions comparing two fields?
Thanks.
 
I have set up a control table and move the quantity to
make into a field named "MakeNr." Have housekeeping
queries to decrement MakeNr by 1 each time the macro
runs. Repeat expression used: [Test MakeNr table]!
[MakeNr]>0. This tests True until MakeNr reaches 0 then
False. All queries test okay but macro just won't run.
Have also set up field to check and everything, the
decrementing and True/False seem okay. Is there
something wrong with my expression syntax? I have written
several systems in Access but still learning. Thanks
again. Jim
 
have you run the macro on a "step" thru? where is it breaking? is it running
once and then stopping? are you getting a failed macro box showing the
failed step? what process are you using to de-increment the table value? a
note there: instead of putting a value in a table, and de-incrementing it,
it might be a lot easier to set up a hidden, unbound control on a form. set
the value of the control in the macro, then set the Repeat Count to

=[Forms]![MyForm]![MyTextBox].[Value]

leave Repeat Expression blank.
run the macro from a command button on the form.

hth


Jim said:
I have set up a control table and move the quantity to
make into a field named "MakeNr." Have housekeeping
queries to decrement MakeNr by 1 each time the macro
runs. Repeat expression used: [Test MakeNr table]!
[MakeNr]>0. This tests True until MakeNr reaches 0 then
False. All queries test okay but macro just won't run.
Have also set up field to check and everything, the
decrementing and True/False seem okay. Is there
something wrong with my expression syntax? I have written
several systems in Access but still learning. Thanks
again. Jim
-----Original Message-----
please post the expression you're using, and any details connected with it.





.
Click to expand...
Click to expand...
 
PMFJI here Jim. You are getting excellent advice from Tina, so go with
that. But I feel sure that the repeat expression
[Test MakeNr table]![MakeNr]>0
will not work. This is incorrect syntax, and will make no sense at all
to Access. If you are trying to refer to the value of the MakeNr field
in the Test MakeNr table table, well, even if Access gave you the
ability to refer to this value, you would still need to indicate which
record in the table (even if there is only one record in the table...
you know that, but Access will not be "willing" to make this assumption
about a table!) You would either need to reference the value from a
form control, as suggested by Tina, or else use a domain function, for
example DLookup("[MakeNr]","Test MakeNr table")
 
actually, my solution excludes the use of a table entirely - i'm not always
as clear as i mean to be! :)
as for the expression Jim posted - i wondered about that. i didn't think you
could reference a table the way you do a form, at least i've never been able
to...now i know for sure. i'm always grateful for anyone who jumps in with
more info, whether they say my suggestions are good - or bad! thanks, Steve!
:)


Steve Schapel said:
PMFJI here Jim. You are getting excellent advice from Tina, so go with
that. But I feel sure that the repeat expression
[Test MakeNr table]![MakeNr]>0
will not work. This is incorrect syntax, and will make no sense at all
to Access. If you are trying to refer to the value of the MakeNr field
in the Test MakeNr table table, well, even if Access gave you the
ability to refer to this value, you would still need to indicate which
record in the table (even if there is only one record in the table...
you know that, but Access will not be "willing" to make this assumption
about a table!) You would either need to reference the value from a
form control, as suggested by Tina, or else use a domain function, for
example DLookup("[MakeNr]","Test MakeNr table")

--
Steve Schapel, Microsoft Access MVP
I have set up a control table and move the quantity to
make into a field named "MakeNr." Have housekeeping
queries to decrement MakeNr by 1 each time the macro
runs. Repeat expression used: [Test MakeNr table]!
[MakeNr]>0. This tests True until MakeNr reaches 0 then
False. All queries test okay but macro just won't run.
Have also set up field to check and everything, the
decrementing and True/False seem okay. Is there
something wrong with my expression syntax? I have written
several systems in Access but still learning. Thanks
again. Jim
Click to expand...
Click to expand...
 
Sure, Tina. I realised you were suggesting a good approach, which
by-passes the table idea, which is why I didn't want to interfere with
what you were doing here. I just wanted to help Jim understand why his
original approach was failing :-)
 
Tina - Thanks for the info. To answer: Yes, every step
tested and works. The macro just doesn't run, not even
once, which leads me to believe it isn't reading the
expression as, "True." Not getting any stop/halt box. I
am calling the macro within a marco. The queries before
the called macro have set up my control table with a
value in a the field set to the number of times I want
the macro to run. Within the called macro I have a
routines to perform the function I want then to update
the control field by -1. Leaving the Repeat Expression
blank and using the Repeat Count Action Augments to
control the number of time it runs indicates the control
table updates correctly and all functions perform as I
wanted. I understand about the command button but that
would defeat the automation I am attempting to do. Also,
I cannot get the Repeat Count augment field to accept
anything other than a number. Thanks much for your time
and advice. I see "Steve" has some ideas also. Have nice
holiday. This is a real learning experience!
-----Original Message-----
have you run the macro on a "step" thru? where is it breaking? is it running
once and then stopping? are you getting a failed macro box showing the
failed step? what process are you using to de-increment the table value? a
note there: instead of putting a value in a table, and de-incrementing it,
it might be a lot easier to set up a hidden, unbound control on a form. set
the value of the control in the macro, then set the Repeat Count to

=[Forms]![MyForm]![MyTextBox].[Value]

leave Repeat Expression blank.
run the macro from a command button on the form.

hth


I have set up a control table and move the quantity to
make into a field named "MakeNr." Have housekeeping
queries to decrement MakeNr by 1 each time the macro
runs. Repeat expression used: [Test MakeNr table]!
[MakeNr]>0. This tests True until MakeNr reaches 0 then
False. All queries test okay but macro just won't run.
Have also set up field to check and everything, the
decrementing and True/False seem okay. Is there
something wrong with my expression syntax? I have written
several systems in Access but still learning. Thanks
again. Jim
-----Original Message-----
please post the expression you're using, and any
Click to expand...
details
connected with it.
I am attempting to use the RunMacro cmd to append records
to a table a specified number of times. Everything works
except the "Repeat Expression." Using the "Count" works
but I can't get ANY expression to let it run.
Click to expand...
Anyone
got
any ideas? Samples of expressions comparing two fields?
Thanks.


.
Click to expand...
Click to expand...


.
Click to expand...
 
the macro doesn't have to run from a command button. that was just a
suggestion, based on not knowing the context of your setup.
The queries before
the called macro have set up my control table with a
value in a the field set to the number of times I want
the macro to run.
Click to expand...
if you're satisfied with the table approach, then my next question is: is
the macro being called, directly or indirectly, from an open form? if so,
suggest the following:
don't try to "de-increment" the table value. instead, use an unbound text
box as i described in my previous post - and set the control source of the
text box to the following
=DLookup("[MyNumberField]", "MyTableName")
the above assumes that there's only one record in the table where you're
saving the "number of macro repeats" number.
it's tricky to get the macro's Repeat Count to accept a form reference. but
i've successfully tested the reference i posted before, so it should work
for you.

hth


Jim said:
Tina - Thanks for the info. To answer: Yes, every step
tested and works. The macro just doesn't run, not even
once, which leads me to believe it isn't reading the
expression as, "True." Not getting any stop/halt box. I
am calling the macro within a marco. The queries before
the called macro have set up my control table with a
value in a the field set to the number of times I want
the macro to run. Within the called macro I have a
routines to perform the function I want then to update
the control field by -1. Leaving the Repeat Expression
blank and using the Repeat Count Action Augments to
control the number of time it runs indicates the control
table updates correctly and all functions perform as I
wanted. I understand about the command button but that
would defeat the automation I am attempting to do. Also,
I cannot get the Repeat Count augment field to accept
anything other than a number. Thanks much for your time
and advice. I see "Steve" has some ideas also. Have nice
holiday. This is a real learning experience!
-----Original Message-----
have you run the macro on a "step" thru? where is it breaking? is it running
once and then stopping? are you getting a failed macro box showing the
failed step? what process are you using to de-increment the table value? a
note there: instead of putting a value in a table, and de-incrementing it,
it might be a lot easier to set up a hidden, unbound control on a form. set
the value of the control in the macro, then set the Repeat Count to

=[Forms]![MyForm]![MyTextBox].[Value]

leave Repeat Expression blank.
run the macro from a command button on the form.

hth


I have set up a control table and move the quantity to
make into a field named "MakeNr." Have housekeeping
queries to decrement MakeNr by 1 each time the macro
runs. Repeat expression used: [Test MakeNr table]!
[MakeNr]>0. This tests True until MakeNr reaches 0 then
False. All queries test okay but macro just won't run.
Have also set up field to check and everything, the
decrementing and True/False seem okay. Is there
something wrong with my expression syntax? I have written
several systems in Access but still learning. Thanks
again. Jim
-----Original Message-----
please post the expression you're using, and any details
connected with it.


message
I am attempting to use the RunMacro cmd to append
records
to a table a specified number of times. Everything
works
except the "Repeat Expression." Using the "Count"
works
but I can't get ANY expression to let it run. Anyone
got
any ideas? Samples of expressions comparing two
fields?
Thanks.


.
Click to expand...


.
Click to expand...
Click to expand...
 
Steve - Thanks. It worked GREAT! I put the Dlookup
command in the Repeat Expression action augment, set the
field to desired times, performed routines I wanted while
decremented the field by 1 each time it ran. Macro
stopped when field reached 0. This allowed me to use the
Count augment as a safety limit. Jim.
-----Original Message-----
PMFJI here Jim. You are getting excellent advice from Tina, so go with
that. But I feel sure that the repeat expression
[Test MakeNr table]![MakeNr]>0
will not work. This is incorrect syntax, and will make no sense at all
to Access. If you are trying to refer to the value of the MakeNr field
in the Test MakeNr table table, well, even if Access gave you the
ability to refer to this value, you would still need to indicate which
record in the table (even if there is only one record in the table...
you know that, but Access will not be "willing" to make this assumption
about a table!) You would either need to reference the value from a
form control, as suggested by Tina, or else use a domain function, for
example DLookup("[MakeNr]","Test MakeNr table")

--
Steve Schapel, Microsoft Access MVP
I have set up a control table and move the quantity to
make into a field named "MakeNr." Have housekeeping
queries to decrement MakeNr by 1 each time the macro
runs. Repeat expression used: [Test MakeNr table]!
[MakeNr]>0. This tests True until MakeNr reaches 0 then
False. All queries test okay but macro just won't run.
Have also set up field to check and everything, the
decrementing and True/False seem okay. Is there
something wrong with my expression syntax? I have written
several systems in Access but still learning. Thanks
again. Jim
Click to expand...
.
Click to expand...
 
Back
Top