recalc subform of a subform not working

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that includes a text box, Multiplier. When the user changes
the value of the multiplier, I have an AfterUpdate event that updates the
values of a table, Totals.

There is a subform within this form. Then another subform within that one.
The second subform is datasheet view form of the Totals table.

In the AfterUpdate event, after updating the values in the table, I use:
Forms!frmProposal![frmSummary subform].Form![frmResourceCodeTotals
subform].Form.Recalc
Forms!frmProposal![frmSummary subform].Form.Recalc
Forms!frmProposal.Form.Recalc

The values are not being updated. But if I retype the new multiplier and
hit enter, it does update the values. It does work with requery, but its not
user-friendly that the focus goes to the first control on the form.

Any suggestions would be much appreciated.
Rob
 
Use Requery, and just add a few steps of code to move the subform back to
the record that it was on.

Dim varPK As Variant
With Forms!frmProposal![frmSummary subform].Form![frmResourceCodeTotals
subform].Form
varPK = .Fields("NameOfPKField").Value
.Requery
.RecordsetClone.FindFirst "NameOfPKField=" & varPK
If .RecordsetClone.NoMatch = False Then _
.Bookmark = .RecordsetClone.Bookmark
End With
With Forms!frmProposal![frmSummary subform].Form
varPK = .Fields("NameOfPKField").Value
.Requery
.RecordsetClone.FindFirst "NameOfPKField=" & varPK
If .RecordsetClone.NoMatch = False Then _
.Bookmark = .RecordsetClone.Bookmark
End With
With With Forms!frmProposal.Form
varPK = .Fields("NameOfPKField").Value
.Requery
.RecordsetClone.FindFirst "NameOfPKField=" & varPK
If .RecordsetClone.NoMatch = False Then _
.Bookmark = .RecordsetClone.Bookmark
End With
 
Thanks Ken. It turns out I just needed to save the record just prior to
Recalc and it works fine.
Thanks,

BTW,
Does it make sense that the AfterUpdate event on a text box whose data
source is field in a table, occurs prior to the record being updated? In
other words, in the AfterUpdate event on the text box, I run a query on the
underlying table and it doesn't see the new value in the text box.

Thanks again

Ken Snell said:
Use Requery, and just add a few steps of code to move the subform back to
the record that it was on.

Dim varPK As Variant
With Forms!frmProposal![frmSummary subform].Form![frmResourceCodeTotals
subform].Form
varPK = .Fields("NameOfPKField").Value
.Requery
.RecordsetClone.FindFirst "NameOfPKField=" & varPK
If .RecordsetClone.NoMatch = False Then _
.Bookmark = .RecordsetClone.Bookmark
End With
With Forms!frmProposal![frmSummary subform].Form
varPK = .Fields("NameOfPKField").Value
.Requery
.RecordsetClone.FindFirst "NameOfPKField=" & varPK
If .RecordsetClone.NoMatch = False Then _
.Bookmark = .RecordsetClone.Bookmark
End With
With With Forms!frmProposal.Form
varPK = .Fields("NameOfPKField").Value
.Requery
.RecordsetClone.FindFirst "NameOfPKField=" & varPK
If .RecordsetClone.NoMatch = False Then _
.Bookmark = .RecordsetClone.Bookmark
End With

--

Ken Snell
<MS ACCESS MVP>


Rob LMS said:
I have a form that includes a text box, Multiplier. When the user changes
the value of the multiplier, I have an AfterUpdate event that updates the
values of a table, Totals.

There is a subform within this form. Then another subform within that
one.
The second subform is datasheet view form of the Totals table.

In the AfterUpdate event, after updating the values in the table, I use:
Forms!frmProposal![frmSummary subform].Form![frmResourceCodeTotals
subform].Form.Recalc
Forms!frmProposal![frmSummary subform].Form.Recalc
Forms!frmProposal.Form.Recalc

The values are not being updated. But if I retype the new multiplier and
hit enter, it does update the values. It does work with requery, but its
not
user-friendly that the focus goes to the first control on the form.

Any suggestions would be much appreciated.
Rob
Click to expand...
Click to expand...
 
Thanks Ken. I actually got the recalc method to work by saving the record
just prior to recalc.
Rob

Ken Snell said:
Use Requery, and just add a few steps of code to move the subform back to
the record that it was on.

Dim varPK As Variant
With Forms!frmProposal![frmSummary subform].Form![frmResourceCodeTotals
subform].Form
varPK = .Fields("NameOfPKField").Value
.Requery
.RecordsetClone.FindFirst "NameOfPKField=" & varPK
If .RecordsetClone.NoMatch = False Then _
.Bookmark = .RecordsetClone.Bookmark
End With
With Forms!frmProposal![frmSummary subform].Form
varPK = .Fields("NameOfPKField").Value
.Requery
.RecordsetClone.FindFirst "NameOfPKField=" & varPK
If .RecordsetClone.NoMatch = False Then _
.Bookmark = .RecordsetClone.Bookmark
End With
With With Forms!frmProposal.Form
varPK = .Fields("NameOfPKField").Value
.Requery
.RecordsetClone.FindFirst "NameOfPKField=" & varPK
If .RecordsetClone.NoMatch = False Then _
.Bookmark = .RecordsetClone.Bookmark
End With

--

Ken Snell
<MS ACCESS MVP>


Rob LMS said:
I have a form that includes a text box, Multiplier. When the user changes
the value of the multiplier, I have an AfterUpdate event that updates the
values of a table, Totals.

There is a subform within this form. Then another subform within that
one.
The second subform is datasheet view form of the Totals table.

In the AfterUpdate event, after updating the values in the table, I use:
Forms!frmProposal![frmSummary subform].Form![frmResourceCodeTotals
subform].Form.Recalc
Forms!frmProposal![frmSummary subform].Form.Recalc
Forms!frmProposal.Form.Recalc

The values are not being updated. But if I retype the new multiplier and
hit enter, it does update the values. It does work with requery, but its
not
user-friendly that the focus goes to the first control on the form.

Any suggestions would be much appreciated.
Rob
Click to expand...
Click to expand...
 
Answer inline...

--

Ken Snell
<MS ACCESS MVP>

Rob LMS said:
Thanks Ken. It turns out I just needed to save the record just prior to
Recalc and it works fine.
Thanks,

BTW,
Does it make sense that the AfterUpdate event on a text box whose data
source is field in a table, occurs prior to the record being updated? In
other words, in the AfterUpdate event on the text box, I run a query on
the
underlying table and it doesn't see the new value in the text box.
Click to expand...

Until the form has actually saved its data to the underlying table, any
"query" of the data in that table will show only the data that were last
saved to that table. In fact, if you were to check the form's Recordset or
RecordsetClone in the textbox's AfterUpdate event, you'll also see that they
too contain the "old" data; they and the table are not updated until the
form's AfterUpdate event occurs.

A textbox's AfterUpdate event occurs after the textbox's data have been
"changed", but this event has no bearing on the form's BeforeUpdate and
AfterUpdate events.
 
Back
Top