Filtered Records (A2K)

Topic

New Reply

This is a routine that works perfectly and resides within a form called: frmCNMaster

In frmCNMaster I have a List box called: lstBox

I have a control button called: cmdFilter

Private Sub cmdFilter_Click()
‘ This one allows multiple selections
Dim varItem As Variant
Dim strWhere As String
For Each varItem In Me.lstSelect.ItemsSelected
strWhere = strWhere & “, ” & Me.lstSelect.ItemData(varItem)
Next
If Not strWhere = “” Then
strWhere = Mid(strWhere, 3)
strWhere = “F00_Function In (” & strWhere & “)”
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

I have a 2nd command button called: FKS_99

Private Sub FKS_99_Click()

Reply | Quote

Replies

You could use OpenArgs in combination with code in the On Load event of the form you open. But it is simpler to specify the WhereCondition argument:

Private Sub cmdFilter_Click()
‘ This one allows multiple selections
Dim varItem As Variant
Dim strWhere As String
For Each varItem In Me.lstSelect.ItemsSelected
strWhere = strWhere & “, ” & Me.lstSelect.ItemData(varItem)
Next
If Not strWhere = “” Then
strWhere = Mid(strWhere, 3)
strWhere = “F00_Function In (” & strWhere & “)”
End If
DoCmd.OpenForm FormName:=”frmCNMaster”, WhereCondition:=strWhere
End Sub

I don’t understand your other code. If FKS_99 is a command button, what is the meaning of … F00_Function < " & Me.FKS_99

Reply | Quote

Hans,

I know you keep hearing something similar to this but, Bang On as Usual!

I was almost close in my assessment, but closies don’t cound except in horseshoes.

The other code with respect to Function, etc. was used to reset the filtered data while still in the form. This was necessary when the data was being filtered within the form. Now that I can apply the filter from outside of the form, it is automatically reset when closed and reopened at a later time. I know what I mean in spite of what it sounds like.

Bottom line, again, thank you for your help,

Reply | Quote

Hans,

I thought of something while working on this. I

Reply | Quote

Will users be content with sorting on a single field, or do you want to offer the capability to specify several fields to sort on (say, first sort on FieldA, then on FieldB, then on FieldC)?

Reply | Quote

Hans,

You have to know that there will always be someone who will want to see multiple field choices such as Inventory Part Numbers by Date Received into Stock sequence just to aggravate. I’d be appreciative to see both single and multiple solutions of the same problem just for learning purposes and for sure anyone else looking in on this thread. It might save someone else aggravation as well. Either solution would, as I said, would be appreciated.

Reply | Quote

You could put a combo box cboSort on your search form with Row Source Type set to Field List and Row Source set to qry CNMstr.
Change the code to open frmCNMaster to

DoCmd.OpenForm FormName:=”frmCNMaster”, WhereCondition:=strWhere
If Not IsNull(Me.cboSort) Then
With Forms!frmCNMaster
.OrderBy = Me.cboSort
.OrderByOn = True
End If

See my next reply for multiple sort capability.

Reply | Quote

The attached database demonstrates how you can provide sorting on multiple fields, with an ascending/denscending choice for each. The demo is based on a Microsoft example for reports; I adapted it for forms. Open frmOrderBy to test it, then look at the code behind the form.

Reply | Quote

Hans,

Many thanks for both replies. This will give me more than enough to while away the rest of this sunshiny weekend. Hope you get to enjoy yours. I’ll let you know how this works out,

Reply | Quote

Hans,

Nice weather or not, I couldn’t wait for the weekend to be over in order to try this out, so here

Reply | Quote

It should be easy to combine the two approaches. For example, copy the part from the first procedure that assembles strWhere into the second procedure, and add a WhereCondition argument to DoCmd.OpenForm.

Reply | Quote

Hans,

Thanks kindly for pointing me in the right direction. I’ll be on this tonight or tomorrow. I’ll post it when I get it going correctly. Or, maybe might have an intermediary question.

Thanks again,

Reply | Quote

Hans,

Can

Reply | Quote

Good for you!

Reply | Quote

Dear Hans;
In a previous Post you provided code to use when using combo boxes to filter the records in a subform. This works great; however the selection criteria is not passed on to the subform. So when I print the a report based on the subform; the report contains all the records within the table. While the subform displays only the records based on the combo box selected. How do you pass on the combo box value to the FilterOn on the subform. Great Code for being able to allow multiple sorting values; how about code to allow multiple items for filtering?

Reply | Quote

Please explain more clearly, and/or provide an example. I’m afraid I don’t understand what you’re talking about.

Reply | Quote