sort a subform by query

[Jone]

Hi everybody!
I have a form with a subform inside and I want to make the subform filtered
for all records, the field is called “purpose of pledge for filter†and it’s
a combo box.
let’s say I want to filter it for all records in that form to “Prizes†and I
have a query that filters it to prizes, how do I make that it should open
with that query?
I know when I want to open a normal form I write “DoCmd.OpenForm "Account",
, "SortByDonor"
What do I write here?

 

[code_monkey_number_9]

Not really sure that I'm understanding you here:

If you have a subform whose recordset you want to filter based on the values
a user selects from a combo box on the main form, then there is no need to
"open" the subform - it is already open when the main form is open. You
simply need to filter its recordset. One way to accomplish that is to attach
a procedure to the After Update event of the combo box:

Private Sub cmbFilter_AfterUpdate()

Dim strSQL as String
strSQL = "Select tblData.Data1, tblData.Data2 " _
& "From tblData " _
& "Where (((tblData.ID)=" & Nz(Me.cmbFilter.Column(0),0) & "));"

Me.subfrmResults.Form.Recordsource = strSQL

End Sub

If on the other hand you have a *related* form you want to open, filtered
based on values a user selects in a combobox in a different form, you can
still accomplish this with a procedure in the After Update event of the combo
box:

Private Sub cmbFilter_AfterUpdate()

DoCmd.OpenForm "subfrmResults", , , "[ID]=" & Nz(Me.cmbFilter.Column(0),0)

End Sub

OR

Private Sub cmbFilter_AfterUpdate()

Select Case Me.cmbFilter.Column(1)
Case "Prizes"
DoCmd.OpenForm "subfrmResults", , qryNameOfSavedQuery1
Case "Balloons"
DoCmd.OpenForm "subfrmResults", , qryNameOfSavedQuery2
End Select

End Sub

hth.

 

[Jone]

No I mean I want to make before that form opens it should ask me how do you
want to open this form?
1= Sorted by prizes
2 = sorted by games
(I know how to make the input box) but I want to know how to sort the
subform in that form to let's say 1 that = to Sorted by prizes, & every
record in that form will be sorted in that subform to "prizes"

 

[code_monkey_number_9]

Easy enough:

In the combobox's After Update event do something like this:

Private Sub cmbFilter_AfterUpdate()

Dim strOpenArgs as String
Select Case Me.cmbFilter.Column(1)
Case "Prizes"
strOpenArgs = "Prizes"
Case "Games"
strOpenArgs = "Games"
End Select

DoCmd.OpenForm, "frmResults", , , , , , strOpenArgs

End Sub

Then in the resulting form's Open Event, do this:

Private Sub Form_Open(Cancel As Integer)

Me.OrderBy = Nz(Me.OpenArgs, "")

End Sub

 

[Jone]

I have another 20 items do I have to write all the items?

 

[code_monkey_number_9]

That is going to depend on the contents of your combo box. If one of the
columns in the table/ query (or value list) that provides values for your
combo box contains the field names of the table or query that underlies the
results form, you don't need the Select Case...

For example, if your combo box is filled by value list, and that list is:
"Prizes"; "Sort by Prizes"; "Games"; "Sort by Games"

you could instead launch your form thus:
DoCmd.OpenForm "frmResults", , , , , , Me.cmbFilter.Column(0)

The same is true if your combo box is filled with a query/ table, so long as
one of the columns contains the field name you are passing to the result
forms OrderBy:

"Select FieldName, SortOrder, Description From tblSortOrders Order by
FieldName;"

strOpenArgs = Me.cmbFilter.Column(0) & " ' & Me.cmbFilter.Column(1)
DoCmd.OpenForm "frmResults", , , , , , strOpenArgs

So there are a number of ways you can accomplish this; all you are really
doing is building an "Order by" clause to apply to the opening forms OrderBy
property.

BTW, in the last example above I've used two columns to construct that Order
By clause. You don't have to do this this way; it is just one way to
approach adding a sort order, like, Games DESC or Prizes ASC