Open continuous subform based on today's date (1 Viewer)

[mafhobb]

I have a form (frmMain) with a continuous subform (frmReservations) that contains reservations with dates (CheckInDate) both past and future. FrmReservations (the subform) is sorted by date (which is needed), but when it is loaded, it always selectec and displays the reservation with the earliest date.
What can I do so that the record selected in the continuous subform is the one with a date closest to today's date (Date())?
I do not mean to hide, suppress or change the order of the records as I need to be able to view them all if needed; I only to have the record closest to today's date selected and displayed.
Any ideas?
mafhobb

 

[ebs17]

' in frmReservations
Me.Recordset.FindFirst "CheckInDate = Date()"

 

[mafhobb]

Thank you, but what if there is no record with a checkindate = date()? In that case, can I make it select the one with the closest date, ahead or behind?

 

[LarryE]

If the forms record source is already sorted by date, then use:
DoCmd.GoToRecord acActiveDataObject, , acLast
in the forms On Load Event. That will present the Last (and latest) record when the form opens because it is already sorted.

 

[Josef P.]

Dim rst as dao.recordset

set rst = Me.RecordsetClone
rst.findfirst "CheckInDate >= Date()"
If rst.NoMatch Then
   rst.FindLast "CheckInDate <= Date()"
End If
...
me.Bookmark = rst.bookmark

or/and compare dates of FindFirst/FindLast.

 

[mafhobb]

I am not sure that this does what I need. Let me explain with a table. Imagine that I have reservations with the following dates:
1st 1/1/23
2nd 2/3/23
3rd 7/4/23
4th 12/4/23
5th 15/4/23

-Now, imagine that today's date is 7/4/23. In that case, the idea is to select the record that has that date since there is one, this would be record number 3

-Now imagine that today's date it is 14/4/23. In this case, there is no record with that date, so the one that I would like to automatically select is the record that has the closest date before that, in this case, the 4th record.

Does this make sense?

 

[Gasman]

I am not sure that this does what I need. Let me explain with a table. Imagine that I have reservations with the following dates:
1st 1/1/23
2nd 2/3/23
3rd 7/4/23
4th 12/4/23
5th 15/4/23

-Now, imagine that today's date is 7/4/23. In that case, the idea is to select the record that has that date since there is one, this would be record number 3

-Now imagine that today's date it is 14/4/23. In this case, there is no record with that date, so the one that I would like to automatically select is the record that has the closest date before that, in this case, the 4th record.

Does this make sense?

One minute it is 'can I make it select the one with the closest date, ahead or behind?', now it is only the one before that date?
Need to make you mind up, you are moving the goalposts all the time.

 

[mafhobb]

One minute it is 'can I make it select the one with the closest date, ahead or behind?', now it is only the one before that date?
Need to make you mind up, you are moving the goalposts all the time.

My apologies. At the time I was not sure what was more practical and I guess that on my last post I thought that I was simplifying the goal. If I can understand how to do one thing, then I'll understand how to do the other if I need to.

 

[Gasman]

Ok, so what happens when you have several records with the same date?
Try and think of all the situations you will encounter, then code for them, not in dribs and drabs.

I would perhaps look for Dmax() of the autonumber with criteria of <=Date().
Then go to that record using the ID number.

 

[arnelgp]

you can also try this, on the Load event of your form:

Private Sub Form_Load()
Dim dte As Variant
Dim d1 As Variant, d2 As Variant
'see if there is current date on the table
dte = DLookup("DateField", "TheTable", "DateField=Date()")
If IsNull(dte) Then
    ' no date for today
    ' get the early date
    ' and late date
    d1 = Nz(DMin("DateField", "TheTable", "DateField>Date()"), 0)
    d2 = Nz(DMax("DateField", "TheTable", "DateField<Date()"), 0)
    If Date - d1 <= d2 - Date Then
        dte = d1
    Else
        dte = d2
    End If
End If
Me!DateField.SetFocus
DoCmd.FindRecord dte
End Sub

 

[ebs17]

If I can understand how to do one thing, then I'll understand how to do the other if I need to

Then tell me whether you understood the original approach - I deliberately kept it very brief.

Me.Recordset.FindFirst "CheckInDate = Date()"

If you have understood, copying the meaning is not a big challenge. Doing instead of talking.

Me.Recordset.FindLast "CheckInDate <= Date()"

If you have many data records with the same date or the sorting is reversed, you would of course have to incorporate additional considerations into your actions.
(When paying by number of lines of code, @arnelgp gets more money.)

 

[Gasman]

you can also try this, on the Load event of your form:

Private Sub Form_Load()
Dim dte As Variant
Dim d1 As Variant, d2 As Variant
'see if there is current date on the table
dte = DLookup("DateField", "TheTable", "DateField=Date()")
If IsNull(dte) Then
    ' no date for today
    ' get the early date
    ' and late date
    d1 = Nz(DMin("DateField", "TheTable", "DateField>Date()"), 0)
    d2 = Nz(DMax("DateField", "TheTable", "DateField<Date()"), 0)
    If Date - d1 <= d2 - Date Then
        dte = d1
    Else
        dte = d2
    End If
