Subform requery anomaly

[davesmith202]

I have a main form (tsTimeSheetMain) which contains a sub form (tsTimeSheetDataNewSub) in data sheet view. When I click off one row onto another row in the sub form, it triggers this code:

Forms!tsTimeSheetMain!ProjectMonSum.Requery

i.e. it tries to requery the ProjectMonSum field (on the main form) which is a dsum calculated field. This works fine and updates the ProjectMonSum field (which dsums values from the same datasource as the subform.

However, this seems to put the cursor back to the top left field in the subform (datasheet view), rather than leave it in the field I click on (in the subform).

Why is this happening and what is a decent workaround this issue? I just want to update the calculated dsum field each time you update values in the subform.

Thanks,

Dave

 

[pr2-eugin]

Do not use Calculated fields ! Calculated fields belong in queries, not tables.

 

[davesmith202]

Actually, the Calculated field is unbound and on the Main Form. It is a total of values in the sub form datasheet. However, when I update a value in the datasheet, I want the dsum value on the main form to update too. That is why I have used a requery. Perhaps there is a better way of doing this?

 

[CJ_London]

in the header or footer of your subform have an unbound control with the control source of

=sum(myctrl)

We'll call it ttlSubForm

In your main form have an unbound control with a controlsource of

=tsTimeSheetDataNewSub.form.ttlSubForm

This will update automatically without the need to use dsum or to requery

 

[pr2-eugin]

If the control is in the Main (Parent) form why are you re querying the SubForm.. If it is the Control on the main from and it is calculated based on expression, it should auto calculate the Sum value if it changes..

 

[Dewi]

Dave,
This will happen..requerying will default any subform back to the first record..however here is a solution. On the event where you are requerying the main form put in this code. I wrote this to cope with a subform within a subform within a main form.

Dim DelReccount As Integer
Dim DelSubRecCount As Integer
DelReccount = 0
DelSubRecCount = 0
DelReccount = Forms!Mainform![Subform].Form.CurrentRecord
DelSubRecCount = Forms!MainForm![Subform]![SubSubform].Form.CurrentRecord

Forms!MainForm.Requery

Forms!MainForm![Subform].SetFocus
Dim index As Integer
index = 1
Do While index < DelReccount
DoCmd.GoToRecord , , acNext
index = index + 1
Loop

Forms!MainForm![Subform].SetFocus 'important to put this line in again
Forms!MainForm![Subform]!SubSubform.SetFocus

Dim Subindex As Integer
Subindex = 1
Do While Subindex < DelSubRecCount
DoCmd.GoToRecord , , acNext
Subindex = Subindex + 1
Loop

end sub


you can omitt the subsubform code if you only have a single subform of course.

This works for me, I spent an absolute age trying to get a solution that worked, in the end it's quite simple.

Cheers

 

[davesmith202]

pr2-eugin, the requery is me trying to requery a field (Forms!tsTimeSheetMain!ProjectMonSum.Requery) on the main Form. Maybe you can't requery a field, just a Form??

I am finding if we change values in the subform, the dsum on the main form doesn't auto update.

CJ London had an idea, although I am not sure if I can refer to a footer unbound calculated field in a subform from the main form?

 

[pr2-eugin]

Maybe you can't requery a field, just a Form??

No, you can requery UNBOUND calculated controls.

I am finding if we change values in the subform, the dsum on the main form doesn't auto update.

If you are using DSum, you should use Requery as DSum will not auto update.

CJ London had an idea, although I am not sure if I can refer to a footer unbound calculated field in a subform from the main form?

You can.. I use the matrix in THIS LINK to get the right Format/Syntax.

 

[CJ_London]

CJ London had an idea, although I am not sure if I can refer to a footer unbound calculated field in a subform from the main form?

I showed you how