Move to record closest to today's date on continuos form (1 Viewer)

[mafhobb]

I have a continuos subform (frmEvSubform) showing a list of events in a table (tblEv), and one of the records is the date of the event (EvDate). The continuous form is ordered by date (ascending).
What would be the code to add a button to move the focus to the event closest to today's date?
I'd like to keep the order of the events the same as it is now (ascending), just "move" the entire list up so the event shwon at the top is the one closest to today.
Thanks!

 

[theDBguy]

First, I think you would need a function to return the ID of the record with the date closest to today. Then, maybe you can use a bookmark to navigate the form to that ID.

 

[CJ_London]

Off the top of my head something like

With me.recordset
.find “evdate = #“ dmax(“evdate”,”evtable”, “evdate<=date()”) & “#”
End with

On my phone so change the smart quotes

 

[MarkK]

Finding the date and moving the view will likely be two steps.
1) Find the row you want to highlight
2) Move the record pointer x number of rows to scroll the view so your selected row is where you want it.
3) Re-select the found row to highlight.

 

[mafhobb]

uff...
Thanks. Sounds much more complicated than I can handle.

 

[Gasman]

uff...
Thanks. Sounds much more complicated than I can handle.

Not really.
Here I wanted to show the last 6 records in a continuous form. If I just went to last, that would be the only record I would see.
So i move back 5 records, and then position myself back on the last record.

However I now get to see as below

You do not need to move the record display, if all you want to find is a particular record.

? forms!frmemails!ID
 1752

The code to do that

    DoCmd.RunCommand acCmdRecordsGoToLast
    DoCmd.GoToRecord acDataForm, Me.Name, acPrevious, 5 ' Needed for a continuous form as only last record shows.
    DoCmd.RunCommand acCmdRecordsGoToLast

 

[mafhobb]

I see.
How would you go about specifying a record based on data on that record (the date)?

 

[Gasman]

I see.
How would you go about specifying a record based on data on that record (the date)?

As you have already been shown. Get the ID, then got to that record also as shown.
Then if you need to, move back 5 (or how many you want), then go back to that record.

When you haven't a clue, you can always ask ChatGPT?

Private Sub cmdFind_Click()
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "ID = " & Me!txtFindID

    If Not rs.EOF Then
        Me.Bookmark = rs.Bookmark
    Else
        MsgBox "Record not found", vbExclamation
    End If
    set rs = Nothing
End Sub

Replace txtFind with the value you get from your DLookup()

From my immediate window

set rs = forms!frmemails.recordsetclone
rs.findfirst "ID=1700"
? forms!frmemails.id
 1752
forms!frmemails.bookmark = rs.bookmark
? forms!frmemails.id
 1700

If you want that record at the top, then you do not need to do anything else?

Would probably get DMax() on the ID for that DMAX() date, in case you have multiple records with that date.

 

[The_Doc_Man]

Just for clarification: When you say "date closest to" - does that include dates in either direction from the specified date? Closest date BEFORE the date? Can you use closest date AFTER the date? Can you use closest date in either direction? Not to downplay ANY of the help you were given, but when you ask a question, you need to be specific about what you mean.

 

[cheekybuddha]

What would be the code to add a button to move the focus to the event closest to today's date?

Do you want to add the button to the main form or the subform?

If you add the button to the main form (called eg cmdGoToToday) you can try something like:

Private Sub cmdGoToToday_Click()

  Dim strSQL As String, strWhere As String, lID As Long, bk As Variant
 
  With Me.frmEvSubForm.Form
    strWhere = " WHERE EvDate <= " & Format(Date, "\#yyyy\-mm\-dd\#")
    If Len(.Filter) And .FilterOn Then
      strWhere = " AND " & .Filter
    End If
    strSQL = .RecordSource
    If Right(strSQL, 1) = ";" Then
      strSQL = Left(strSQL, Len(strSQL) - 1)
    End If
    strSQL = "SELECT TOP 1 ID FROM (" & strSQL & ")" & strWhere & " ORDER BY EvDate DESC"
    With CurrentDb.OpenRecordset(strSQL)
      If Not .EOF Then
        lID = .Fields(0)
      End If
      .Close
    End With
    If lID > 0 Then
      With .RecordsetClone
        .FindFirst "ID = " & lID
        If Not .NoMatch Then
          bk = .Bookmark
        End If
      End With
    End If
    If bk Then
      .Bookmark = bk
    Else
      MsgBox "Could not find a matching date!"
    End If
  End With

End Sub

(NB. totally untested!)

 

[mafhobb]

Just for clarification: When you say "date closest to" - does that include dates in either direction from the specified date? Closest date BEFORE the date? Can you use closest date AFTER the date? Can you use closest date in either direction? Not to downplay ANY of the help you were given, but when you ask a question, you need to be specific about what you mean.

It really does not matter. I am hoping to get an idea of the code that I can understand and modify as needed. If it is easier to get a record before today's date, that is ideal, but if it is easier to get a date for a record after today's date, then that works too. Again, ia m trying to understand the steps and hoepfully get some pieces of code that I can understand, learn from and edit as needed later.
Thanks for everyone's responses so far!