Probelm refreshing calcutated field on main form based on subform

[Willam Roberts]

I have [Total] field on my main form [Invoices]. This [Total] field displays
the Sum of the line-items in the Subform [InvoiceDetails] by referencing an
invisible calculated Field[detTotal] in the footer of that Subform. Pretty
straight forward stuff. This is my dilemma , I want to the user to be able
to make changes to the [Quantity] and/or the [UnitPrice] and have the new
[Total] displayed immediately on the main form after leaving either of these
two fields. I was successfully able to accomplish this by simply placing a
'me.refresh' statement in the AfterUpdate event on both the [Quantity] &
[UnitPrice] field.

The PROBLEM is that in the BeforeUpdate Event for the subform
[InvoiceDetails], I have a routine that detects if the form is dirty and
prompts the user to Yes/No/Cancel before moving off the record. I cannot
seem to update the [Total] field without prematurely triggering the
BeforeUpdate event in the subform thus annoying the user to confirm every
change when they are not necessarily moving off that record. I have tried
every thing I could think of, i.e. subqueries, subforms, accessing an
invisible [detTotal] field in the subform. They all work but present the
same problem. Any Ideas?

Regards

William Roberts

 

[Wayne Morgan]

In the AfterUpdate event of the 2 subform controls try

Me.Parent.Recalc

The Me.Refresh is telling the subform to go back to its record source and
look for changes to current records. To do this, it wants to commit the
dirty record first. Recalculating the parent (main) form hopefully won't
cause this problem. However, since the values aren't being stored yet, you
may not get the update you want either. Give it a try and see what happens.
If it doesn't work, what are the Control Sources for the main form control
and the calculated control on the subform that it refers to?

 

[Willam Roberts]

Wayne
Thanks, you were right, recalc did not trigger update. But also did not
update control. Here is info you asked about.

txt_detTotal in subform footer
refers to txt_UnitPrice and txt_Quantity in Details section of
subform[InvoiceDetails]
Syntax: ControlSource =Sum([UnitPrice]*[Quantity])

txt_Total In Main Form
Syntax: ControlSource ==[InvoiceDetailsSubform]!txt_detTotal

In the AfterUpdate event of the 2 subform controls try

Me.Parent.Recalc

The Me.Refresh is telling the subform to go back to its record source and
look for changes to current records. To do this, it wants to commit the
dirty record first. Recalculating the parent (main) form hopefully won't
cause this problem. However, since the values aren't being stored yet, you
may not get the update you want either. Give it a try and see what happens.
If it doesn't work, what are the Control Sources for the main form control
and the calculated control on the subform that it refers to?

--
Wayne Morgan
Microsoft Access MVP

I have [Total] field on my main form [Invoices]. This [Total] field displays
the Sum of the line-items in the Subform [InvoiceDetails] by referencing an
invisible calculated Field[detTotal] in the footer of that Subform. Pretty
straight forward stuff. This is my dilemma , I want to the user to be able
to make changes to the [Quantity] and/or the [UnitPrice] and have the new
[Total] displayed immediately on the main form after leaving either of these
two fields. I was successfully able to accomplish this by simply placing a
'me.refresh' statement in the AfterUpdate event on both the [Quantity] &
[UnitPrice] field.

The PROBLEM is that in the BeforeUpdate Event for the subform
[InvoiceDetails], I have a routine that detects if the form is dirty and
prompts the user to Yes/No/Cancel before moving off the record. I cannot
seem to update the [Total] field without prematurely triggering the
BeforeUpdate event in the subform thus annoying the user to confirm every
change when they are not necessarily moving off that record. I have tried
every thing I could think of, i.e. subqueries, subforms, accessing an
invisible [detTotal] field in the subform. They all work but present the
same problem. Any Ideas?

Regards

William Roberts

 

[Wayne Morgan]

I did some playing around. Even without the main form/subform setup, this
doesn't work. I tried just recalculating the form that the data is on
(essentially your subform) and it saves the record when you do a Recalc of
that form, so your BeforeUpdate is still going to fire. What you are summing
is stored in the fields of the form's recordset, but the new data isn't
there until the record is saved. If the calculation only involved the
current record, this could be done, but since you are summing all the
records, the data needs to be updated first.

One thing I can think of would be to make a copy of the recordset, make the
changes to the copy via code in the AfterUpdate event of the two controls
and do your calculation on this copy, then place the result in the textbox.

Another option, may be to use the OldValue property of these controls, in
the AfterUpdate event of the controls, subtract the old value of the 2
multiplied together from the sum and add the new value. Place this in a
textbox that is over the total textbox on the main form. make this textbox
visible to display the new value. When the record is saved, hide this
textbox. I did a little testing with this and it appears to work and would
be much easier than the first idea.

--
Wayne Morgan
Microsoft Access MVP

Wayne
Thanks, you were right, recalc did not trigger update. But also did not
update control. Here is info you asked about.

txt_detTotal in subform footer
refers to txt_UnitPrice and txt_Quantity in Details section of
subform[InvoiceDetails]
Syntax: ControlSource =Sum([UnitPrice]*[Quantity])

txt_Total In Main Form
Syntax: ControlSource ==[InvoiceDetailsSubform]!txt_detTotal

In the AfterUpdate event of the 2 subform controls try

Me.Parent.Recalc

The Me.Refresh is telling the subform to go back to its record source and
look for changes to current records. To do this, it wants to commit the
dirty record first. Recalculating the parent (main) form hopefully won't
cause this problem. However, since the values aren't being stored yet, you
may not get the update you want either. Give it a try and see what happens.
If it doesn't work, what are the Control Sources for the main form control
and the calculated control on the subform that it refers to?

--
Wayne Morgan
Microsoft Access MVP

I have [Total] field on my main form [Invoices]. This [Total] field displays
the Sum of the line-items in the Subform [InvoiceDetails] by

referencing
an

invisible calculated Field[detTotal] in the footer of that Subform. Pretty
straight forward stuff. This is my dilemma , I want to the user to be able
to make changes to the [Quantity] and/or the [UnitPrice] and have the new
[Total] displayed immediately on the main form after leaving either of these
two fields. I was successfully able to accomplish this by simply

placing

a

'me.refresh' statement in the AfterUpdate event on both the [Quantity] &
[UnitPrice] field.

The PROBLEM is that in the BeforeUpdate Event for the subform
[InvoiceDetails], I have a routine that detects if the form is dirty and
prompts the user to Yes/No/Cancel before moving off the record. I cannot
seem to update the [Total] field without prematurely triggering the
BeforeUpdate event in the subform thus annoying the user to confirm every
change when they are not necessarily moving off that record. I have tried
every thing I could think of, i.e. subqueries, subforms, accessing an
invisible [detTotal] field in the subform. They all work but present the
same problem. Any Ideas?

Regards

William Roberts