Open continuous subform based on today's date

M

mafhobb

Registered User.
Local time
Today, 13:37
Joined
Feb 28, 2006
Messages
1,245
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
 
Code: 
' in frmReservations
Me.Recordset.FindFirst "CheckInDate = Date()"
 
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?
 
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.
 
Code: 
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.
 
Last edited:
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?
 
mafhobb said:
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?
Click to expand...
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. :(
 
Gasman said:
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. :(
Click to expand...
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.
 
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.
 
you can also try this, on the Load event of your form:
Code: 
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
 
If I can understand how to do one thing, then I'll understand how to do the other if I need to
Click to expand...
Then tell me whether you understood the original approach - I deliberately kept it very brief.
Code: 
Me.Recordset.FindFirst "CheckInDate = Date()"
Click to expand...

If you have understood, copying the meaning is not a big challenge. Doing instead of talking.
Code: 
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.)
 
Last edited:
arnelgp said:
you can also try this, on the Load event of your form:
Code: 
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
Click to expand...
@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..... :(
 
mafhobb said:
-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?
Click to expand...
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?
 
Gasman said:
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.
Click to expand...
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.
 
arnelgp said:
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?
Click to expand...
Sure, the 15th is closest, but the 12th is the closest date before the current day in the example.
 
mafhobb said:
Sure, the 15th is closest, but the 12th is the closest date before the current day in the example.
Click to expand...
so you want the Date Before when there is no matching date?
what is the date is March 1?
 
arnelgp said:
you can also try this, on the Load event of your form:
Code: 
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
Click to expand...
I do see that with this code it is also necessary to specify the property as the code is looking at the table.
 
arnelgp said:
so you want the Date Before when there is no matching date?
what is the date is March 1?
Click to expand...
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.
 
the code i gave you is for the Subform:
Code: 
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
 
arnelgp said:
the code i gave you is for the Subform:
Code: 
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
Click to expand...
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:
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?
Code: 
Me.Recordset.FindFirst "CheckInDate = Date()"
 
You must log in or register to reply here.

Similar threads

D
Solved I use VBA to adjust the height of a continuous subform based on # of records, but my subform only adjusts to show up to 3 at a time w/ scrollbar
2
Replies
24
Views
906
Mark_
M
M
Solved Record Selection in Continuous Form
Replies
6
Views
395
theDBguy
theDBguy
I
Make Table from filtered subform
Replies
4
Views
205
mike60smart
mike60smart
M
Solved Date Picker value depending on another control
Replies
7
Views
847
mafhobb
M
I
Change background color on click
Replies
3
Views
230
Mike Krailo
Mike Krailo

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom