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

  • Thread starter Thread starter Michael
  • Start date Start date
M

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?
 
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.
 
Back
Top