Replicatiing the Filter By function on a Custom Toolbar (1 Viewer)

[Robert.Finch]

Hi

I am trying to replicate the Filter By functionality on a custom popup (right click) toolbar. This is not specific to any form or any control on a form but allows a filter to be applied in any selected control.

I started by making the custom property load a filter criteria form called frmFilterBy.
The FilterBy form has a single unbound control named FilterFor.
The FilterFor control has a On Lost Focus event that hides the form then runs the public function FilterBy2

The FilterBy2 Function reads:
Option Compare Database
Public strFilter01 As String
Public strFilter02 As String
Public strFilter03 As String
Public strFilter04 As String
Public strFilter05 As String

strFilter02 = Screen.ActiveControl.ControlSource
strFilter01 = Screen.ActiveForm.Name
strFilter03 = Screen.ActiveForm.Name & "." & Screen.ActiveControl.Name
strFilter04 = Forms!frmfilterby.FilterFor
MsgBox strFilter01 (02,03,04) (this is just for development checking purposes)

DoCmd.FindRecord [Forms]![frmfilterby]![FilterFor], acEntire, False, , False, acCurrent, (True This finds the first matching record)

strFilter05 = Screen.ActiveControl.ControlSource & " Like " & [Forms]![frmfilterby]![FilterFor]
MsgBox strFilter05 (Check)
DoCmd.RunCommand acCmdRemoveFilterSort

Now the bit that dosn't work

Screen.ActiveForm.Filter = """ & Screen.ActiveControl.ControlSource & " Like " & Forms!frmfilterby.FilterFor & """
Screen.ActiveForm.FilterOn = True

or

Screen.ActiveForm.Filter = Screen.ActiveControl.ControlSource & " Like " & Forms!frmfilterby.FilterFor
Screen.ActiveForm.FilterOn = True

If I replace the Screen.ActiveControl.ControlSource with a form and control it works but I need to reference the current form and control not specific ones.

Help Please

 

[vbaInet]

You're trying to reinvent the wheel here, well, to an extent.

Questions you should ask yourself:

1. How do I identify the field's datatype. You need this in your Filter string. For example you wrap Text with a single or double quote whereas numbers are without.
2. How do I identify whether the selected control can be filtered? Example, a filter cannot be applied on an unbound control.
3. Would the LIKE operator be an effective search criteria? The filter is certainly going to be ineffecient if you're going to use LIKE for every filter so maybe you want to give the OP a choice of "Sounds Like" or "Contains" or "Is Equal To".
4. Following from point 3, you can already begin to see the volume of what you're attempting. Are you going to create a separate popup form that will allow the user choose certain parameters?
5. Last but not the least, is it worth the time? Is it a must have or just something you thought would be a nice feature?

My advice would be, use the filter options provided by Access such as filter by form or filter menu OR create a search pane for each form (where necessary).

Filter by form:
Docmd.RunCommand acCmdFilterByForm

Filter menu:
Docmd.RunCommand acCmdFilterMenu

 

[Robert.Finch]

Hi, thanks for the answer.

Yes it is worth it, I need the filtering functionality provided the full version of Access for my run-time version.

Filter by form, filter by selection and filter excluding selection are all good but limited but we often know part of a part number so it might be 207-25-00023 or 207*0023 or *-25-*, or we may know part of the description. In one form I may have 15,000 entries and I want to isolate just those parts with a description including "ring". I may want to have part numbers starting 07000, etc.

All this is easilly done on full Access on the right click, I need to replicate this on the toolbar. Yes I could write somethiong for every one of the 100+ forms each containig 15-50 controls but that seems extreme.

Surely someone has done this before?


You say I need to identify datatype, is there a function for this?

Thanks