After Update set focus to the record with the most current date

[SusanCoder]

I'm working with a continuous form we'll call subform1. The underlying query sorts by field DateOfService, descending. After a record updates the subform's After Update event proc requeries several subforms then sets the focus back on subform1, except the focus is on the record with the oldest DateOfService value. How can I set the focus to the record with the newest DateOfService value? I'm assuming I would place this code in the subform's After Update event proc after resetting the focus to subform1.



Thank you. Your help is much appreciated!

Susan

[ranman256]

me.subform.form.recordset.movefirst

[SusanCoder]

Thanks for the response, ranman256. I entered this code in the subform1's After Update event proc.

Me.[Subform Name].[MainForm Name].Recordset.MoveFirst

where Subform Name and MainForm Name equal the actual form names, and I received this error: Microsoft Access can't find the field '|1' referred to in your expression

I've not worked with recordsets much, but do I need to define my database and recordset, open the recordset, movefirst, then close the recordset?

Is there another way to move the focus to the most current record without using recordset?

[Micron]

I think you misinterpreted the words used, plus you are missing at least one key word. If referring to a form other than the one that the code or expression is on, you can't use Me. Regardless, the following syntax should work for when it is and is not the same form:

Forms!MainFormName!SubformControlName.Form. then the property, method or object after the last period/dot that I wrote. You cannot eliminate or change the bold words using that syntax.
e.g.

Forms!MainFormName!SubFormControlName.Form.Recordset.MoveFirst

[SusanCoder]

Thanks, Micron.

My code is shown below, where [OKIL Main Screen] is the main form and [ServicesSubTable subform] is the subform. (Don't judge, I didn't name these forms.)

The code runs and doesn't produce an error, but also doesn't set the focus on the first record (the most current record according to DateOfService date value.)

More specifically, when the DateOfService on the 4th record in the list of records on the subform is updated to today's date, the focus stays on the 4th record after the update, and the updated record is shown appropriately in the first record in the list, which is sorted in descending order. After the update I want the focus to be on the record at the top of the list with the most current DateOfService value.

fyi...all code prior to the .recordset.movefirst line is established code and works as designed. Thanks for the help!

Private Sub Form_AfterUpdate()
On Error GoTo Err_Form_AfterUpdate
    If Me.Dirty = True Then Me.Dirty = False
    Forms![OKIL Main Screen]![subfrmNotifSTDInit].Requery
    Forms![OKIL Main Screen]![subfrmNotifSTD2nd].Requery
    Forms![OKIL Main Screen]![subfrmNotifSTDFinal].Requery
    Forms![OKIL Main Screen]![subfrmNotifLSAUnder16].Requery
    Forms![OKIL Main Screen]![subfrmNotifLSAInitUnder16].Requery
    Forms![OKIL Main Screen]![subfrmNotifLSA2ndUnder16].Requery
    Forms![OKIL Main Screen]![subfrmNotifLSAFinalUnder16].Requery
    Forms![OKIL Main Screen]![subfrmNotifLSA].Requery
    Forms![OKIL Main Screen]![subfrmNotifLSAInit].Requery
    Forms![OKIL Main Screen]![subfrmNotifLSA2nd].Requery
    Forms![OKIL Main Screen]![subfrmNotifLSAFinal].Requery
    Forms![OKIL Main Screen]![subfrmNotifILP].Requery
    Forms![OKIL Main Screen]![subfrmNotifILPInit].Requery
    Forms![OKIL Main Screen]![subfrmNotifILP2nd].Requery
    Forms![OKIL Main Screen]![subfrmNotifILPFinal].Requery
    Forms![OKIL Main Screen]![subfrmNotif90D].Requery
    Forms![OKIL Main Screen]![subfrmNotif90DInit].Requery
    Forms![OKIL Main Screen]![subfrmNotif90D2nd].Requery
    Forms![OKIL Main Screen]![subfrmNotif90DFinal].Requery
    Forms![OKIL Main Screen]![subfrmNotifFinalCallILP].Requery
    Forms![OKIL Main Screen]![subfrmNotifFinalCall90D].Requery
    Forms![OKIL Main Screen]![subfrmNotifFollowUp].Requery
    Forms![OKIL Main Screen].[ServicesSubTable subform].Requery
    Forms![OKIL Main Screen]![ServicesSubTable subform].SetFocus
     
    Forms![OKIL Main Screen]![ServicesSubTable subform].Form.Recordset.MoveFirst

Exit_Form_AfterUpdate:
    Exit Sub

Err_Form_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_Form_AfterUpdate

End Sub

[Micron]

[OKIL Main Screen] is the main form and [ServicesSubTable subform] Forms![OKIL Main Screen]![subfrmNotifSTDInit].Requery

You're not understanding. I wrote in post 4: You cannot eliminate or change the bold words using that syntax yet you dropped the word Form. Try
Forms![OKIL Main Screen]![ServicesSubTable subform].FORM.Requery
I also wrote SubformControlName but you seem to be saying that you're using the subform name. The subform control might be named the same as the subform, but then again, they might not be. SubFormControlName is whatever the control is that contains the subform. If they are named the same, probably will work. If not, it won't. If you get this syntax right it may or may not solve your issue, but it certainly won't if the syntax is incorrect. First things first, I guess. Maybe this explanation, which I'll copy from a text file, will help:

[Forms]![frmNP]![subNPfrm].[Form]![txtNPno]
frmNP is main form name
subNPfrm is name of subform control on frmNP
txtNPno is the textbox control name
NOTE: subformControlName IS NOT NECESSARILY THE SUBFORM NAME

[SusanCoder]

Thanks for sticking with me, Micron. I misspoke on my previous reply. [ServicesSubTable subform] is the SubFormControlName.

Here is the syntax you gave me to try in your 4th post:
Forms!MainFormName!SubFormControlName.Form.Records et.MoveFirst

Here is the code I tried, noted in my previous post:

Forms![OKIL Main Screen]![ServicesSubTable subform].Form.Recordset.MoveFirst

Unfortunately the code didn't solve the issue of refocusing to the current record after updating the record.

P.S. I did update the code on the requery statement. Thank you.

[Micron]

Now that we seem to have the syntax worked out, you're saying the records are sorted by descending date, which means the first record has the most recent date. When focus goes back to the form the record that becomes current is the last record because it's the earliest date? Or at least, it's not the first (latest date)? If that's not correct, then maybe post pics showing the problem. Did you step through this code to ensure it executes as expected? Have you turned off warnings or done something with error handling that is masking a problem? Did you try MoveLast to see if it made any difference?

If you could post a db copy that would probably speed thing up a lot.

[isladogs]

If all those subforms are linked to the main form using master/child fields then this much simpler version should work

Private Sub Form_AfterUpdate()
On Error GoTo Err_Form_AfterUpdate
If Me.Dirty = True Then Me.Dirty = False

'the next line should requery all controls including the subform controls and their contents
Me.Requery 


Me.[ServicesSubTable subform].SetFocus

'the next line is probably unnecessary as setting focus to the subform should send cursor to the first record
Me.[ServicesSubTable subform].Form.Recordset.MoveFirst 


Exit_Form_AfterUpdate:
Exit Sub


Err_Form_AfterUpdate:
MsgBox Err.Description
Resume Exit_Form_AfterUpdate


End Sub

That's it!