mysterious disappearing filter

[quaddawg via AccessMonster.com]

I apologize in advance for the longwindedness of this post, but I can't for
the life of me figure out the problem so I'll err on the side of providing
too much info. Also, this might be nearly impossible to diagnose this way,
but if anything strikes you as strange or wrong, please let me know.

I have a data entry form with a command button that launches a search
dialogue box containing unbound search boxes. I have a search button on the
search form coded to open (reopen?) the entry form while applying a filter
consisting of a query running off the search entry form. (The query is now
displayed in design or sql view with an insane number of permutations, I'm
guessing due to the original design allowing null values in the unbound
search fields).

The code of the search button reads in pertinent part:
DoCmd.OpenForm "frmMembershipEntryForm", acNormal, "qryMembershipFormSearch2"

I added a command button on the entry form that cleared the filter using:
Me.FilterOn = False

This worked fine for awhile; the form would open, filtered by the query.
Pushing the clear filter button would remove the query filter, displaying all
results.

Today, for no apparent reason, the search button would open the form with no
noticeable filtering.
Some things I've noticed, relevance unknown:
While before the form would indicate, next to the record selector buttons, "
(Filtered)", it no longer does.
The filter funnel icon on the menu is grayed out when the form opens.
The form's properties are set to allow filters, but where the filter line
used to have at least some of the language of the fitlering query is now
blank.

To diagnose, I added a DoCmd.OpenQuery "qryMembershipFormSearch2", etc. to
the Search button and the proper results display in the query, but the Entry
Form still opens with no filter applied.

