sortingms-accessvbaopenform

Access VBA OpenForm Grouping and Sorting


I have a form that is used for data entry. We have to go back through and add data to these records. Is there a way to pull up the form that groups the records by field "A" and sorts by field "B"? This would essentially order the forms A1-1, A1-2, etc, making adding data easier.

Right now I am using DoCmd.OpenForm to only display records with certain values in certain fields. Do I just need to modify this a bit?

Thanks for the help!

[Edit]

I would like this to load the form on button click so I have

Private Sub btnDataEntry_Click() 
    DoCmd.OpenForm "Data Sheet", acNormal, , , acFormEdit, , OpenArgs:="MapNumber"
End Sub

Then as suggested

Private Sub Form_Load() 
    If Not IsNull(Me.OpenArgs) Then 
        Main.OrderBy = Me.OpenArgs 
        Main.OrderByOn = True 
    End If 
End Sub

This is not working for me. If possible I would also like it to group all map numbers together and then have all Item numbers ascending. So there could be 10 entries with map number 1 and item numbers 1-10.


Solution

  • OpenForm doesn't include an option to specify the sort order. However you could use its OpenArgs option to pass in sort information, then apply that during form load.

    Private Sub Form_Load()
        If Not IsNull(Me.OpenArgs) Then
            Me.OrderBy = Me.OpenArgs
            Me.OrderByOn = True
        End If
    End Sub
    

    Then to open YourForm sorted by a field named id in ascending order ...

    DoCmd.OpenForm "YourForm", OpenArgs:="id"
    

    Include DESC for descending order ...

    DoCmd.OpenForm "YourForm", OpenArgs:="id DESC"
    

    Use this version of Form_Load to troubleshoot why the form opens without the sorting you expect.

    Private Sub Form_Load()
        MsgBox "Me.OpenArgs: " & Nz(Me.OpenArgs, "Null")
        If Not IsNull(Me.OpenArgs) Then
            Me.OrderBy = Me.OpenArgs
            Me.OrderByOn = True
        End If
        MsgBox "Me.OrderBy : '" & Me.OrderBy & "'"
        MsgBox "Me.OrderByOn: " & Me.OrderByOn
    End Sub