Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Form Sort Syntax (A2K SP3)

    I have an OnOpen event in frmCheckInLast as follows:

    Private Sub Form_Open(Cancel As Integer)

    Dim strWhere As String

    strWhere = "RoomNumber=[Enter Room Number]"

    DoCmd.OpenForm "frmCheckInLast", acNormal, , strWhere

    End Sub

    I need to be able to sort on the RoomNumber field and the CheckOutDate3 field. Can someone assist with the proper syntax.

    Tom

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form Sort Syntax (A2K SP3)

    The code to sort is as follows:

    Me.OrderBy = "RoomNumber, CheckOutDate3"
    Me.OrderByOn = True

    But why do you open the form anew in the On Open event? It would make more sense to put both the parameter and the sort order in the query that acts as record source of the form, you wouldn't need code then.

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Sort Syntax (A2K SP3)

    I have a query RentCalc0 which has about 100 fields, a great many of chich are calculated fields. Currently, I have another query, qryRentCalcLast which has about 60 of the field in RentCalc0.

    RentCalcLast ashs the operator to enter the Room Number as the selectin criteria. RentCalcLast is sorted on RoomNumber -ascending and CheckOutDate-descending.

    What I was trying do accomplish was to reduce the number of objects in the database by eliminating RentCalcLast. I was trying to do this by using qryRentCalc0 as the source and adding the selection criteria and sorts to the form.

    From a space point of view am I on the right track or should I just use both queries. I have a great many forms and reports that were originally based on seperate queries. I have converted some to use RentCalc0 with selection/sorts in the forms/Reports to reduce the size of the database.

    Tom

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form Sort Syntax (A2K SP3)

    The way I would do it is by creating a form on which the user can select various options to filter a form or report, then open the form/report in the On Click event procedure of a command button. For example, such a form could contain a combo box that lists all room numbers. Code to open a form with a specific room would look like this:

    DoCmd.OpenForm FormName:="frmCheckInLast", WhereCondition:="RoomNumber = " & Forms!frmSelect!cboRooms

    where frmSelect is the name of the form with the combo box, and cboRooms is the name of the combo box. This way, the query doesn't need a parameter and the frmCheckInLast form doesn't need code to filter the records.

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Sort Syntax (A2K SP3)

    Thanks. I will look into that possibility.

    Tom

  6. #6
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Sort Syntax (A2K SP3)

    In this example how would the syntax be if RoomNumber is Ascending and CheckOutDate3 is descending?

    Tom

  7. #7
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Sort Syntax (A2K SP3)

    I found the answer of adding DESC after the name of the filed.

    Thanks again fro the assistance

    Tom

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •