Combine a Filter between vStDate and vEndDate (unbound txt boxes) on a form -w- a multi-select list box that filters by tEvType (lookup field Auto Ke) (1 Viewer)

kengooch

Member
Local time
Today, 06:32
Joined
Feb 29, 2012
Messages
137
So I have a form that I need to be able to filter by a range of dates and by a selected value in a multi-select list box (actually using only one value at a time)
vStDate and vEndDate are unbound text boxes tied to the Date Picker in access. tEvType is a look up field that that gets its values from a table named tEvntTypes with two fields, tEvTypeID (AutoNumber) and tEvType (text field that describes the type)
tEvntTypes.PNG
fVaxEvntCnt.PNG


The code to manage the Date filter is:
DateFilterPic.PNG


The multiselect list box can handle 5 boxes, but at this juncture I only use the one. Here is the code for it.
'= = = = = = = = = = = = = = = = = = = = = = = = = = = = =
'MULTI SELECT LIST BOXES - Referred to as MSL
'This code handles the Multi select list Boxes that allow interactive data selection
'Last Updated 08/14/2020
'This code adjusts the Form View according to the Specified MSLB Selections
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'Terminology and Notes
' msl = Multi Select List box
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'Form Fields being Filtered
' tField1 = tEvType 'This is another issue, I have tried to set the MSL's up so I can just change the forms field name but it errors out.
' tField2 = tYourField2
' tField3 = tYourField3
' tField4 = tYourField4
' tField5 = tYourFiled5
'(Search and Replace the following values for the Form you apply this to.'Search and Replace the following values for the Form you apply this to.)
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'This code handles the First List Box

Private Sub mslBox1_Click()
Me.Form.Filter = vSetFilters
Me.Form.FilterOn = True
Me.Form.Refresh
MsgBox "Pause" 'The above code filters perfectly

'The next line is where I am trying to combine the two filters into one that does both.

Me.Form.Filter = vSetFilters & vDateRng 'vDateRng is assigned in the Date filter portion.
Me.Form.FilterOn = True
Me.Form.Refresh
End Sub
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'This code handles the Second List Box

Private Sub mslBox2_Click()
Me.Form.Filter = vSetFilters
Me.Form.FilterOn = True
... Code Removed for Brevities sake
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'This code handles the Fourth List Box

Private Sub mslBox5_Click()
Me.Form.Filter = vSetFilters
Me.Form.FilterOn = True
Me.Form.Refresh
End Sub
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'Multi-Select List Boxes
'Last Modified 08/14/2020
'This code adjusts the Form View according to the Specified Selections
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Public Function vSetFilters() As String
On Error GoTo vSetFilters_Err
' Assign Variables
Dim vIndvSel As String
Dim varItem As Variant
Dim vListBox1, vListBox2, vListBox3, vListBox4, VListBox5 As Control
'Define List Boxes
Set vListBox1 = Me.mslBox1
Set vListBox2 = Me.mslBox2
Set vListBox3 = Me.mslBox3
Set vListBox4 = Me.mslBox4
Set VListBox5 = Me.mslBox5
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'1st Multi Select List Box
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Select Case vListBox1.ItemsSelected.Count
Case Is = 1
If Len(vIndvSel) >= 1 Then vIndvSel = vIndvSel & " AND "
vIndvSel = vIndvSel & "ABS([tEvntType])='"
For Each varItm In vListBox1.ItemsSelected
vIndvSel = vIndvSel & vListBox1.ItemData(varItm) & "'"
Next varItm
Case Is > 1
If Len(vIndvSel) >= 1 Then vIndvSel = vIndvSel & " AND "
vIndvSel = vIndvSel & "ABS([tEvntType]) IN ("
i = 0
For Each varItm In vListBox1.ItemsSelected
If i > 0 Then
vIndvSel = vIndvSel & ", "
End If
i = i + 1
vIndvSel = vIndvSel & Chr(34) & vListBox1.ItemData(varItm) & Chr(34)
Next varItm
vIndvSel = vIndvSel & ") "
End Select
'Assign String to Isolate Records
vSetFilters = vIndvSel
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'2nd Multi Select List Box
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Select Case vListBox2.ItemsSelected.Count
Case Is = 1

...... code for 2 - 4 Removed for brevities sake.

