command button: set a default filter & filter on load

[rodrigoturbina]

Hi everybody, hope you are doing fine.

I have a continuous form based on table "INCOMES" that shows all the payments received, which mediums can be (field "PMNT_MEDIUM"):
- check
- transfer
- taxes
- cash

Table "INCOMES" is filled using another form, but in this particular form I just want to show "check", "transfer" and "cash" (not "taxes") so that I can track all the cash incomes.

Note: taxes are loaded because they appear in my invoices and I need them there to reach the invoice total amount; although I don´t really receive money for those taxes.

So my form has a search bar which allows me to search by PMNT_MEDIUM listing all "checks", all "cash" or all "transfer". I can also search by payment number (meaning: check number). To that end I have a "search" button that applies the filter. And I have another button that "cleans" the filtering by "putting a "" in the search-bar and then calling the "on click" of the search button".

What I need is, no matter if I click over the "search" or "clean" button, it NEVER shows me the "taxes".

Search button, on click code:

If IsNumeric(Me.busq_chq_med) Then
Me.Filter = "[PMNT_MEDIUM_NUMB] =" & Me.SEARCH_BAR
Else
Me.Filter = "[PMNT_MEDIUM] like'" & Me.SEARCH_BAR & "*'"
Me.Filter = "[PMNT_MEDIUM] like'" & Me.SEARCH_BAR & "*' or [INVOICE] like'" & Me.SEARCH_BAR & "*'"
End If
Me.FilterOn = True

Clean filter button, on click code:

[SEARCH_BAR] = ""
Call [Search button]_click
Me.Filter = "[PMNT_MEDIUM] like'" & Me.SEARCH_BAR & "*'"
Me.FilterOn = True

Thanks so much,
Rod

 

[vbaInet]

If you want to remove the filter you need to turn it off, True to False.

 

[rodrigoturbina]

I don´t want to remove the filter, I want:
1) form on load: show me all records, but the "taxes";
2) when I enter something in the search bar: only show me the entered value (if exists, otherwise, no results);
3) when I click over the "clean" button: show me all records but the "taxes", as in (1).

Sorry for not being clear enough!!

 

[vbaInet]

I thought you said the searching already works? So based on the list what actually works?

 

[rodrigoturbina]

Everything works, but when I "clean" the filter it does not "clean" the taxes... just puts a "" in the search bar, and calls the "search" button, showing all records.

1) I would like to auto-filter on form load based on [PMNT_MEDIUM] not equal to "taxes" but I don´t know how to syntax it when I enter it in the form properties and set the "filter on load" to "yes"... [PMNT_MEDIUM] not like "taxes"

2) this already works, since it filters what I enter there, so won´t show the taxes unless I voluntairly enter "taxes"... which is not a problem.

3) when I click over the "clean" button, I would like the form to show me the records as when just loaded, calling the (1) property: show everything but "taxes".

 

[vbaInet]

So you have the filter already when the form loads right? Copy it and save it in a variable. When you need it again, get the value from the variable.

 

[rodrigoturbina]

I don´t know how to write it in the code for the "clean filter" button... It is a syntax problem, I am not programmer.

Or perhaps I can create the button again with a different instruction: do a form reload (is this a requery? or a refresh?) so that it uses the "filter on load" property;

Or just add to the clean filter button something that says "clean the current filter and apply the "standard filter I do want" (which is "everything BUT taxes")

In both cases I don´t know how to do it... sorry man!

 

[vbaInet]

Didn't you write the code you used for the filter?

You don't know how to declare a variable or you don't know how to get the filter value?

 

[rodrigoturbina]

1) filter on load property works, indeed:

form properties, data tab, filter field:

[form name].[PMNT_MEDIUM]<>"taxes" AND [form name].[PMNT_MEDIUM]<>"taxes2"

form properties, data tab, filter on load: YES

========

2) clean filter button, I replaced my "rudimentary" code with the easier:

me.filteron = false

But this shows me all records instead of "all but ("tax1" nor "tax2")... how do I write this?

btw, I also tried filtering "on the form", then going to the properties, and copying "the syntax access wrote in the filter field"... but it did not work.

 

[vbaInet]

You've re-iterated your problem several times when you don't really need to because I've already told you what to do. I asked if you know how to declare a variable or not?

 

[rodrigoturbina]

Note: when I say "taxes" or "tax1" or "tax2" it is the same for instance... what I do have in the field is not actually taxes but "Ret IIBB" or "Ret IIGG" (which are local country taxes). These are the 2 f... values I don´t want to be displayed

 

[rodrigoturbina]

dim xxxx as (variant, string, date) is what I know...

 

[vbaInet]

Excellent!

1. Declare a variable in the declarations section of your form, i.e. just below this:

Option Compare Database
[COLOR="Blue"]Option Explicit

Private strFilter As String[/COLOR]

strFilter is now your string variable.

2. Go to the Load event of your form and do this:

strFilter = Me.Filter

3. In the Clear button's click event just do this:

Me.Filter = vbNullString
Me.Filter = strFilter
Me.FilterOn = True

 

[rodrigoturbina]

Well I have done so for 2 and 3... for 1 I don´t know if I did it in the right place: I went to the main menu in access, "create" tab, then "module" and when "Option Compare Database" appeared, I pasted the rest:

Option Explicit
Private strFilter As String

was it right? cause you mention to do it "in the declarations section of your form" but I don´t know if I did it "in my form" or "somewhere else in the whole database"...

The module was saved as "Módulo 3"... Thanks... wait for more instructions

 

[vbaInet]

It's in the wrong place. It needs to go in the same code as your form.

 

[rodrigoturbina]

Now I think I did it right, went to the form design view and then "view code" and added

Option Explicit
Private strFilter As String

Now when I hit the "clean button" it filters what I had filtered (which was another filter I was using) when I set that up... how do I set it up for filtering everything but "Ret IIBB" or "Ret IIGG"? (we are almost done I guess)

 

[rodrigoturbina]

solved... Re-setup the filter on load criteria and saved, closed and reopen de form... it´s working.

On load: everything but "Ret IIGG" nor "Ret IIBB"
When I click over "clean search": everything but "Ret IIGG" nor "Ret IIBB"

Now if I put "Ret..." in the search bar, it shows me those records, but I can "clean search" and it works back again.

Thanks so much, and I apologize for not being easy to learn this... not a good day today

 

[vbaInet]

Good to hear!