Problem setting filter with code - is it a bug?

[Leslie Isaacs]

Hello All

I have a form called [frm message log], andf in the form header is an
unbound textbox called [msgsearch] and a toggle called [tglEnded]. One of
the fields in the detail section is [msgended] (a true/false field). Also in
the form header is a button with the following onClick event:

If [Forms]![frm message log]![tglEnded] = False Then
Me.Filter = "[msgended] = False AND [msgtext] like
""*""&[Forms]![frm message log]![msgsearch] &""*"""
Else
Me.Filter = "[msgtext] like ""*""&[Forms]![frm message
log]![msgsearch] &""*"""
End If
Me.FilterOn = True

The filter in 'true' part of the condition works fine, but when the
condition fails the second filter generates an error - Runtime error 3021No
Current Record.
I can't understand this, as the second filter is the same as the first
filter except that it excludes the first part - i.e. [msgended] = False. I
have even tried making the second filter

Me.Filter = "([msgended] = False OR [msgended] = True) AND [msgtext]
like ""*""&[Forms]![frm message log]![msgsearch] &""*"""

but this gives the same error.

Is this a bug - or am I being thick?

Hope someone can help.
Thanks in advance.
Les

 

[NKTower]

I think you have a problem with those pesky doubled-up quotes etc. I like to
use a variable Q for the quote character, it makes it easier to follow. Try
this (be careful about spaces on either side of the ampersands - may not
show on the forum) Lastly, when inside an object (your form) you may use the
"Me" syntax.

Dim Q As String * 1
Dim sLP As String ' our Like Phrase
Dim sF As String ' our filter string

Q = Chr$(34)
sLP = " [msgtext] Like " & Q & "*" & Me.msgsearch] & "*" & Q

If Not Me.tglEnded Then
sF = "( ( Not [msgended] ) And ( " & sLP & " ) ) "
Else
sF = "( " & sLP & " )"
End If
Debug.Print sF ' comment out once tested
Me.Filter = sF
Me.FilterOn = True

 

[Leslie Isaacs]

Hello NKTower

Thanks for your reply. I will try your suggested code when I get back to the
office - but unfortunately this won't be until Friday. I'll post back then
with the results!

Thanks again
Les

I think you have a problem with those pesky doubled-up quotes etc. I like
to
use a variable Q for the quote character, it makes it easier to follow.
Try
this (be careful about spaces on either side of the ampersands - may not
show on the forum) Lastly, when inside an object (your form) you may use
the
"Me" syntax.

Dim Q As String * 1
Dim sLP As String ' our Like Phrase
Dim sF As String ' our filter string

Q = Chr$(34)
sLP = " [msgtext] Like " & Q & "*" & Me.msgsearch] & "*" & Q

If Not Me.tglEnded Then
sF = "( ( Not [msgended] ) And ( " & sLP & " ) ) "
Else
sF = "( " & sLP & " )"
End If
Debug.Print sF ' comment out once tested
Me.Filter = sF
Me.FilterOn = True

Hello All

I have a form called [frm message log], andf in the form header is an
unbound textbox called [msgsearch] and a toggle called [tglEnded]. One of
the fields in the detail section is [msgended] (a true/false field). Also
in
the form header is a button with the following onClick event:

If [Forms]![frm message log]![tglEnded] = False Then
Me.Filter = "[msgended] = False AND [msgtext] like
""*""&[Forms]![frm message log]![msgsearch] &""*"""
Else
Me.Filter = "[msgtext] like ""*""&[Forms]![frm message
log]![msgsearch] &""*"""
End If
Me.FilterOn = True

The filter in 'true' part of the condition works fine, but when the
condition fails the second filter generates an error - Runtime error
3021No
Current Record.
I can't understand this, as the second filter is the same as the first
filter except that it excludes the first part - i.e. [msgended] = False.
I
have even tried making the second filter

Me.Filter = "([msgended] = False OR [msgended] = True) AND [msgtext]
like ""*""&[Forms]![frm message log]![msgsearch] &""*"""

but this gives the same error.

Is this a bug - or am I being thick?

Hope someone can help.
Thanks in advance.
Les