End If
Me!DateField.SetFocus
DoCmd.FindRecord dte
End Sub

@arnelgp
Does that allow for records with same date being searched?
I know O/P has not said that could be the case, but still.....

 

[arnelgp]

-Now imagine that today's date it is 14/4/23. In this case, there is no record with that date, so the one that I would like to automatically select is the record that has the closest date before that, in this case, the 4th record.

Does this make sense?

15/4/23 is nearest to 14/4/23 (not 12/4/23)

between 15 and 14 is but 1 day between.
while 12 and 14 is 2 days?

 

[mafhobb]

Ok, so what happens when you have several records with the same date?
Try and think of all the situations you will encounter, then code for them, not in dribs and drabs.

I would perhaps look for Dmax() of the autonumber with criteria of <=Date().
Then go to that record using the ID number.

There is never a record with the same date as this subform is filtered to show records for one single property and only one reservation can begin on a specific date.

 

[mafhobb]

15/4/23 is nearest to 14/4/23 (not 12/4/23)

between 15 and 14 is but 1 day between.
while 12 and 14 is 2 days?

Sure, the 15th is closest, but the 12th is the closest date before the current day in the example.

 

[arnelgp]

Sure, the 15th is closest, but the 12th is the closest date before the current day in the example.

so you want the Date Before when there is no matching date?
what is the date is March 1?

 

[mafhobb]

you can also try this, on the Load event of your form:

Private Sub Form_Load()
Dim dte As Variant
Dim d1 As Variant, d2 As Variant
'see if there is current date on the table
dte = DLookup("DateField", "TheTable", "DateField=Date()")
If IsNull(dte) Then
    ' no date for today
    ' get the early date
    ' and late date
    d1 = Nz(DMin("DateField", "TheTable", "DateField>Date()"), 0)
    d2 = Nz(DMax("DateField", "TheTable", "DateField<Date()"), 0)
    If Date - d1 <= d2 - Date Then
        dte = d1
    Else
        dte = d2
    End If
End If
Me!DateField.SetFocus
DoCmd.FindRecord dte
End Sub

I do see that with this code it is also necessary to specify the property as the code is looking at the table.

 

[mafhobb]

so you want the Date Before when there is no matching date?
what is the date is March 1?

Yes, I'd like the date before if there is no match. You are correct in that it there is no previous reservation it wouldn't find anything. If there is no previous date, then do nothing.
But as I was mentioned above, I realize that this is going to be harder than I thought as it is not something that can be done by looking at the data on the subform (something similar to sort order, done at the subform level when it is already filtered) but at the table level, where all the reservations for all the properties are and therefore there needs to be a way to also specify the property.

 

[arnelgp]

the code i gave you is for the Subform:

Private Sub Form_Load()
Dim dte As Variant
'Dim d1 As Variant
Dim d2 As Variant
'see if there is current date on the table
dte = DLookup("DateField", "TheTable", "DateField=Date()")
If IsNull(dte) Then
    ' no date for today
    ' get the early date
    ' and late date
    'd1 = Nz(DMin("DateField", "TheTable", "DateField>Date()"), 0)
    d2 = Nz(DMax("DateField", "TheTable", "DateField<Date()"), 0)
'    If Date - d1 <= d2 - Date Then
'        dte = d1
'    Else
'        dte = d2
'    End If
    dte = d2
End If
If Not IsNull(dte) Then
    Me!DateField.SetFocus
    DoCmd.FindRecord dte
End If
End Sub

 

[mafhobb]

the code i gave you is for the Subform:

Private Sub Form_Load()
Dim dte As Variant
'Dim d1 As Variant
Dim d2 As Variant
'see if there is current date on the table
dte = DLookup("DateField", "TheTable", "DateField=Date()")
If IsNull(dte) Then
    ' no date for today
    ' get the early date
    ' and late date
    'd1 = Nz(DMin("DateField", "TheTable", "DateField>Date()"), 0)
    d2 = Nz(DMax("DateField", "TheTable", "DateField<Date()"), 0)
'    If Date - d1 <= d2 - Date Then
'        dte = d1
'    Else
'        dte = d2
'    End If
    dte = d2
End If
If Not IsNull(dte) Then
    Me!DateField.SetFocus
    DoCmd.FindRecord dte
End If
End Sub

Could you please clarify something for me?
The main form where this subform is placed in is loaded when the database is opened and then the data in the subform is updated by changing the value of a combobox in the main form using the following code:

DoCmd.SearchForRecord , "", acFirst, "[PropertyName] = " & "'" & Screen.ActiveControl & "'"

In my inexperienced view, this means that the subform is only loaded once, correct? Therefore this code should be on a diferent event, only when the property is updated in the main form and so are the reservations shown in this subform are also updated. Should this be on an "AfterUpdate" even in the subform? or maybe another event?
The code below, which works well when there is a reservation that matches the date is placed in the OnCurrent even of the reservations subform, but perhaps that is not the best place to have it either?

Me.Recordset.FindFirst "CheckInDate = Date()"