I would greatly appreciate any insights you might have as to the sudden
(can't be random, I know) inapplicability of the filter.
Further, I'd love suggestions for the proper application of filtering queries
to forms through coded buttons.
Thanks for your help!

 

[AccessVandal via AccessMonster.com]

On the form properties for "frmMembershipEntryForm", event tab,
put this into the "On Close" event of the the form.

Me.FilterOn = True

 

[quaddawg via AccessMonster.com]

Thanks for your reply.
I followed your instructions to no avail--for whatever reason the filter
still does not seem to be applying to the form.

 

[AccessVandal via AccessMonster.com]

Take a look a Allen Brown's site. Might help you.

http://allenbrowne.com/bug-02.html

Thanks for your reply.
I followed your instructions to no avail--for whatever reason the filter
still does not seem to be applying to the form.

On the form properties for "frmMembershipEntryForm", event tab,
put this into the "On Close" event of the the form.

[quoted text clipped - 4 lines]

 

[quaddawg via AccessMonster.com]

Thanks. I'm not sure any of the listed flaws is causing my problem; I'm
more inclined to think I've missed something obvious, though these flaws are
disconcerting.
It's perplexing that naming the query as the filter in the DoCmd.OpenForm
line has no effect when once it did.

Take a look a Allen Brown's site. Might help you.

http://allenbrowne.com/bug-02.html

Thanks for your reply.
I followed your instructions to no avail--for whatever reason the filter

[quoted text clipped - 5 lines]

 

[quaddawg via AccessMonster.com]

I think the beastly query as transformed by the query "genie" is too long to
serve as a filter. If I try to paste the query in the Filter line in the
form's properties I get the message: "The text is too long to be edited."

If I clear out the Order By line in the form's properties and run the code,
the Order By info from the query shows up, but the Filter line remains blank.

I made a stripped down query only searching on one field and it works,
filtering the records and filling in the Filter line in the form's properties.


As to why it suddenly happened? Perhaps the query was not tranformed into
its insane length until I opened it in design view? I know that sounds
unlikely, but something like it seems to be the case.

I suppose I need to come up with a new way of searching. Time to switch
Groups.

Take a look a Allen Brown's site. Might help you.

http://allenbrowne.com/bug-02.html

Thanks for your reply.
I followed your instructions to no avail--for whatever reason the filter

[quoted text clipped - 5 lines]

 

[AccessVandal via AccessMonster.com]

This is what I found in Access Help,

“The maximum length of the Where Condition argument is 256 characters. If you
need to enter a longer SQL WHERE clause, use the ApplyFilter method of the
DoCmd object in Visual Basic. You can enter SQL WHERE clause statements of up
to 32,768 characters in Visual Basic.”

Meaning, "qryMembershipFormSearch2” is your Filter Name, the where conditions
for the form filter, the characters cannot exceed 256.

 

[Allen Browne]

The Help file indicates that the 256-char limit on the WhereCondition
applies only the the OpenReport action in a macro. If you use OpenReport in
code, the limit is 32768 characters. Quoting the Access 2003 help file:
<quote>
The maximum length of the WhereCondition argument is 32,768 characters
(unlike the Where Condition action argument in the Macro window, whose
maximum length is 256 characters).
</quote>

Backing up a little, you seem to be using the FilterName argument for open
report? I have found that approach to be unreliable. Use the WhereCondition
instead, i.e. just the WHERE clause of whatever SQL was in
qryMembershipFormSearch2, after the extra comma.

 

[quaddawg via AccessMonster.com]

I was using the FilterName argument of open FORM rather than open report.; I
don't know if that makes a difference. I was not using the WhereCondition
because it is extremely long due to multiple "[Forms]![frmSearch]![txtABC] Or
[Forms]![[frmSearch]![txtABC] Is Null" statements.

Looking through the site more I've decided to use your (Mr. Browne's) code to
build a filter string that you posted under a thread entitled: Query Fields
Value and Null.

I will try using the Where Condition argument and I will try the filter
building code and post back the results.

Thanks to both of you.
Lawton

The Help file indicates that the 256-char limit on the WhereCondition
applies only the the OpenReport action in a macro. If you use OpenReport in
code, the limit is 32768 characters. Quoting the Access 2003 help file:
<quote>
The maximum length of the WhereCondition argument is 32,768 characters
(unlike the Where Condition action argument in the Macro window, whose
maximum length is 256 characters).
</quote>

Backing up a little, you seem to be using the FilterName argument for open
report? I have found that approach to be unreliable. Use the WhereCondition
instead, i.e. just the WHERE clause of whatever SQL was in
qryMembershipFormSearch2, after the extra comma.

This is what I found in Access Help,

[quoted text clipped - 13 lines]

 

[quaddawg via AccessMonster.com]

Any ideas how to get a WHERE statement with over 20,000 characters in the VBA
editor? I get a huge block of red text when I try to paste in the
WhereStatement position.

Also, I could not get the code string mentioned above to work as I'm a
complete code novice.

The Help file indicates that the 256-char limit on the WhereCondition
applies only the the OpenReport action in a macro. If you use OpenReport in
code, the limit is 32768 characters. Quoting the Access 2003 help file:
<quote>
The maximum length of the WhereCondition argument is 32,768 characters
(unlike the Where Condition action argument in the Macro window, whose
maximum length is 256 characters).
</quote>

Backing up a little, you seem to be using the FilterName argument for open
report? I have found that approach to be unreliable. Use the WhereCondition
instead, i.e. just the WHERE clause of whatever SQL was in
qryMembershipFormSearch2, after the extra comma.

This is what I found in Access Help,

[quoted text clipped - 13 lines]

 

[AccessVandal via AccessMonster.com]

If you want to use VBA, here is the “air” code sample.
Use the “ & _ ” or “ _ “ to break long statements into multiple lines.

Dim stLinkCriteria as String

StLinkCriteria = “[ABC] = “ & [Forms]![frmSearch]![txtABC] & “ OR [ABC] =
“isNull” “ & _
“ AND [DEF] = “ & [Forms]![frmSearch]![txtDEF] & “ AND “ & “[GHI] = “ &
[Forms]![frmSearch]![txtGHI] & _
“ ………and so on………

DoCmd.OpenForm "frmMembershipEntryForm", , , stLinkCriteria

Take a look at Allen Browne’s site of more samples.
There are some sites with tutorials using VBA and SQL.

 

[quaddawg via AccessMonster.com]

Will do, thanks.

If you want to use VBA, here is the “air” code sample.
Use the “ & _ ” or “ _ “ to break long statements into multiple lines.

Dim stLinkCriteria as String

StLinkCriteria = “[ABC] = “ & [Forms]![frmSearch]![txtABC] & “ OR [ABC] =
“isNull” “ & _
“ AND [DEF] = “ & [Forms]![frmSearch]![txtDEF] & “ AND “ & “[GHI] = “ &
[Forms]![frmSearch]![txtGHI] & _
“ ………and so on………

DoCmd.OpenForm "frmMembershipEntryForm", , , stLinkCriteria

Take a look at Allen Browne’s site of more samples.
There are some sites with tutorials using VBA and SQL.

Any ideas how to get a WHERE statement with over 20,000 characters in the VBA
editor? I get a huge block of red text when I try to paste in the
WhereStatement position.

 

[quaddawg via AccessMonster.com]

This thread might be dead, but just in case, I'll ask (I've been reading lots
of threads on the where statement, but they all seem to be starting from
scratch rather than using the language from an existing query):
Using the where condition is not as simple as copying and pasting the where
statement in sql view is it? When I do, I get an "Object required" error
message.

