none
Access 2016 Form RRS feed

  • Question

  • I have an Access 2016 Database, I am trying to enter a date into a field and have the form open to that date. For example today is 2/9/2018, if I enter 12/21/2017 in this field and hit enter I would like the form to go to the records for that date.
    Friday, February 9, 2018 2:27 PM

All replies

  • If you want to just go to the first record found, then use the DoCmd.FindRecord command

    1. DoCmd.ShowAllRecords
    2. DoCmd.GoToControl command (Use Name of field you want to search)
    3. DoCmd.FindRecord command (Use Name of search input field)

    Friday, February 9, 2018 5:29 PM
  • As it happens, there is an example of navigating to a record by date in one of my demo files.  The file in question is CorrelatedSubs.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file is really intended to illustrate the use of correlated and nested subforms, using Northwind data as its example.  In the form which demonstrates nested subforms there is an unbound combo box in the orders subform in which an order date can be selected to move to that order.  The code in the combo box's AfterUpdate event procedure is:

    Private Sub cboGotoOrder_AfterUpdate()

        On Error GoTo Err_Handler
        
        Dim strCriteria As String
        Dim ctrl As Control
        
        Set ctrl = Me.ActiveControl
        
        ' navigate to selected order
        With Me.RecordsetClone
            If Not IsNull(ctrl) Then
                strCriteria = "OrderID = " & ctrl
                .FindFirst strCriteria
                If Not .NoMatch Then
                    Me.Bookmark = .Bookmark
                End If
            End If
        End With

    Exit_Here:
        Exit Sub
        
    Err_Handler:
        MsgBox Err.Description & " (" & Err.Number & ") ", vbExclamation, "Error"
        Resume Exit_Here

    End Sub

    You'll see that, while the combo box lists the order dates, the code does not look for the date, but for the OrderID primary key of the record with that order date.  This is achieved by setting up the combo box like this:

    RowSource:    

        SELECT OrderID,  OrderDate,  CustomerID
        FROM Orders
        WHERE CustomerID = [Form]![CustomerID]
        ORDER BYOrderDate;

    BoundColumn:    1
    ColumnCount:     2
    ColumnWidths:    0cm

    The OrderID column is hidden by setting the ColumnWidths property to zero, so the user sees the dates of the current customer's  listed, but by virtue of the BoundColumn property being 1 the value of the control is the OrderID corresponding to that date.

    Where searching for a specific record like this, even though the search might be on the basis of another column of distinct values, the code should normally search for the corresponding primary key value.  Where you wish to search for records on the basis of non-distinct values, you can filter the form rather than navigating to a specific record, as there might be multiple records with the same value.  You'll find an example of this in the FindRecord.zip demo in the same OneDrive folder.

    Ken Sheridan, Stafford, England


    Saturday, February 10, 2018 1:55 PM