Requery Main Form after updating SubForm without losing position

[jazzy]

there are several solutions on here but none seem to work for me. I have a bound main form 'frm_Invoice' bound to 'tbl_Contracts'. there is a field called InvoicePayments that is DSum based on a query. 1 subform 'subfrm_Invoice' with LinkMasterFields and Link Child Fields 'ContractsID'. 1 other subform 'subfrm_InvoiceLineItem' with LinkMasterFields and Link Child Fields 'InvoiceID'. subfrm_InvoiceLineItem is based on selection of subform 'subfrm_Invoice'. when i make a change on the subfrm_InvoiceLineItem i want it to requery the InvoicePayments field without losing its place on the subfrm_InvoiceLineItem datasheet. when a user is entering data and i requery AfterUpdate it goes to the first record on that datasheet so data input is infuriating for employees. Thanks

[Gicu]

Can you show us your current code? Is the InvoicePayments unbound (bound directly to the DSum in the form) or bound to a calculated field in the form's record source?

If the later you could try to run a

Me.Parent.Form.Recordset.Requery

in the subform's AfterUpdate event (requery the main form's recordset not the form itself). Otherwise you will need to record the current position in the subform (unique record id), requery the parent then navigate back to where you were, all this while you turn the screen updates off (Application.Echo False) to prevent flickering.

Cheers,

[jazzy]

put a command button and ran the Me.Parent.Form.Recordset.Requery Run_Time error '2452'" the expression you entered has an invalid reference to the parent property. InvoicePayments in Data is =DSum("[LineTotal]","qry_InvoiceLineItemPayments","InvoiceID = " & [Forms]![frm_Invoice]![InvoiceIDBOX]). thanks for helping.

"Otherwise you will need to record the current position in the subform (unique record id), requery the parent then navigate back to where you were, all this while you turn the screen updates off (Application.Echo False) to prevent flickering." got some code to back this method up please?

[Gicu]

Where did you put the command button, you don't need one if you use the subform's AfterUpdate event, but if you want one it needs to be on the subform (or remove .Parent.Form from the line if the button is on the main form).

Please post the code you currently have (using the # tags).

[jazzy]

command button was put on frm_Invoice (for easy testing using a command button). the 2 subforms are datasheets. on command click i have Me.Recordset.Requery. it requeries it but refreshes the entire screen and goes back to first record in subfrm_Invoice. user is inputting data on subfrm_InvoiceLineItem and when i have it in the AfterUpdate of that subform it refreshes that subform and goes to the top record. so after every new record is put in they have to arrow down to the bottom to add a new record

[Gicu]

Then the easiest would be to add code after the RecordsetRequery to set focus on the subform control and go to new record:

Me.Recordset.Requery
Me.subfrm_InvoiceLineItem.SetFocus
DoCmd.GoToRecord , , acNewRec

Cheers,

[jazzy]

very good idea but brings to mind, what if they are updating a record in the middle of the datasheet? ideally afterUpdate (tab through end of record) should just goto next record with form field updated. possibly using the ID of the record in InvoiceLineItem and just adding 1 to it and setfocus to that record?

Dim rec As Recordset
Set rec = subfrm_InvoiceLineItem.Form.Recordset
rec.FindFirst "InvoiceLineItemID = ??"

can't thank you enough, thanks again for helping.

[jazzy]

sorry mistake post

[Gicu]

sorry dont know how to "Please post the code you currently have (using the # tags)."

Just like you did in post # 7.

what if they are updating a record in the middle of the datasheet

that is what I was initially suggesting as the alternative, something like this (it goes in the AfterUpdate event of the subform or of the last editable control on the subform):

Dim lID as Long

Application.Echo False 'turn off screen updating
lID=Me.InvoiceLineItemID 'store the current ID
Me.Parent.Form.Recordset.Requery 'refresh parent (main) form
Me.InvoiceLineItemID.SetFocus
Docmd.FindRecord lID '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

[jazzy]

have code below in AfterUpdate on subfrm_InvoiceLineItem . I had to make the field InvoicePayments Unbound and have this "Form_frm_Invoice.InvoicePayments = DSum("[LineTotal]", "qry_InvoiceLineItemPayments", "InvoiceID =" & Form_frm_Invoice.InvoiceIDBOX)" in OnOpen on frm_Invoice, OnCurrent on subfrm_Invoice and nested in your code in AfterUpdate on subfrm_InvoiceLineItem. the field InvoicePayments changes everytime subfrm_Invoice and subfrm_InvoiceLineItem has a change. Requerying all the time ended up with the entire form being reset.

your code is working but only when i use BreakPoints (no idea why?) in VBA. when it runs without breakpoints it ends up at the top of the records in subfrm_InvoiceLineItem. for ex..the subfrm_InvoiceLineItem consists of InvoiceLineItemID 41-46. i am editing 45 and after the code it ends up on 41.

Dim lID As Long
Application.Echo False 'turn off screen updating
lID = Me.InvoiceLineItemID 'store the current ID
Form_frm_Invoice.InvoicePayments = DSum("[LineTotal]", "qry_InvoiceLineItemPayments", "InvoiceID =" & Form_frm_Invoice.InvoiceIDBOX)
Me.InvoiceLineItemID.SetFocus
DoCmd.FindRecord lID '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

[Micron]

I admit I skimmed here, but basically, didn't see where this info was posted: If you requery a form it reloads the recordset and by default, will always make the 1st record the current record. The only way to stay where you are after a requery is to clone the recordset and set its bookmark property, perform the requery, then set the current record to the bookmark property value. I cannot recall what happens if, for example, someone else has created a new record in the meantime and because of sorting that record would come before your bookmarked record. At least you should be close to where you were. IIRC, the bookmark property would increment if a record was inserted, but I'm not sure.

If you're requerying the main form, bookmarking the main form record should land you back on that record with the related records in the subform. However, it likely won't hold your place in the subform if you don't also bookmark that record.

[Gicu]

@jazzy:
maybe you could post a sample file with just the objects (tables, queries and forms) needed to illustrate this problem, I think you have extra code in many places. The code you have in post #10 doesn't make much sense as you no longer requery the main form to refresh the bound calculated field, so all the other lines other the setting the InvoicePayments value to the dSum are no longer needed.
@micron: the capturing of the ID in the variable lID before the requery and the Docmd.FindRecord lId afterwards is achieving the same as using the recordset bookmarking (I think).

[Micron]

the capturing of the ID in the variable lID before the requery and the Docmd.FindRecord lId afterwards is achieving the same as using the recordset bookmarking (I think).

I guess that's what happens when I skim...
In my defense, I was trying to make dinner at the same time?

[jazzy]

on open, open contracts then click invoice. then on subfrm_Invoice click final balance record. subfrm_InvoiceLineItem will populate. i was editing InvoiceLineItemID 45 from qty 10 to qty 5 and tabbing through end of record. when i get to end of record it recalculates the form correctly but then goes to the top of the subfrm_InvoiceLineItem to InvoiceLineItemID 41. when i put breaks in VBA, it works. ultimately when a recrd gets updated/added it should recalc all the numbers and go to next record. thanks

[isladogs]

Consider using .recalc instead of .requery
Recalc does not return you to the first record.
It may not work in your case but at least worth a try ....,