I also should have said I'm using the approach that Rick did in his series Associations where the subform is sown using the SourchObject = .

Thanks

Ok, I'm a step further I get it to open correctly when I open the form directly using;

Private Sub Form_Load()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim EventDate As Date
    Dim nearestEventDate As Date
    Dim nearestEventID As Long
    
    Dim currentDate As Date
    currentDate = Date
    
    nearestEventDate = DateAdd("yyyy", 10, currentDate)
    
    Set db = CurrentDb()
    strSQL = "SELECT EventID, EventDate FROM EventsT"
    Set rs = db.OpenRecordset(strSQL)
    
    While Not rs.EOF
        EventDate = rs.Fields("EventDate").Value
        If EventDate >= currentDate And EventDate < nearestEventDate Then
            nearestEventDate = EventDate
            nearestEventID = rs.Fields("EventID").Value
        End If
        rs.MoveNext
    Wend
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    
    DoCmd.OpenForm "EventMainF", acNormal
    
    ' Find the record for the nearest event and navigate to it
    Forms("EventMainF").Recordset.FindFirst "EventID = " & nearestEventID

End Sub

it opens with the whole set of records for all the events and it opens to the one that is coming up next.  Then when I try to open it as a subform within MainMenuF using

    SubFormCTL.SourceObject = "EventMainF"

I get an error

Run-time error '2450':

Microsoft Access cannot find the referenced form 'EventMainF'

Is there a way to specify my EventID = nearestEventID in combination with the

SubFormCTL.SourceObject = "EventMainF"   ??

Try:

SubFormCTL.SourceObject.Form.RecordSource = "SELECT * FROM EventT WHERE EventT=1234"

Rick thanks for your reply. I think I'm not fully clear about what I'm trying to do.  I do want to select all the records from EventT but would like the form to go to the record that's either on or next closest to Date().  I used the example about but changed the WHERE ;

SubFormCTL.SourceObject.Form.RecordSource = "SELECT * FROM EventT WHERE EventT=1234"

Was I supposed to change the Form.RecordSource to EventMainF.RecordSource?

SubFormCTL.SourceObject.EventMainF.RecordSource = "SELECT * FROM EventT WHERE EventT=1234"

Sorry I copied from your reply what I have in my code is

SubFormCTL.SourceObject.Form.RecordSource = "SELECT * FROM EventT WHERE EventDate => " & Date()

When I run

SubFormCTL.SourceObject.Form.RecordSource = "SELECT * FROM EventT WHERE EventDate => " & Date()

or

SubFormCTL.SourceObject.EventFormF.RecordSource = "SELECT * FROM EventT WHERE EventDate => " & Date()

I get Invalid Qualifier and it hilights .SourceObject.

Try this:

SubFormCTL.SourceObject = "EventMainF"
SubFormCTL.Form.RecordSource = "SELECT * FROM EventT WHERE EventDate>=#" & Date & "#"

Your operator is incorrect the should be >= not =>
Remember dates should be enclosed in #s.

^ what he said. :)

Kevin, I'll try that, but won't that only give me records where the EventDate is equal to or greater than today's date? I want to load all records from the EventT and goto the record where EventDate >= today's date. That way if there are updates to past records we can make them. That's why I was trying DoCmd.GoToRecord and/or DoCmd.FindRecord

Ok using some of my stuff and your very good ideas I tried this and it worked.

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim EventDate As Date
    Dim nearestEventDate As Date
    Dim nearestEventID As Long
    Dim currentDate As Date
    
    currentDate = Date
    
    nearestEventDate = DateAdd("yyyy", 10, currentDate)
    
    Set db = CurrentDb()
    strSQL = "SELECT EventID, EventDate FROM EventsT"
    Set rs = db.OpenRecordset(strSQL)
    
    While Not rs.EOF
        EventDate = rs.Fields("EventDate").Value
        If EventDate >= currentDate And EventDate < nearestEventDate Then
            nearestEventDate = EventDate
            nearestEventID = rs.Fields("EventID").Value
        End If
        rs.MoveNext
    Wend
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing
      
    SubFormCTL.SourceObject = "EventMainF"
    SubFormCTL.Form.RecordSource = "SELECT * FROM EventsT"
    SubFormCTL.Form.Recordset.FindFirst "EventID = " & nearestEventID

Thanks for you help getting me close, and probably there is an easier way but this works for me