1st time macro user

[Kate]

Hi,
I have a form and a subform. Every time the component gets issued to a
job, this is recorded in the subform. ie, today 4 of this component
might be issued, and tomorrow another 2 might be issued. I want the
main form to keep a running sum of the total issued. At the moment,
you have to count the no. issued in the subform and manually type this
in to the total issued on the main form. Instead, I want it to update
automatically.

I wanted to write a macro to set "Quantity Issued" on the main form to
the sum of the "QuantityIssued" on the subform. But alot of the
expressions you can use in queries, you dont seem to be able to use in
macros.

Can anyone help with a way to do this?

Cheers

 

[Dirk Goldgar]

Hi,
I have a form and a subform. Every time the component gets issued to
a job, this is recorded in the subform. ie, today 4 of this component
might be issued, and tomorrow another 2 might be issued. I want the
main form to keep a running sum of the total issued. At the moment,
you have to count the no. issued in the subform and manually type this
in to the total issued on the main form. Instead, I want it to update
automatically.

I wanted to write a macro to set "Quantity Issued" on the main form to
the sum of the "QuantityIssued" on the subform. But alot of the
expressions you can use in queries, you dont seem to be able to use in
macros.

Can anyone help with a way to do this?

If I understand you correctly (and of course, I may not), you don't
actually need to store the total issued anywhere, because it's always
calculable from the total of the related subform records. Therefore,
you shouldn't need to update the main-form total at all, neither
manually nor by macro or code, because it's a calculated value that
Access can generate and for you.

Here's how I think I would do it. I'd put a text box in the Form Footer
or Form Header section of the subform, and set the ControlSource of that
text box to

=Sum([QuantityIssued])

You could call the text box "TotalIssued". If the subform doesn't
already have a Form Footer/Header section to put this text box on, I'd
create one.

Maybe just having that TotalIssued text box on the subform would be
enough to serve your purposes. If not, you could put a text box on the
main form that gets its value directly from the text box on the subform.
It would have a controlsource property like this:

=[YourSubformName].[Form]![TotalIssued]

If you don't want to show the text box on the subform, but only on the
main form, you can set the text box (on the subform) to be invisible, or
even set the whole section containing it to be invisible. Both the text
box and the section have a Visible property that can be set to No.

 

[Kate]

This is good for getting the running sum to show on the main form, but
how do i then get it to show on the related report since its then a
calculated control?