none
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 RRS feed

  • 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
  • Well i had some time...so here is a quick and dirty sample.



    Wednesday, September 4, 2019 3:13 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