Requery Main Form after updating SubForm without losing position

[Gicu]

Here you are, please have a look; all was needed is to wrap the DSum expressions in NZ().



Cheers,

[jazzy]

this code returns it back to first record of subform

Private Sub Form_AfterUpdate()

Dim RecordID As Long


Application.Echo False 'turn off screen updating
RecordID = Me.InvoiceLineItemID 'store the current ID


Form_frm_Invoice.InvoicePayments = DSum("[LineTotal]", "qry_InvoiceLineItemPayments", "InvoiceID =" & Form_frm_Invoice.InvoiceIDBOX)
Form_frm_Invoice.InvoiceAddendums = DSum("[LineTotal]", "qry_InvoiceLineItemAddendums", "InvoiceID =" & Form_frm_Invoice.InvoiceIDBOX)
Me.InvoiceLineItemID.SetFocus
DoCmd.FindRecord RecordID 'get back to starting record
DoCmd.GoToRecord , , acNext ' comment this out if you want to stay where you started
Application.Echo True 'turn on screen updating
End Sub

This Code Works but seems excessive

Private Sub Form_AfterUpdate()

Dim RecordID As Long


Application.Echo False 'turn off screen updating
RecordID = Me.InvoiceLineItemID 'store the current ID


Form_frm_Invoice.InvoicePayments = DSum("[LineTotal]", "qry_InvoiceLineItemPayments", "InvoiceID =" & Form_frm_Invoice.InvoiceIDBOX)
Me.InvoiceLineItemID.SetFocus
DoCmd.FindRecord RecordID 'get back to starting record
DoCmd.GoToRecord , , acNext ' comment this out if you want to stay where you started
Application.Echo True 'turn on screen updating


Form_frm_Invoice.InvoiceAddendums = DSum("[LineTotal]", "qry_InvoiceLineItemAddendums", "InvoiceID =" & Form_frm_Invoice.InvoiceIDBOX)
Me.InvoiceLineItemID.SetFocus
DoCmd.FindRecord RecordID 'get back to starting record
DoCmd.GoToRecord , , acNext ' comment this out if you want to stay where you started
Application.Echo True 'turn on screen updating


End Sub

Thoughts?

[Gicu]

Did you try to wrap the DSums in the Nz() function as I showed you in post #16?

Private Sub Form_AfterUpdate()
Dim curPayment As Currency, curAddendums As Currency
curPayment = Nz(DSum("[LineTotal]", "qry_InvoiceLineItemPayments", "InvoiceID =" & Form_frm_Invoice.InvoiceIDBOX))
curAddendums = Nz(DSum("[LineTotal]", "qry_InvoiceLineItemAddendums", "InvoiceID =" & Form_frm_Invoice.InvoiceIDBOX))
'update parent form
Form_frm_Invoice.InvoicePayments = curPayment 'DSum("[LineTotal]", "qry_InvoiceLineItemPayments", "InvoiceID =" & Form_frm_Invoice.InvoiceIDBOX)
Form_frm_Invoice.InvoiceAddendums = curAddendums 'DSum("[LineTotal]", "qry_InvoiceLineItemAddendums", "InvoiceID =" & Form_frm_Invoice.InvoiceIDBOX)

Current event of the subform_Invoice:

Form_frm_Invoice.InvoicePayments = Nz(DSum("[LineTotal]", "qry_InvoiceLineItemPayments", "InvoiceID =" & Form_frm_Invoice.InvoiceIDBOX))
Form_frm_Invoice.InvoiceAddendums = Nz(DSum("[LineTotal]", "qry_InvoiceLineItemAddendums", "InvoiceID =" & Form_frm_Invoice.InvoiceIDBOX))

And same on the Open event of the frmInvoice.

Cheers,

[jazzy]

completely missed this..... new to the forum. thank you very much

[jazzy]

GICU thank you very much

[Gicu]

GICU thank you very much

You're very welcome 😉!