Macro muddle, eliminate the nag

[Guest]

I’ve created a simple macro that uses the SetValue action to store a
calculated value in the underlying table of a form control.

It works, pushing the calculated value into the tablefield as necessary,
but with an admonitory error message popping up as I tab to different
controls in the form. I’d like to eliminate the nagging.

(I realize I’ve waded into design-discussion-territory here by storing
calculated values in a table, i.e., wasting space and compromising historical
data, but I’ve decided to go ahead anyway with one calculated field in one
small table of the db.)

Here’s the macro:

- - -MacroName- - - -Action-
macSHCalculatedTotal SetValue

SetValue
Item: [SHCashPurchaseTotal]
Expression: CCur([SHProductUnitPrice]*[SHQuantityPurchased])


I’ve set the OnCurrent property of the form to macSHCalculatedTotal, along
with the AfterUpdate properties of the SHProductUnitPrice and
SHQuantityPurchased bound controls.

The form opens fine, but when I go to append a new record I get an “Invalid
use of Null†diagnostic, upon which I close the nag, oops, diagnostic,
window, and an “Action Failed†diagnostic on my macro appears, its Condition
showing as True, and only the “Halt†button available. Clicking “Halt†allows
me to proceed choosing a product id in my form, and key a number in the
Quantity field, but when I click the unit price field to fill it in, Null nag
neighs at me again. (Sorry, couldn’t resist…). Close the nag and Halt the
“Action Failed†diagnostic a second time, and the calculated total finally
appears. Checking the underlying table shows it’s being updated correctly.

I’m suspecting Access doesn’t like trying to execute the macro on the blank
fields in a new record, but what’s the solution?

 

[Steve Schapel]

Katsup,

Why are you running this macro on the Current event of the form? On the
basis of what you have told us so far, this doesn't make sense.

Also, I would not put this macro on any event associated with the fields
used in the calculation itself. I think I would use the Before Update
event of the form.

Also, I don't think the CCur() function here can be achieving anything
useful. I would suggest you remove that.

Having said that, I probably would not use a SetValue macro action in
this context. I probably would set up an Update Query, to update the
value of the SHCashPurchaseTotal field for the current record, and then
use an OpenQuery action in my macro, on the form's After Update event,
to run the update.

 

[Guest]

Steve, thanks for your reply.

I originally set up the macro following instructions in Article 209172 from
support.microsoft.com. I’m new enough that I’m still blundering about
through the myriad of objects and properties in Access, and I wasn’t
knowledgeable enough to do anything but follow the article blindly.

Thanks to your answer, I now know what an update query is (didn’t know the
critters existed before), and have created one which is called by an
OpenQuery macro action, in the form’s After Update event, as you suggested.
It works.

I notice there was one advantage to using SetValue as per the article
though; the calculated total cost was displayed on the screen as soon as
UnitPrice and QuantityPurchased had been filled in. By contrast, using the
AfterUpdate property leaves the field’s default of $0.00 displaying in the
form’s CashPurchaseTotal field, until control passes out of the record. Am I
correct in guessing that I can just replace the CashPurchaseTotal field with
an unbound control (on the bound form) and set its control source to a
duplicate of the calculation - temporarily displaying, in other words, the
calculated value that the form macro will push into the table when the record
is closed?

Access still complains as control is passing out of the record, but not as
much: “You are about to run an update query that will modify data in your
tableâ€, and “You are about to update row #â€. This is easier to live with for
the time being than all the previous nagging however, and I’ll see if I can
understand the Help File's suggestions for eliminating the warnings.

Checking back on the record afterwards shows that the calculated value was
successfully pushed into the table, so I’m satisfied with that.

Katsup,

Why are you running this macro on the Current event of the form? On the
basis of what you have told us so far, this doesn't make sense.

Also, I would not put this macro on any event associated with the fields
used in the calculation itself. I think I would use the Before Update
event of the form.

Also, I don't think the CCur() function here can be achieving anything
useful. I would suggest you remove that.

Having said that, I probably would not use a SetValue macro action in
this context. I probably would set up an Update Query, to update the
value of the SHCashPurchaseTotal field for the current record, and then
use an OpenQuery action in my macro, on the form's After Update event,
to run the update.

--
Steve Schapel, Microsoft Access MVP

I’ve created a simple macro that uses the SetValue action to store a
calculated value in the underlying table of a form control.

It works, pushing the calculated value into the tablefield as necessary,
but with an admonitory error message popping up as I tab to different
controls in the form. I’d like to eliminate the nagging.

(I realize I’ve waded into design-discussion-territory here by storing
calculated values in a table, i.e., wasting space and compromising historical
data, but I’ve decided to go ahead anyway with one calculated field in one
small table of the db.)

Here’s the macro:

- - -MacroName- - - -Action-
macSHCalculatedTotal SetValue

SetValue
Item: [SHCashPurchaseTotal]
Expression: CCur([SHProductUnitPrice]*[SHQuantityPurchased])