This is the way my code looks (it's all on one line of code, so I'm assuming
I don't need to wrap any lines):

DoCmd.OpenForm "frmMembershipEntryForm", , , (((tblMemberData.LastName) Like
forms!frmSearch!txtLastName & "*" Or forms!frmSearch!txtLastName Is Null) And
((tblMemberData.FirstName) Like forms!frmSearch!txtFirstName & "*" Or forms!
frmSearch!txtFirstName Is Null) And ((tblMemberData.Address1) Like forms!
frmSearch!txtAddress1 & "*" Or forms!frmSearch!txtAddress1 Is Null) And (
(tblMemberData.City) Like forms!frmSearch!txtCity & "*" Or forms!frmSearch!
txtCity Is Null) And ((tblMemberData.State) Like forms!frmSearch!cmbState Or
forms!frmSearch!cmbState Is Null))

Do I have to add some punctuation, some word or words, or do I need to
completely rewrite the where statement?
Thanks

The Help file indicates that the 256-char limit on the WhereCondition
applies only the the OpenReport action in a macro. If you use OpenReport in
code, the limit is 32768 characters. Quoting the Access 2003 help file:
<quote>
The maximum length of the WhereCondition argument is 32,768 characters
(unlike the Where Condition action argument in the Macro window, whose
maximum length is 256 characters).
</quote>

Backing up a little, you seem to be using the FilterName argument for open
report? I have found that approach to be unreliable. Use the WhereCondition
instead, i.e. just the WHERE clause of whatever SQL was in
qryMembershipFormSearch2, after the extra comma.

This is what I found in Access Help,

[quoted text clipped - 13 lines]

 

[Douglas J. Steele]

You're missing a lot of quotes and concatenation operators.

All of the field names and Like operators need to be in quotes. The values
you're looking for need to have quotes before and after, but the control is
outside of the quotes, so that you get the value from the control, not its
name. And you don't really need the Is Null checks.

DoCmd.OpenForm "frmMembershipEntryForm", , , "(tblMemberData.LastName Like
'" & forms!frmSearch!txtLastName & "*') And (tblMemberData.FirstName Like '"
& forms!frmSearch!txtFirstName & "*') And (tblMemberData.Address1 Like '" &
forms!frmSearch!txtAddress1 & "*') And (tblMemberData.City Like '" &
forms!frmSearch!txtCity & "*') And (tblMemberData.State Like '" &
forms!frmSearch!cmbState & "*')"

The reason I say you don't need the checks for Null is because if the
control is Null, you'll end up with something like (tblMemberData.LastName
Like '*'), so you'll get all the names. (Actually, there is a subtle
difference: if LastName is Null, you won't get it with that)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

This thread might be dead, but just in case, I'll ask (I've been reading
lots
of threads on the where statement, but they all seem to be starting from
scratch rather than using the language from an existing query):
Using the where condition is not as simple as copying and pasting the
where
statement in sql view is it? When I do, I get an "Object required" error
message.

This is the way my code looks (it's all on one line of code, so I'm
assuming
I don't need to wrap any lines):

DoCmd.OpenForm "frmMembershipEntryForm", , , (((tblMemberData.LastName)
Like
forms!frmSearch!txtLastName & "*" Or forms!frmSearch!txtLastName Is Null)
And
((tblMemberData.FirstName) Like forms!frmSearch!txtFirstName & "*" Or
forms!
frmSearch!txtFirstName Is Null) And ((tblMemberData.Address1) Like forms!
frmSearch!txtAddress1 & "*" Or forms!frmSearch!txtAddress1 Is Null) And (
(tblMemberData.City) Like forms!frmSearch!txtCity & "*" Or
forms!frmSearch!
txtCity Is Null) And ((tblMemberData.State) Like forms!frmSearch!cmbState
Or
forms!frmSearch!cmbState Is Null))

Do I have to add some punctuation, some word or words, or do I need to
completely rewrite the where statement?
Thanks

The Help file indicates that the 256-char limit on the WhereCondition
applies only the the OpenReport action in a macro. If you use OpenReport
in
code, the limit is 32768 characters. Quoting the Access 2003 help file:
<quote>
The maximum length of the WhereCondition argument is 32,768 characters
(unlike the Where Condition action argument in the Macro window, whose
maximum length is 256 characters).
</quote>

Backing up a little, you seem to be using the FilterName argument for open
report? I have found that approach to be unreliable. Use the
WhereCondition
instead, i.e. just the WHERE clause of whatever SQL was in
qryMembershipFormSearch2, after the extra comma.

This is what I found in Access Help,

[quoted text clipped - 13 lines]

serve as a filter. If I try to paste the query in the Filter line in
the
form's properties I get the message: "The text is too long to be
edited."