Question Requering control in main form brings focus on subform to first record? (1 Viewer)

[jonathanchye]

Hi all,

Have a very weird problem. I have a form with a subform that is linked via a one-to-many relationship.

On my main form I have a few textboxes with the control source set to DSum the prices on the subform.

My subform is set in datasheet view. I've created an "After Update" event in one of the field to requery the textbox in the mainform.

This works fine but I notice if I am on record no. 5 in the subform for example, when the after update event runs the subform gets refreshed too and I get returned to the first record (but still remain in the same field).

How can I stop this from happening?

So on my mainform the control source of my textbox (txtTotalPrice) is set as :

 = DSum("curPrice","tblSubForm", "subformID = " & Me.MainformID)

in the subform I have this in the "After Update" event of the "curPrice" field :

Forms!frmMainForm.txtTotalPrice.Requery

Everytime that after update triggers my whole subform gets refreshed and I get returned to the first record? I don't mind the subform getting refreshed but would prefer not to get returned to first record ...

 

[MStef]

Try this:
Delete this line in "After update" event.

 

[jonathanchye]

Try this:
Delete this line in "After update" event.

er..which line?

To be clearer in my sub form I have this :

Private Sub Form_AfterUpdate()
If IsNull(Me.intReqQty) = True Then Me.intReqQty.SetFocus
End Sub

This is the After Update event of the field "curUnitPrice"

Private Sub curUnitPrice_AfterUpdate()
If IsNull(Me.intReqQty) = False And IsNull(Me.curUnitPrice) = False Then
Me.curTotalPrice = Me.intReqQty * Me.curUnitPrice
Else
Me.curTotalPrice = Null
End If
Forms!frmWorksOrder.txtTotalPrice.Requery
End Sub

This is the control source of the textbox in the main form (if I don't requery this textbox everything is fine but of course the values don't update...)

=Nz(DSum("curTotalPrice","tblOrderItems","fkSpecID =" & Nz([SpecID],0)),0)

 

[MStef]

In your first thread.
"After Update" event
Forms!frmMainForm.txtTotalPrice.Requery

 

[Pat Hartman]

Everytime that after update triggers my whole subform gets refreshed

because that is EXACTLY what you asked Access to do. Requery says - run the RecordSource query AGAIN. Requerying the main form forces the subforms to requery perforce. Access can't keep track of what was the current record when you requery a form and it has no idea that that record will even be in the recordset once the query is executed again. It is up to you to manage the record position if it is important to you.

The better solution is not to requery the main form. MStef was trying to suggest that to you but he was too subtle.

You can refresh/recalc/requery a control which will just affect the specific control and should solve your problem.

 

[jonathanchye]

Hi,

Thanks for the reply. Sorry for my response as I got a bit confused.

Anyway, all I did was requery the control itself in the main form and not the whole mainform.

I can't recalc or refresh a textbox control but perhaps I am wrong?

The control source of the textbox relates back to the recordsource of the subform though but I don't think that should be a problem?

because that is EXACTLY what you asked Access to do. Requery says - run the RecordSource query AGAIN. Requerying the main form forces the subforms to requery perforce. Access can't keep track of what was the current record when you requery a form and it has no idea that that record will even be in the recordset once the query is executed again. It is up to you to manage the record position if it is important to you.

The better solution is not to requery the main form. MStef was trying to suggest that to you but he was too subtle.

You can refresh/recalc/requery a control which will just affect the specific control and should solve your problem.