Subform total on main form

[Geraint]

OK I know this should be really easy but I can't get it
right.

I have a form for invoices that has a subform showing
invoice items with a total field in the subform footer,
so far so good.

I then have another form showing unpaid invoices that is
a continuous form of invoices based on a query that
brings together invoice numbers and totals for the
invoice items to give "invoice totals" that you should be
able to click on to say that a given invoice is paid.
However I get message that Recordset is not Updateable.
This I now know is because the query contains totals
fields.

OK so what I need is the subform total field from the
invoice form to be copied to the main form and recorded
in a field so that I can just have a straight forward
query based on the one table that is updateable. I can
get an equation to show it on the main form in an unbound
textbox but can not see how to get it to update a bound
field.

Any help much appreciated.

Geraint

 

[Bruce M. Thompson]

OK so what I need is the subform total field from the

invoice form to be copied to the main form and recorded
in a field so that I can just have a straight forward
query based on the one table that is updateable. I can
get an equation to show it on the main form in an unbound
textbox but can not see how to get it to update a bound
field.

I wouldn't save it to any field. Use a calculated field (as in your unbound
textbox) everywhere that you need to retrieve the value. That way it will always
be up-to-date regardless of how the source data might be updated/manipulated.

 

[Geraint]

Thanks for input Bruce.

OK but I don't see how I reference this unbound field on
my second continuous form list (when I can't have a
subform)? Or am I missing the point completely?

Geraint

 

[Bruce M. Thompson]

OK but I don't see how I reference this unbound field on

my second continuous form list (when I can't have a
subform)? Or am I missing the point completely?

Are you trying to use this "Unpaid Invoices" form to enter Invoice payments or
to mark an Invoice as Paid? If you are intending to use this form to mark
Invoices as paid, I would consider a command button behind which I would open a
recordset for the purpose of setting the "Paid" field's value, then refresh the
form. Make sure that you have a calculated control that will display the "Paid"
field's value so you will know, while that form is open, which Invoices you have
already marked as paid. Normally, I would just place a combo box on the main
Invoice form (or a "Payments" form dedicated to receiving payments) to allow
navigation to unpaid invoices so that I could enter related payments in a
"Payments" subform and set the Invoice's "Paid" field's value as appropriate
when exiting the "Payments" subform.

 

[Geraint]

I'm using the form to mark invoices as paid. I think the
command button route is going to be the best to achieve
what I'm after. Thanks for your help.

Geraint