filter on a form only works once

[.]

ms access 2007,
using a simple filter to show certain records, based on value in a combobox

what happens:
1) form loads, all records are visible-------good
2) a command button is used to set a filter based on a value in a
ombobox ----- works correctly the first time
3) next time the filter is run, new filter displays results from the first
filter instead of the second filter request....

DoCmd.ShowAllRecords
Me.Refresh
Me.Requery
Me.Repaint
DoCmd.ApplyFilter , "invoicenumber=[Forms]![Purchases
Entry]![TB_invoicenumber]"

i've been around the web, and havent found a solution for this problem, so
it seems like i'm just doing something stupid...

 

[Jeanette Cunningham]

Hi,
to clear or remove a form's filter use

Me.FilterOn = False


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[.]

i tried it
i added your line to the beginning of the command buton procedure, but it
still behaves the same way.
the filter works correctly the first time, but in the same session the 2nd
filter request returns records from the first filter.

eg// load the form, see all records
generate a filter to see all records with invocie#="001".......works
good
next turn off the filtering (me.filteron=false),
showallrecords..........works good
next generate a filter to see all records with
invoice#-"002"..........returns records belonging to invoice# "001"

what's up with this???

Hi,
to clear or remove a form's filter use

Me.FilterOn = False


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

ms access 2007,
using a simple filter to show certain records, based on value in a
combobox

what happens:
1) form loads, all records are visible-------good
2) a command button is used to set a filter based on a value in a
bobox ----- works correctly the first time
3) next time the filter is run, new filter displays results from the
first filter instead of the second filter request....

DoCmd.ShowAllRecords
Me.Refresh
Me.Requery
Me.Repaint
DoCmd.ApplyFilter , "invoicenumber=[Forms]![Purchases
Entry]![TB_invoicenumber]"

i've been around the web, and havent found a solution for this problem,
so it seems like i'm just doing something stupid...

 

[Beetle]

What is the name of the combo box you are getting the filter value from?
--
_________

Sean Bailey

i tried it
i added your line to the beginning of the command buton procedure, but it
still behaves the same way.
the filter works correctly the first time, but in the same session the 2nd
filter request returns records from the first filter.

eg// load the form, see all records
generate a filter to see all records with invocie#="001".......works
good
next turn off the filtering (me.filteron=false),
showallrecords..........works good
next generate a filter to see all records with
invoice#-"002"..........returns records belonging to invoice# "001"

what's up with this???

Hi,
to clear or remove a form's filter use

Me.FilterOn = False


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

ms access 2007,
using a simple filter to show certain records, based on value in a
combobox

what happens:
1) form loads, all records are visible-------good
2) a command button is used to set a filter based on a value in a
bobox ----- works correctly the first time
3) next time the filter is run, new filter displays results from the
first filter instead of the second filter request....

DoCmd.ShowAllRecords
Me.Refresh
Me.Requery
Me.Repaint
DoCmd.ApplyFilter , "invoicenumber=[Forms]![Purchases
Entry]![TB_invoicenumber]"

i've been around the web, and havent found a solution for this problem,
so it seems like i'm just doing something stupid...

 

[Ken Sheridan]

Instead of using the ApplyFilter method of the DoCmd object set form's Filter
property:

Dim strFilter As String

strFilter = "invoicenumber = " & _
[Forms].[Purchases Entry].[TB_invoicenumber]

Me.Filter = strFilter
Me.FilterOn = True

Note that the value of the TB_invoicenumber control is concatenated into the
string, rather than a reference to the control being a part of the literal
string. I've assumed above that the invoicenumber column is of a number data
type. If its of a text data type then wrap the value in quotes characters:

strFilter = "invoicenumber = """ & _
[Forms].[Purchases Entry].[TB_invoicenumber] & """"

A pair of contiguous quotes within quotes is interpreted as a single quotes
character.

If you want a 'Show All' button to revert the form to showing all records
you just need:

Me.FilterOn = False

in its Click event procedure.

Ken Sheridan
Stafford, England

i tried it
i added your line to the beginning of the command buton procedure, but it
still behaves the same way.
the filter works correctly the first time, but in the same session the 2nd
filter request returns records from the first filter.

eg// load the form, see all records
generate a filter to see all records with invocie#="001".......works
good
next turn off the filtering (me.filteron=false),
showallrecords..........works good
next generate a filter to see all records with
invoice#-"002"..........returns records belonging to invoice# "001"

what's up with this???

Hi,
to clear or remove a form's filter use

Me.FilterOn = False


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

ms access 2007,
using a simple filter to show certain records, based on value in a
combobox

what happens:
1) form loads, all records are visible-------good
2) a command button is used to set a filter based on a value in a
bobox ----- works correctly the first time
3) next time the filter is run, new filter displays results from the
first filter instead of the second filter request....

DoCmd.ShowAllRecords
Me.Refresh
Me.Requery
Me.Repaint
DoCmd.ApplyFilter , "invoicenumber=[Forms]![Purchases
Entry]![TB_invoicenumber]"

i've been around the web, and havent found a solution for this problem,
so it seems like i'm just doing something stupid...

 

[.]

i tried both syntaxes , and got error both times---object does not support
this property or method-----
using these:
strFilter = "invoicenumber = " & [Forms].[Purchases
Entry].[TB_invoicenumber]
strFilter = "invoicenumber = """ & [Forms].[Purchases
Entry].[TB_invoicenumber] & """"

finally.....it wanted '!' instead of periods
works better now
thank you for the help

Instead of using the ApplyFilter method of the DoCmd object set form's
Filter
property:

Dim strFilter As String

strFilter = "invoicenumber = " & _
[Forms].[Purchases Entry].[TB_invoicenumber]

Me.Filter = strFilter
Me.FilterOn = True

Note that the value of the TB_invoicenumber control is concatenated into
the
string, rather than a reference to the control being a part of the literal
string. I've assumed above that the invoicenumber column is of a number
data
type. If its of a text data type then wrap the value in quotes
characters:

strFilter = "invoicenumber = """ & _
[Forms].[Purchases Entry].[TB_invoicenumber] & """"

A pair of contiguous quotes within quotes is interpreted as a single
quotes
character.

If you want a 'Show All' button to revert the form to showing all records
you just need:

Me.FilterOn = False

in its Click event procedure.

Ken Sheridan
Stafford, England

i tried it
i added your line to the beginning of the command buton procedure, but it
still behaves the same way.
the filter works correctly the first time, but in the same session the
2nd
filter request returns records from the first filter.

eg// load the form, see all records
generate a filter to see all records with
invocie#="001".......works
good
next turn off the filtering (me.filteron=false),
showallrecords..........works good
next generate a filter to see all records with
invoice#-"002"..........returns records belonging to invoice# "001"

what's up with this???

Hi,
to clear or remove a form's filter use

Me.FilterOn = False


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


ms access 2007,
using a simple filter to show certain records, based on value in a
combobox

what happens:
1) form loads, all records are visible-------good
2) a command button is used to set a filter based on a value in a
bobox ----- works correctly the first time
3) next time the filter is run, new filter displays results from the
first filter instead of the second filter request....

DoCmd.ShowAllRecords
Me.Refresh
Me.Requery
Me.Repaint
DoCmd.ApplyFilter , "invoicenumber=[Forms]![Purchases
Entry]![TB_invoicenumber]"

i've been around the web, and havent found a solution for this
problem,
so it seems like i'm just doing something stupid...