Requery main form after subform update and not lose place in main form.

[Michael]

I have a Form that has multiple subforms. I need to requery the main
form after any subform edits and return to the same record I was
working with in the main form and place my focus back into the subform
where I was making my edits.

Main Form Name: FRM_Tickets
Sub Form Name: SUB_AP_Tickets

I read a similar post and they helped me develop this code to resolve
part of my problem (refresh and take me back to the same record in the
main form):

Private Sub Form_AfterUpdate()
Dim frmMain As Form, sfrm As Form, hldMainID As Long, hldsfrmID As
Long
Set frmMain = Forms![FRM_TICKETS]
hldMainID = frmMain![TicketNumber]

frmMain.Requery
frmMain.Recordset.FindFirst "[TicketNumber] = " & hldMainID

Set frmMain = Nothing
End Sub

I haven't been able to figure out how to put the focus back in the
subform which I was editing before the refresh. The code I was
working with (which did not work) was:

Dim frmMain As Form, sfrm As Form, hldMainID As Long, hldsfrmID As
Long
Set frmMain = Forms![Tickets]
Set sfrm = frmMain![SUB_AP_Tickets].Form
hldMainID = frmMain![TicketNumber]
hldsfrmID = sfrm![RecordNumber]

frmMain.Requery
frmMain.Recordset.findFirst "[TicketNumber] = " & hldMainID
sfrm.Recordset.FindFirst "[RecordNumber] = " & hldsfrmID

Set sfrm = Nothing
Set frmMain = Nothing

The above code gave me an error on the "Set sfrm = frmMain!
[SUB_AP_Tickets].Form" line. Can anyone help me get the next phase of
my code figured out?

 

[Marshall Barton]

I have a Form that has multiple subforms. I need to requery the main
form after any subform edits and return to the same record I was
working with in the main form and place my focus back into the subform
where I was making my edits.

Main Form Name: FRM_Tickets
Sub Form Name: SUB_AP_Tickets

I read a similar post and they helped me develop this code to resolve
part of my problem (refresh and take me back to the same record in the
main form):

Private Sub Form_AfterUpdate()
Dim frmMain As Form, sfrm As Form, hldMainID As Long, hldsfrmID As
Long
Set frmMain = Forms![FRM_TICKETS]
hldMainID = frmMain![TicketNumber]

frmMain.Requery
frmMain.Recordset.FindFirst "[TicketNumber] = " & hldMainID

Set frmMain = Nothing
End Sub

I haven't been able to figure out how to put the focus back in the
subform which I was editing before the refresh. The code I was
working with (which did not work) was:

Dim frmMain As Form, sfrm As Form, hldMainID As Long, hldsfrmID As
Long
Set frmMain = Forms![Tickets]
Set sfrm = frmMain![SUB_AP_Tickets].Form
hldMainID = frmMain![TicketNumber]
hldsfrmID = sfrm![RecordNumber]

frmMain.Requery
frmMain.Recordset.findFirst "[TicketNumber] = " & hldMainID
sfrm.Recordset.FindFirst "[RecordNumber] = " & hldsfrmID

Set sfrm = Nothing
Set frmMain = Nothing

The above code gave me an error on the "Set sfrm = frmMain!
[SUB_AP_Tickets].Form" line. Can anyone help me get the next phase of
my code figured out?

As I said in your other thread, I am not convinced that you
really need to requery the main form just because a subform
record is added/edited.

I also don't see how the focus move from the subform to the
main form, so I don't want to comment on returning the
focus.

Without knowing what error you got, I suspect your specific
problem about the Set sfrm = line may be caused by the
subform control name being different from the name of the
form object (SourceObject property) displayed in the subform
control.

 

[Michael]

We need to requery the main form to refresh the other subforms which
are dependent upon some of the same data. It's not just for adds/
deletes but also for edits of existing subform records.

Sorry about the 2 threads. I thought this was more of a new idea and
didn't want to clutter the old thread.

I will look at the source object and see if that is the issue.

 

[Marshall Barton]

We need to requery the main form to refresh the other subforms which
are dependent upon some of the same data. It's not just for adds/
deletes but also for edits of existing subform records.

Sorry about the 2 threads. I thought this was more of a new idea and
didn't want to clutter the old thread.

I will look at the source object and see if that is the issue.

If you want to requery the other subforms, you should
requery the subforms, not the main form:
Parent,[subform control N].Form.Requery

If you must requery the main form and make the same record
current, then the code in the subform could be something
like:

Dim lngPK As Long 'assuming autonumber primary key
lngPk = Parent.PKfield
Parent.Requery
With Parent.RecordsetClone
.FindFirst "PKfield = " & lngPK
If Not .NoMatch Then Parent.Bookmark = .Bookmark
End If