#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2013
    Location
    Staten Island, NY
    Posts
    2
    Rep Power
    0

    Sorting on a subform


    I've had this Access DB for years. It's has a master table or names and a child table with attendance dates.

    The form shows on the screen with a tab for the attendance form. The form sorts the dates in DES order.

    I wanted to click a button and:
    Add a Record
    Then sort the attendance dates in ASC order so the new added record would be at the top.
    move the focus to the new (empty) attenance date.

    I got the add a record by entering this code inn the On Clicl property of the New Button:

    Dim StrSQL
    StrSQL = "Insert into Attendance (IDNUM) Select IDNUM from Member where IDNUM = [Forms]![Secretary Changes]![IDNUM];"
    Docmd. RunSQL StrSQL

    Docmd.doMenuItem acformbar, acrecordsmenu,5 , , acmenuver70 (Refresh)

    But Orderon doesn't work!!! Even if I set the focus to the Attendance form.


    Any help would be greatly appreciated

    Alan
  2. #2
  3. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    59
    Is the button on the main form or on the subform:

    Mainform:
    Code:
    Forms!MainFormName!SubformName.form.OrderBy = "[DateField] desc"
    Forms!MainFormName!SubformName.form.orderOn = True
    SubForm:
    Code:
    Me.OrderBy = "[DateField] Desc"
    Me.OrderOn = True
    Last edited by meratigoerr; January 28th, 2013 at 05:43 AM.
  4. #3
  5. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2013
    Location
    Staten Island, NY
    Posts
    2
    Rep Power
    0
    Hi,

    Great Reply!

    Here is the full Code for anyone with this issue:

    Private Sub CmdAddRec_Click()
    Dim StrSQL As String
    'Add New Attendance Record
    StrSQL = "Insert into Attendance (IDNUM) Select IDNUM from Member where IDNUM = [Forms]![Secretary Changes]![IDNUM];"
    DoCmd.RunSQL StrSQL

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

    'Set Focus to New Record
    Me.Attendance1_subform.SetFocus

    'Change Attendance Date Sort to ASC so Cursor is in New Date Field
    Forms![Secretary Changes]![Attendance1 subform].Form.OrderBy = "AttendDate ASC"
    Forms![Secretary Changes]![Attendance1 subform].Form.OrderByOn = True
    End Sub


    Thanks Again for the Quick Reply,
    Alan
    Staten Island, NY

Similar Threads

  1. Replies: 2
    Last Post: August 29th, 2008, 07:57 AM
  2. Subform Sorting?
    By Solitaire in forum Microsoft Access Help
    Replies: 2
    Last Post: October 29th, 2006, 05:04 AM
  3. Subform Not Sorting
    By gschimek in forum Microsoft Access Help
    Replies: 1
    Last Post: September 24th, 2006, 07:55 PM
  4. Filtering field in 2nd subform based on selection in 1st subform
    By sburgess in forum Microsoft Access Help
    Replies: 1
    Last Post: September 5th, 2006, 03:51 AM
  5. Sorting records in form alphabetically by first record in subform
    By jrthomp in forum Microsoft Access Help
    Replies: 1
    Last Post: August 1st, 2004, 04:47 PM

IMN logo majestic logo threadwatch logo seochat tools logo