Creating a Sort combo box

[Arielthealpaca]

Hi everyone!

I am about to finish my first semester working with access and am very new to this.
One of my requirements for my final project is to create and Advanced/Filter/Sort Form. I already create a Filter combo box, where you can select what video game system you want to view the games for. I want to add another combo box with a label “Sort By” and have the drop down options be “Release Date Ascending” and “Release Date Descending”. I did create a query that has the ReleaseDate field and this query is the forms record source. I have entered so many different codes into the combo box’s record source and the VBA code on the “on event” tab in the property sheet and I have no idea what to do. I’ve been working at this for six hours.

 

[GaP42]

There are many ways in which the sort order on a form can be changed using different types of controls - eg a toggle, or by clicking on the heading of a column in a continuous form - to some extent it is about user preference. However using a combo box as you have proposed you need to use the AfterUpdate Event of the cbo. On Change you need to set up something like:

Me.OrderByOn = True

If me.cboVideoSort = "Release Date Ascending" then     ' assuming you have this as a value list
    Me.OrderBy = "[ReleaseDate] ASC"                              ' assuming ReleaseDate is the name of the column that sorting needs to be applied
Else                                                                                    ' option Release Date Descending was selected
    Me.OrderBy = "[ReleaseDate] DESC"
End If

Me.Refresh

Untested air code

Good luck

 

[June7]

If you want to programmatically filter and/or sort, can build an entire SQL statement and change RecordSource property or apply just the WHERE or SORT criteria to form properties: Filter, FilterOn, OrderBy, OrderByOn.
Review http://allenbrowne.com/ser-62.html

 

[The_Doc_Man]

For the simplest cases (single-field order, single-field filter), the most efficient method would be to build the Filter or OrderBy clauses as strings and apply them as June7 suggests. (So essentially, I am seconding June7's post.) Just remember to set the OrderByOn or FilterOn to TRUE after you load the new filter or order by criteria.