Count of selected records on form

[Pendragon]

Access03/WinXP

I saw the post from Paul Sator and the response from Graham Mandeno -
unfortunately, adding a checkbox or some other identifier isn't going to help
because I need to have this in the OnDelete property (I think!).

I am revising a form and subform and want to know if in VBA there is a way
to reference the number of records selected on a subform.

The subform has an Amount Due field and the parent form has a Grand Total
field. I would like to update the Grand Total field if subform records are
deleted.

Currently, I am using a recordset ( rs = select * from MyTable where
MyTable.MyID = MyVariable ) and performing the updated calculation on the
grand total field. This works fine if only one record is selected and
deleted; unfortunately, if more than one record is selected, rs.RecordCount
shows a count of 1 and thus the grand total is adjusted only by the amount of
the first record. I would like to run a summation on the Amount Due fields
of the selected records and adjust Grand Total accordingly.

Is there a way to reference the number of records selected on a form? Better
ideas?

Thanks in advance.

 

[tina]

it's not clear what you're doing. does the calculated value in the parent
form include a total value of some field in the existing subform records? if
so, how are you getting that total value of the subform records? the usual
way is to add a calculated control to the subform's Footer section, which
uses an expression to calculate the total of one or more fields in one or
more subform records. then the parent form calculation just references that
calculated control in the subform - updating is automatic for both
calculated controls (in the subform and in the parent form), not requiring
code.

hth

 

[John W. Vinson]

The subform has an Amount Due field and the parent form has a Grand Total
field. I would like to update the Grand Total field if subform records are
deleted.

Well, the standard advice would be to not store the Grand Total field in any
table, anyhow; just recalculate it as needed.

If you really want to do it the hard way, and risk data integrity problems
(e.g. an editable Grand Total field which can be changed to a number different
from the real grand total, other ways that the underlying table could be
edited, etc.) consider using the subform's AfterDeleteConfirm event.

John W. Vinson [MVP]