I’ve set the OnCurrent property of the form to macSHCalculatedTotal, along
with the AfterUpdate properties of the SHProductUnitPrice and
SHQuantityPurchased bound controls.

The form opens fine, but when I go to append a new record I get an “Invalid
use of Null†diagnostic, upon which I close the nag, oops, diagnostic,
window, and an “Action Failed†diagnostic on my macro appears, its Condition
showing as True, and only the “Halt†button available. Clicking “Halt†allows
me to proceed choosing a product id in my form, and key a number in the
Quantity field, but when I click the unit price field to fill it in, Null nag
neighs at me again. (Sorry, couldn’t resist…). Close the nag and Halt the
“Action Failed†diagnostic a second time, and the calculated total finally
appears. Checking the underlying table shows it’s being updated correctly.

I’m suspecting Access doesn’t like trying to execute the macro on the blank
fields in a new record, but what’s the solution?

 

[Steve Schapel]

Katsup,

See comments inline...

... Am I
correct in guessing that I can just replace the CashPurchaseTotal field with
an unbound control (on the bound form) and set its control source to a
duplicate of the calculation - temporarily displaying, in other words, the
calculated value that the form macro will push into the table when the record
is closed?

Yes, that is correct, and not a bad solution. Otherwise, in
contradiction to what I told you before (which I did to keep it simple
), you could use the After Update event of the [SHProductUnitPrice]
*or* [SHQuantityPurchased] control on the form (not both - whichever one
is normally entered second). But if you do this, you will need to use a
macro condition to check that there is already a value entered in the
first of the 2 controls, and you will have to use a SaveRecord action in
the macro first, and you will have to put a Requery action in the macro
after the OpenQuery.

Access still complains as control is passing out of the record, but not as
much: “You are about to run an update query that will modify data in your
table”, and “You are about to update row #”. This is easier to live with for
the time being than all the previous nagging however, and I’ll see if I can
understand the Help File's suggestions for eliminating the warnings.

Just put a SetWarnings/No action in the macro before the OpenQuery action.

Checking back on the record afterwards shows that the calculated value was
successfully pushed into the table, so I’m satisfied with that.

Well, satisfaction is our aim

 

[Guest]

Thanks, Steve, simple is good for this old noggin’. I’m happy with little
victories slowly turning into more knowledge. Otherwise I get discouraged,
and am tempted to throw in the towel.

I chose the easier route, with SetWarnings/No in the OpenQuery macro, and
presumed that I should SetWarnings/Yes at its end. The nags are completely
laid to rest, I’m happy to report.

The unsimple fix intrigued me however. Using the AfterUpdate events seem
intuitively right; would it be the HasData property that I’d use to check if
values have been entered in the 2 multiplicands?

But why would I want to save the record first, before checking for data in
the multiplicands? And what would a requery do

Katsup,

See comments inline...

... Am I
correct in guessing that I can just replace the CashPurchaseTotal field with
an unbound control (on the bound form) and set its control source to a
duplicate of the calculation - temporarily displaying, in other words, the
calculated value that the form macro will push into the table when the record
is closed?

Yes, that is correct, and not a bad solution. Otherwise, in
contradiction to what I told you before (which I did to keep it simple
), you could use the After Update event of the [SHProductUnitPrice]
*or* [SHQuantityPurchased] control on the form (not both - whichever one
is normally entered second). But if you do this, you will need to use a
macro condition to check that there is already a value entered in the
first of the 2 controls, and you will have to use a SaveRecord action in
the macro first, and you will have to put a Requery action in the macro
after the OpenQuery.

Access still complains as control is passing out of the record, but not as
much: “You are about to run an update query that will modify data in your
tableâ€, and “You are about to update row #â€. This is easier to live with for
the time being than all the previous nagging however, and I’ll see if I can
understand the Help File's suggestions for eliminating the warnings.

Just put a SetWarnings/No action in the macro before the OpenQuery action.

Checking back on the record afterwards shows that the calculated value was
successfully pushed into the table, so I’m satisfied with that.

Well, satisfaction is our aim

 

[Steve Schapel]

Katsup,

I chose the easier route, with SetWarnings/No in the OpenQuery macro, and
presumed that I should SetWarnings/Yes at its end.

Good thought, but not necessary, in fact. This is automatically reset
when the macro finishes anyway.

The unsimple fix intrigued me however. Using the AfterUpdate events seem
intuitively right; would it be the HasData property that I’d use to check if
values have been entered in the 2 multiplicands?

No, you would need a Condition something like...
[SHProductUnitPrice] Is Not Null

But why would I want to save the record first, before checking for data in
the multiplicands?

The Update Query will only work if the record exists already, and the
record doesn't exist until you save it.

And what would a requery do

After running the Update Query, the results of the update (i.e. the
newly entered SHCashPurchaseTotal) will not be automatically shown on
the screen, until you Requery the form.