Form record filtering not working

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two combo boxes (for account # and account name) on a user input form
from which users can select a record (an account) in the underlying table. I
want to filter out one record (account #1) so this one cannot be selected. I
have tried putting "[AccountNo] > 1" in the Filter property of the form, but
this is being ignored. Do I need to do something in the form's code instead,
or in addition to this?
 
You need to set the form's FilterOn property as well, so the filter is
applied.

In the form's Open event procedure:
Me.Filter = "[AccountNo] > 1"
Me.FilterOn = True

It might be easier to make a query, put your criteria in there, and then use
the query as the RecordSource for the form. Then the user can't just remove
the filter.
 
It would probably be better to limit the combo box, not the form
itself. Make the Row Source of the combobox something like:
Select AccountNumber
From YourTableName
Click to expand...
Where AccountNumber > 1

By the way, the reason it is not working the way you have it is because
you have to tell Access to apply the filter you have set. There should
be an icon in the toolbar to apply the filter or you can click Records,
Apply Filter. You can also apply it via some programming code with
Me.FilterOn = True.

Hope that helps!
 
Thanks - this works. As it happens, I just found another example in my own
code where I used a WHERE clause in a form open statement to filter records.
Doing it that way apparently doesn't require setting FilterOn.

However, another question comes up ... Apparently related to the combo
boxes, the account being filtered out actually still appears in the drop down
list of the combo boxes even though the user can't select it. Is there a way
to prevent it from even showing in the drop down list, which is what I would
have expected filtering to do in the first place?

ctdak


Allen Browne said:
You need to set the form's FilterOn property as well, so the filter is
applied.

In the form's Open event procedure:
Me.Filter = "[AccountNo] > 1"
Me.FilterOn = True

It might be easier to make a query, put your criteria in there, and then use
the query as the RecordSource for the form. Then the user can't just remove
the filter.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

ctdak said:
I have two combo boxes (for account # and account name) on a user input
form
from which users can select a record (an account) in the underlying table.
I
want to filter out one record (account #1) so this one cannot be selected.
I
have tried putting "[AccountNo] > 1" in the Filter property of the form,
but
this is being ignored. Do I need to do something in the form's code
instead,
or in addition to this?
Click to expand...
Click to expand...
 
Yes, when you OpenForm with a WhereCondition, Access does set FilterOn.
Unfortunately, it is not consistent at setting the FilterOn property when
you use a WhereCondtion with OpenReport (though the filter works fine.)

You need to change the RowSource of the combo as well. Create a string that
is a SQL statement, and include a WHERE clause to exclued the AccountNo.

This kind of thing patches the form's Filter in between the stub of the SQL
statement and the tail:
Const strcStub = "SELECT AccountNo, AccountName FROM tblAccount "
Const strcTail = " ORDER BY AccountName;"
Dim strWhere As String
If Me.FilterOn Then
strWhere = "WHERE " & Me.Filter
End If
Me.Combo1.RowSource = strcStub & strWhere & strcTail

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

ctdak said:
Thanks - this works. As it happens, I just found another example in my
own
code where I used a WHERE clause in a form open statement to filter
records.
Doing it that way apparently doesn't require setting FilterOn.

However, another question comes up ... Apparently related to the combo
boxes, the account being filtered out actually still appears in the drop
down
list of the combo boxes even though the user can't select it. Is there a
way
to prevent it from even showing in the drop down list, which is what I
would
have expected filtering to do in the first place?

ctdak


Allen Browne said:
You need to set the form's FilterOn property as well, so the filter is
applied.

In the form's Open event procedure:
Me.Filter = "[AccountNo] > 1"
Me.FilterOn = True

It might be easier to make a query, put your criteria in there, and then
use
the query as the RecordSource for the form. Then the user can't just
remove
the filter.

ctdak said:
I have two combo boxes (for account # and account name) on a user input
form
from which users can select a record (an account) in the underlying
table.
I
want to filter out one record (account #1) so this one cannot be
selected.
I
have tried putting "[AccountNo] > 1" in the Filter property of the
form,
but
this is being ignored. Do I need to do something in the form's code
instead,
or in addition to this?
Click to expand...
Click to expand...
Click to expand...
 
Thanks Allen. This combination of the OpenForm Where clause and the combo
box Where clause did the trick.
ctdak


Allen Browne said:
Yes, when you OpenForm with a WhereCondition, Access does set FilterOn.
Unfortunately, it is not consistent at setting the FilterOn property when
you use a WhereCondtion with OpenReport (though the filter works fine.)

You need to change the RowSource of the combo as well. Create a string that
is a SQL statement, and include a WHERE clause to exclued the AccountNo.

This kind of thing patches the form's Filter in between the stub of the SQL
statement and the tail:
Const strcStub = "SELECT AccountNo, AccountName FROM tblAccount "
Const strcTail = " ORDER BY AccountName;"
Dim strWhere As String
If Me.FilterOn Then
strWhere = "WHERE " & Me.Filter
End If
Me.Combo1.RowSource = strcStub & strWhere & strcTail

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

ctdak said:
Thanks - this works. As it happens, I just found another example in my
own
code where I used a WHERE clause in a form open statement to filter
records.
Doing it that way apparently doesn't require setting FilterOn.

However, another question comes up ... Apparently related to the combo
boxes, the account being filtered out actually still appears in the drop
down
list of the combo boxes even though the user can't select it. Is there a
way
to prevent it from even showing in the drop down list, which is what I
would
have expected filtering to do in the first place?

ctdak


Allen Browne said:
You need to set the form's FilterOn property as well, so the filter is
applied.

In the form's Open event procedure:
Me.Filter = "[AccountNo] > 1"
Me.FilterOn = True

It might be easier to make a query, put your criteria in there, and then
use
the query as the RecordSource for the form. Then the user can't just
remove
the filter.

I have two combo boxes (for account # and account name) on a user input
form
from which users can select a record (an account) in the underlying
table.
I
want to filter out one record (account #1) so this one cannot be
selected.
I
have tried putting "[AccountNo] > 1" in the Filter property of the
form,
but
this is being ignored. Do I need to do something in the form's code
instead,
or in addition to this?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top