when do controls in form footers get updated

[ryetee]

I'm looking at a form that has a sub form and in the sub form there is a total of one of the fields (ie =sum(field1)) Let's call this sumcontrol1

If any of one of these field1 fields is updated and the cursor put on to any other row then sum(field1) gets updated automatically.

Now this form has a button on the main form. When this is clicked an unbound control (let's call it mainformsum) on the main form is set to sumcontrol1. This works to a fashion.

If a field is changed and the cursor is not moved then when the button is clicked it picks up the old total in sumcontrol1 so mainformsum = old sumcontrol1. After the click sumcontrol1 is then updated. To get mainformsum updated to the correct amount the button has to be clicked again. Can I get around this?

so
Field1
10
12
18
20
Sumcontrol1 = 60
If 10 is changed to 20 and the cursor put onto the following field1 (ie the 12) then sumcontrol becomes 70. If the button is then clicked mainformsum becomes 70 as well.
Now if 10 is changed to 20 and the cursor is left on that field then prior to button being clicked sumcontrol1 = 60. When button is clicked mainformsum becomes 60 and then sumcontrol1 becomes 70.

In the event for the click can I force the update of sumcontrol1?
Hope this all makes sense

 

[CJ_London]

look at using me.requery

 

[ryetee]

Tried Me.requery and me.refreseh in the event for the button to no avail.
Also put in doevents at some stage.
I'll revisit as I've tried allsorts.
Incidentally when I put a break in the it seems to work....

 

[ryetee]

So Me.requery seems to blank the whole subform out
Me.subformname.requery sets the sumcontrol1 to null which in tturn then sets mainformsum to 0.
After click of button sumcontrol1 is correct and button needs clicking a second time to get mainformsum to proper value

 

[GinaWhipp]

Hmm, what about Me.Recalc?

 

[missinglinq]

...If any of one of these field1 fields is updated and the cursor put on to any other row then sum(field1) gets updated automatically...

...If a field is changed and the cursor is not moved then when the button is clicked it picks up the old total in sumcontrol1 so mainformsum = old sumcontrol1..

When you move the cursor onto another Record, the Record that was just edited is saved...hence the new sum is shown.

When you leave the cursor on the edited Record, then click the button on the Main Form, the edited Record has not been saved...hence the sum displayed reflects the old sum.

Out of curiosity, why are you using a Command Button to do this? Why not simply have an Unbound Textbox on the Main Form and use this Control Source for it:

=SubformName.Form!ControlNameOnSub

where SubformName is just that, the name of the Subform Control (not the original Form name, unless they are one and the same) and ControlNameOnSub is the name of the Control on the Subform Footer.

Linq ;0)>

 

[ryetee]

Hmm, what about Me.Recalc?

That seems to have done the business, thanks!

 

[ryetee]

When you move the cursor onto another Record, the Record that was just edited is saved...hence the new sum is shown.

When you leave the cursor on the edited Record, then click the button on the Main Form, the edited Record has not been saved...hence the sum displayed reflects the old sum.

Out of curiosity, why are you using a Command Button to do this? Why not simply have an Unbound Textbox on the Main Form and use this Control Source for it:

=SubformName.Form!ControlNameOnSub

where SubformName is just that, the name of the Subform Control (not the original Form name, unless they are one and the same) and ControlNameOnSub is the name of the Control on the Subform Footer.

Linq ;0)>

Thanks.
I've inherited this system to amend so have also inherited using the command button. I've got something that works from GinaWhipp.

 

[Simon_MT]

Also try Indexing the fields on the Footer I have a record with 50,000 entries in the subform, don't ask why, not my idea anyway by indexing the main fields I have the great performance these are numeric value fields.

Simon

 

[ryetee]

Also try Indexing the fields on the Footer I have a record with 50,000 entries in the subform, don't ask why, not my idea anyway by indexing the main fields I have the great performance these are numeric value fields.

Simon

Are these bound fields? Mine aren't so not sure how to index

 

[Simon_MT]

I then assume you are using VBA and DAO?

Simon

 

[ryetee]

I then assume you are using VBA and DAO?

Simon

I'm using Access 2010 and VBA. I don't know if I'm using DAO on this particular form.

 

[Simon_MT]

Are you the RecordSet to poplulate the Form or are the records derived directly from a Query or Table.

Simon

 

[ryetee]

Are you the RecordSet to poplulate the Form or are the records derived directly from a Query or Table.

Simon

Record Source uses a select on 2 joined tables