'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'5th Multi Select List Box
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Select Case VListBox5.ItemsSelected.Count
Case Is = 1
If Len(vIndvSel) >= 1 Then vIndvSel = vIndvSel & " AND "
vIndvSel = vIndvSel & "[tField5]='"
For Each varItm In VListBox5.ItemsSelected
vIndvSel = vIndvSel & VListBox5.ItemData(varItm) & "'"
Next varItm
Case Is > 1
If Len(vIndvSel) >= 1 Then vIndvSel = vIndvSel & " AND "
vIndvSel = vIndvSel & "[tField5] IN ("
i = 0
For Each varItm In VListBox5.ItemsSelected
If i > 0 Then
vIndvSel = vIndvSel & ", "
End If
i = i + 1
vIndvSel = vIndvSel & Chr(34) & VListBox5.ItemData(varItm) & Chr(34)
Next varItm
vIndvSel = vIndvSel & ") "
End Select
'Assign String to Isolate Records
'This code handles the Fifth List Box
vSetFilters = vIndvSel
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
vSetFilters_Exit:
Exit Function
vSetFilters_Err:
Select Case Err
Case Is = 2501
Resume Next
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume vSetFilters_Exit
End Select
End Function
= = = = = = = = = = = = End of Code

So I use vIndvSel to capture each click of the MSL box. Then assign those to vSetFilters. Once all boxes are checked I use the concatenated filters in vSetFilters to filter the form.

So... I can either filter at the Me.Form.Filter = vSetFilters and try to figure out how to add on the Date filter
Me.Form.Filter = vSetFilters & vDateRng '(This doesn't work)
This is where I would prefer to set the filter...

- OR -

For the Date Filter, the following Code works.
Me.Filter = "[tEvntDate] BETWEEN #" & Me.vStDate & "# AND #" & Me.vEndDate & "# AND [tEvType] = 'Reaction'"
(Notice I hard coded one of the vEvType which is the description of the vEvTypeID and it filters correctly)

So... Does anybody know what the syntax would be to put append the tEvType or tEvTypeID together with the date filter vSetFilters. In other words, I can't figure out the Syntax to replace the hard coded 'Reaction'" with the MSL vSetFilters
 

Ranman256

Well-known member
Local time
Today, 09:32
Joined
Apr 9, 2015
Messages
4,339
don't use multi select listboxes, it requires programming.
Instead use tPicked table for user to dbl-click the list box adding the item to the tPicked table.
Join this table to your query to get only those items. NO programming, 1 append query.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:32
Joined
Feb 19, 2002
Messages
42,981
Dim vListBox1, vListBox2, vListBox3, vListBox4, VListBox5 As Control
This is only dimming the last item as a control. The first four will all be variants. You must do one at a time in VBA.

When you past code, PLEASE use the code tool so it is indented properly. It is ever so much easier to read. If you don't indent your code, now would be a good time to start.

If you want to use multi-select list boxes, you need to collect the selections and use them in an In() clause and you can only do this if you are building your own SQL or filter.

And finally, I NEVER use filters, mostly because my BE's are almost always SQL Server or if they don't start out that way, they tend to end up there later and filtering forms is far less efficient when your BE is ODBC.

Here is a sample that shows you how to use a multi-select listbox in a query or as a filter.
 

Attachments

  • FillFormFields20210105.zip
    101.4 KB · Views: 269

kengooch

Member
Local time
Today, 06:32
Joined
Feb 29, 2012
Messages
137
don't use multi select listboxes, it requires programming.
Instead use tPicked table for user to dbl-click the list box adding the item to the tPicked table.
Join this table to your query to get only those items. NO programming, 1 append query.
I am not familiar with this concept. Could you expand or give an example? thanks so much! :)
 

kengooch

Member
Local time
Today, 06:32
Joined
Feb 29, 2012
Messages
137
This is only dimming the last item as a control. The first four will all be variants. You must do one at a time in VBA.

When you past code, PLEASE use the code tool so it is indented properly. It is ever so much easier to read. If you don't indent your code, now would be a good time to start.

If you want to use multi-select list boxes, you need to collect the selections and use them in an In() clause and you can only do this if you are building your own SQL or filter.

And finally, I NEVER use filters, mostly because my BE's are almost always SQL Server or if they don't start out that way, they tend to end up there later and filtering forms is far less efficient when your BE is ODBC.

Here is a sample that shows you how to use a multi-select listbox in a query or as a filter.
Thanks for the samples, they are great. But I would still like to know how to join these two techniques into one filter. Is it possible? Also, sorry about the pasting of the code. I wasn't aware that there was a feature to paste code.
 

kengooch

Member
Local time
Today, 06:32
Joined
Feb 29, 2012
Messages
137
I have a query that can accomplish this process by prompting the user for input of the type and dates, but it provides no means of picking from the list of values in the EvTypes table. I guess there must be a way to filter a form by a query too. But if someone knows the syntax to pull these two Filters together I would be ever so grateful!!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:32
Joined
Feb 19, 2002
Messages
42,981
I'm not sure I understand the question. The criteria would be the same whether you are using a query or a filter.

SomeDate Between Forms!yourform!FromDate AND Forms!yourform!ThruDate AND SomeField In("aa","bb","cc")

You have to build this string using VBA since you can't use something like - In(variable) If you passed the sample from above, it would end up as one variable NOT three - In(' "aa","bb","cc" ')I put extra spaces so you can see what happens.
 

Users who are viewing this thread

Top Bottom