Subform recalc issue

  • Thread starter Thread starter Laura McKittrick
  • Start date Start date
L

Laura McKittrick

Wonder if someone can help with this...

MS Access 2002
Main form has a Total Requests field
Subform allows any number of detail records with Requests Fulfilled
Total of Requests Fulfilled displays on the main form
Total of Requests Fulfilled must NOT be more than Total Requests

This is working fine until I get to the last point, but I'm having
trouble setting up the data validation for that item. I can do
something like this: if me.fldTotalRequests <
me.txtTotRequestsFulfilled then... (Adjust the syntax depending on
whether I'm working in the parent form or subform.)

The problem is there's a delay in when the totals get updated. I have
to move to another record or some such thing to get them to display
correctly. Putting me.recalc in the fldRequestsFulfilled's
AfterUpdate event solves this problem. But then when I do the data
validation in the subform's BeforeUpdate event, I get an error
complaining that the Recalc is preventing it from saving the data.
And when I try it in the main form's BeforeUpdate event, the subform
is no longer dirty. If the only thing that has changed is a detail
record, the data will get saved without the test being run.

I suppose I could mess around with flags to tell the main form when
the subform has changed. But seems there ought to be a way to use
either the main form's or the subform's BeforeUpdate event, which
would be simpler and less prone to break.

Any ideas?

Thanks in advance!

Laura
 
Try a "Requery" of your main form in your AfterUpdate
event rather than a "Recalc." Recalc does not look at
changes to the underlying recordset. A Requery should
provide the correct values in the main form without moving
to and back from another record.

HTH,

TK
 
ReQuery gave me the same error as ReCalc, or something similar.

What I'm doing now is using the subform's fldRequestsFulfilled
AfterUpdate event to do a ReCalc and then set a public flag to tell
the main form that the subform total has changed. That part is
working fine. What's giving me fits is preventing the user from
moving to another parent record when only the subform has changed,
because the main form isn't dirty. I'm trying to use the main form's
Current event, but having problems getting it to work just right with
my Close button and the BeforeUpdate event. The irritating thing is
the Current event doesn't have a Cancel property, and there doesn't
seem to be anything else that I could use.

Laura
 
Back
Top