Filtering a form: Making a blank filter to show all records

[CoZak]

Hey guys, I ve added comboboxes to a form, in order to filter a subform. However when I leave the combobox blank (I have added an option) or when no value was picked the subform is already filtered (when i open the form the subform is already filtered while comboboxes appear emtpy...), this way when i choose 1 combobox only to filter, for example age of line= 5 it doesnt show ALL records where age of line = 5 which is kinda annoying. For the filtering I used the built in macros after update event and the "SearchForRecord" action. I tried to add that when no value is selected then all filters are applied with AND (therefore no filter) but it isnt working. I ve attached a pic and the file so you can understand the issue better maybe.. I tried to use the filtering method where you filter the query directly from the criteria with the combobox is the form but then no records showed..Road_Marking_Database_51.zip

[ranman256]

build the 'where' clause by cycling thru all the controls....
it executes after a find button CLICK event
if null, ignore.
if not, apply.

'----------------
sub btnFilter_click()
'----------------
dim sWhere as string 

sWhere = "1=1"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"

If sWhere = "1=1" Then
  Me.FilterOn = False
Else
  Me.Filter = sWhere
  Me.FilterOn = True
End If
end sub

[CoZak]

Hi again ranman, thanks for the help but I get the error runtime error 3464 data type mismatch. The debugger shows the line Me.FilterOn = True
It s not related to the different data types (date, number etc) because I tried the code again with just numeric values and I still had the error.
Also will this show all records or just revert me to the way it looks when i open the subform? Since the subform il already filtered when I open it which is weird.

Here s the code if I made an error

Private Sub btn_clear_Click()
Dim sWhere As String


sWhere = "1=1"
If Not IsNull(cmb_kp) Then sWhere = sWhere & " and [KP]='" & cmb_kp & "'"
If Not IsNull(cmb_bound) Then sWhere = sWhere & " and [Bound]='" & cmb_bound & "'"
If Not IsNull(cmb_line) Then sWhere = sWhere & " and [Line]='" & cmb_line & "'"
If Not IsNull(cmb_applicationdate) Then sWhere = sWhere & " and [Application Date]='" & cmb_applicationdate & "'"
If Not IsNull(cmb_age) Then sWhere = sWhere & " and [Age of line when measured]='" & cmb_age & "'"




If sWhere = "1=1" Then
  Me.FilterOn = False
Else
  Me.Filter = sWhere
  Me.FilterOn = True
  
End If


End Sub

thanks for the help as always though

[ranman256]

this code must be in the subform, not a module.
the control sWheres in my example are all strings. you must alter for numerics.

[CoZak]

Ok i ll try that

[CoZak]

But how can my button be visible in a subform when it is displayed as a table?

[Gicu]

Replace Me with Me.sfrmControlName.Form (the name of your subform control) as in Me.sfrmControlName.Form.Filter = sWhere and place the code in the main form.

Cheers,
Vlad

[CoZak]

Ηι vlad, thanks for the info, but how can I have a visible control in a subform displayed as a table? In Design View i can see it but in Form View no. See pic on first post, the subform is location's Rl Qd measurements

[Gicu]

You can't (have buttons show on a datasheet form), that is why I suggested you move it to the main form. How do you apply the filter now? You need to replace the individual macros with Ranman2 code and place the button that runs it on the main form beside the combo-boxes.

Cheers,
Vlad

[CoZak]

Hey Vlad thanks for the help,

That s what I did, the code and the button is in the form, as well as the comboboxes (that work) but the filter reset button doesnt, I can see the code running (subform flickering) but nothing happens.
Since the subform seems already filtered when i open the form even though the comboboxes are empty will "resetting" the filters work??

CoZak

[Gicu]

Hi CoZak,

I would add Me.sfrmControlName.Form.Filter ="" to reset the filter and a Me.sfrmControlName.Form.Requery:

If sWhere = "1=1" Then
  Me.sfrmControlName.Form.Filter =""
  Me.sfrmControlName.Form.FilterOn = False
Else
  Me.sfrmControlName.Form.Filter = sWhere
  Me.sfrmControlName.Form.FilterOn = True
  
End If
Me.sfrmControlName.Form.Requery

You could also try to turn the filter off in the OnOpen event of the subform: Me.FIlterOn = False

Cheers,
Vlad

[CoZak]

Hey Vlad,

Thanks for the continuuous help, but it looks like it s not working, it s because I used Macros with the SearchForRecord instead of a filter right?

Regards,
CoZak

[CoZak]

Did I maybe do smth in order the subform to only show 5 records max?
Why cant it search for aaaaalll records on 12/12/17 (example) and not just a batch of them?

[CoZak]

Ok asking the question made me think, the query was also searching with the location ID which had at max 5 measurements but the ID was not shown so the query probably filtered the first result by ID ascending now this part is fixed, I ll keep trying with the filters

[CoZak]

The problem wasnt the filter.on = False it was the Link Master Fields and Link Child Fields
But now my graph doesnt work since before the Search Record would act on the query but now the

q_get_measurement_subform.Form.Filter = "[KP]= " & Str(Nz([Screen].[ActiveControl], 0))
q_get_measurement_subform.Form.FilterOn = True

I use only modifies the subform thus my graph doesnt change
Any idea how the graph can refer to a subform and not a table or query?