Answered by:
Having a subform , has lots of data with dates both backwards and forwards in time. Want to end up on today's date at opening, but have the opportunity to scroll both backwards and forwards in the dates

Question
-
Hi
My Sub Form has lots of data with dates both backwards and forwards in time. Want to end up on today's date at opening, but have the opportunity to scroll both backwards and forwards in the dates.
Lets say the name of Forms are
MainForm
SubForm1
The controle name, containing the dates, is PlannedDay.
Cheers // Peter Forss Stockholm
Wednesday, September 4, 2019 2:25 PM
Answers
-
The following is an example of code in a form's Load event procedure which does this:
Private Sub Form_Load()
Dim strCriteria As String
strCriteria = "TransactionDate = #" & _
Format(DMax("TransactionDate", "qryTransactions", "TransactionDate <= Date()"), "yyyy-mm-dd") & "#"
With Me.RecordsetClone
.FindFirst strCriteria
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub
where qryTransactions is the form's RecordSource. This will work with a parent form, but in the case of a subform, only when the first instance of a subform loads when the parent form is opened. To get it to do so when the parent form is moved to each record you should instead put the code in a Public function in the subform's module, e.g. declared with Public Function LoadSubform(). This exposes it as a method of the class. You can then call it in the Current event procedure of the parent form like this:
Form_NameOfSubform.LoadSubform
Note that Form_NameOfSubform is a reference to the class, so NameOfSubform should be the name of the subform's source form object, not of the subform control in the parent form's Controls collection.
Ken Sheridan, Stafford, England
- Marked as answer by ForssPeterNova Wednesday, September 4, 2019 6:53 PM
Wednesday, September 4, 2019 3:30 PM
All replies
-
Probably a screenshot would helpful to avoid misconceptions..Wednesday, September 4, 2019 3:02 PM
-
Here it is
Cheers // Peter Forss Stockholm
Wednesday, September 4, 2019 3:06 PM -
-
The following is an example of code in a form's Load event procedure which does this:
Private Sub Form_Load()
Dim strCriteria As String
strCriteria = "TransactionDate = #" & _
Format(DMax("TransactionDate", "qryTransactions", "TransactionDate <= Date()"), "yyyy-mm-dd") & "#"
With Me.RecordsetClone
.FindFirst strCriteria
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub
where qryTransactions is the form's RecordSource. This will work with a parent form, but in the case of a subform, only when the first instance of a subform loads when the parent form is opened. To get it to do so when the parent form is moved to each record you should instead put the code in a Public function in the subform's module, e.g. declared with Public Function LoadSubform(). This exposes it as a method of the class. You can then call it in the Current event procedure of the parent form like this:
Form_NameOfSubform.LoadSubform
Note that Form_NameOfSubform is a reference to the class, so NameOfSubform should be the name of the subform's source form object, not of the subform control in the parent form's Controls collection.
Ken Sheridan, Stafford, England
- Marked as answer by ForssPeterNova Wednesday, September 4, 2019 6:53 PM
Wednesday, September 4, 2019 3:30 PM -
Thank you guys
Cheers // Peter Forss Stockholm
Wednesday, September 4, 2019 6:54 PM