How do you use VBA to make a form jump to the nearest future date in Access?

I’m trying to get an MS Access database to open a form either to today’s date if it’s in the date field in the database, or the nearest date in the future. I tried the code here, but it doesn’t work. It just highlights the date field and goes to the first record instead of the nearest one in the future.

The Date field is WorshipDate and the table is wp_elements. (I am a pastor using this table to plan Sunday Worship).

Private Sub Form_Load()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim TheDate As Date

Set db = CurrentDb
strSQL = "SELECT TOP 1 * FROM wp_elements WHERE wp_elements.WorshipDate >= Date() ORDER BY wp_elements.WorshipDate;"
Set rst = db.OpenRecordset(strSQL)
TheDate = rst.Fields(0)
WorshipDate.SetFocus
DoCmd.FindRecord TheDate, , True, , True
Set rst = Nothing
Set db = Nothing
'DoCmd.RunCommand acCmdRecordsGoToLast
End Sub

I know the SQL code I included works because I am using it in a Python script that’s working quite well.

What am I missing? Or is there some other, easier way to do this?

Answer

You should only require one line of code:

me.RecordSet.FindFirst "Date() >= WorshipDate"

Advertisement

Answer