Finding the first record >= today

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The record source for my Appointments Form is a query that sorts records by
AppointmentDate. When the form opens, I want the current record to be the
first record for today or the next record after today (if there are no
appointments for today).

I've tried VBA and a macro. I can get it to work if there is an appointment
for today with this macro:

GoToControl ApptDate
FindRecord =Date()

However, if there is no appointment today, the form just opens with the
earliest record in the database. I can't figure out how to position at the
first record after today.
 
bob_spear said:
The record source for my Appointments Form is a query that sorts records by
AppointmentDate. When the form opens, I want the current record to be the
first record for today or the next record after today (if there are no
appointments for today).

I've tried VBA and a macro. I can get it to work if there is an appointment
for today with this macro:

GoToControl ApptDate
FindRecord =Date()

However, if there is no appointment today, the form just opens with the
earliest record in the database. I can't figure out how to position at the
first record after today.
Click to expand...


You can do that using a little code in the form's Load event
provedure:

With Me.RecordsetClone
.FindFirst "appdate >= #" & date & "#"
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With

But, generally, it might be better to filter out the old
appointments so they don't clutter up the form's data.
Maybe you want to combine this with the above code. For
example, if you have no need to look at appointments more
than a week old, add a criteria to the appdate field in the
form's record source query:
 
Back
Top