Subform updating parent form

[Guest]

Is it possible for a field on a subform to update a field on the parent form that's tied to a table field

The sample Northwind Orders form does something similar to what I want to do: when you add items on the subform, the subtotal on the main form changes. But the subtotal field on the main form is a calculated field, it's not tied to a table field. Would it be possible to update the subtotal field programatically whenever the subform is added to or updated if the control source is an actual table field

Thanks
Rich

 

[Dirk Goldgar]

Is it possible for a field on a subform to update a field on the
parent form that's tied to a table field?

The sample Northwind Orders form does something similar to what I
want to do: when you add items on the subform, the subtotal on the
main form changes. But the subtotal field on the main form is a
calculated field, it's not tied to a table field. Would it be
possible to update the subtotal field programatically whenever the
subform is added to or updated if the control source is an actual
table field?

Thanks,
Rich

It's possible, using a little VBA code, but it may not be a good idea.
Almost certainly if the field on the parent form is a subtotal or
similar value that can always be calculated from the related subform
records, then that calculated-and-calculable value should not be saved
in the main form's table. Saving calculated values is a good way to end
up with mismatched data, where the calculated field doesn't accurately
reflect the current values of the fields on which it's based.

Anyway, if you have a good reason to do this, the code is pretty simple.
In a suitable event on the subform (or more than one if necessary), you
execute a line of code like this:

Me.Parent!ControlOnParentForm = Me!ControlOnSubform

The trick is to pick the right event(s) so that the parent control
always gets updated when it should.

Again, I wouldn't recommend this unless you have a very good reason.

 

[Guest]

Thanks, Dirk! I tried your code and it worked as described. The key does seem to be finding the right event: I've currently got it tied to the subform's "On Dirty" event, but even that's not working exactly as I'd like: the main form's field is only getting updated after the subform's next record has actually been modified, whereas I'd like it to be updated when the next record is simply "prepared," i.e., when you tab into it but before you enter any data. That's the way the Northwind example works, which makes me think that the recalculation of calculated fields happens automatically at that point, and I just need to find out what event the recalculation is tied to

I should add that I agree with your caution about including the field in the table at all. My problem is that I'm converting an existing DOS-based Paradox application with a pre-defined structure, and I don't know yet whether or not the client will agree to changing the structure of the database. If he's amenable to it, I'll probably suggest that it would be a good idea

Thanks again
Rich

 

[Dirk Goldgar]

Thanks, Dirk! I tried your code and it worked as described. The key
does seem to be finding the right event: I've currently got it tied
to the subform's "On Dirty" event, but even that's not working
exactly as I'd like: the main form's field is only getting updated
after the subform's next record has actually been modified, whereas
I'd like it to be updated when the next record is simply "prepared,"
i.e., when you tab into it but before you enter any data. That's the
way the Northwind example works, which makes me think that the
recalculation of calculated fields happens automatically at that
point, and I just need to find out what event the recalculation is
tied to.

I should add that I agree with your caution about including the field
in the table at all. My problem is that I'm converting an existing
DOS-based Paradox application with a pre-defined structure, and I
don't know yet whether or not the client will agree to changing the
structure of the database. If he's amenable to it, I'll probably
suggest that it would be a good idea.

I think it would depend on what it is that is actually to be stored. If
it's some sort of total of the subform records, then you may need to use
the subform's AfterUpdate and AfterDelConfirm events. If it's something
else, then it may be the Current event, the AfterUpdate event, or some
other event that I can't think of without more information. If you'd
like to explain in more detail, maybe I could make a better suggestion.