Filtering records for form

  • Thread starter Thread starter Rob Parker
  • Start date Start date
R

Rob Parker

I have a form which contains several combo/text boxes and three toggle
buttons in the header section, which are used with a command button to
select the records to be displayed in the form. The code for this button is
as follows:
________________________

Private Sub cmdSelect_Click()

On Error GoTo Err_cmdSelect_Click

Dim strFilter As String

strFilter = ""
strFilter = "Task Like " & Chr$(34) & "*" & cboSelectTaskName & "*" &
Chr$(34)

If cboSelectDepartment <> "" Then
strFilter = strFilter & " " & tglDepartment.Caption & " Department = " &
cboSelectDepartment
End If

If cboSelectWorkplace <> "" Then
strFilter = strFilter & " " & tglWorkplace.Caption & " Workplace = " &
cboSelectWorkplace
End If

If txtSelectTaskNo <> "" Then
strFilter = strFilter & " " & tglTaskNo.Caption & " TaskID = " &
txtSelectTaskNo
End If

Debug.Print strFilter
Filter = strFilter
FilterOn = True
' Enable appropriate browse buttons in form footer
SetNavButtons "FIRST"

Exit_cmdSelect_Click:
Exit Sub

Err_cmdSelect_Click:
If Err.Number = 3021 Then 'No matching records for filter
MsgBox "There are no records matching the select conditions you have
entered " _
& vbNewLine & vbNewLine & "Please try again ...", vbOKOnly, "Select
Failed"
cboSelectTaskName.SetFocus
Resume Exit_cmdSelect_Click
Else
MsgBox Err.Description
Resume Exit_cmdSelect_Click
End If

End Sub
________________________

My problem is that this only works on the first occasion, and then only if
cboSelectTaskName is not null. Subsequent clicks on this command button
produce:
"Run-time error 2001: You cancelled the previous operation"
The offending line of code is indicated as being:
FilterOn = True

The tglXXX.Caption strings are either "AND" or "OR", and the constructed
strFilter works fine if I copy it from the debug window into the form's
Filter property, and then open the form.

Where is this RTE 2001 coming from, and how can I stop it from occurring?

TIA,

Rob
 
1. You probably need to test if the combos are Null rather than zero-length
strings.

2. If your field names have spaces in them, you will have to enclose the
names in square brackets.

3. I'm not sure how the "AND" between clauses is happening in your code.

4. Different delimiters are needed for different field types. The quotes are
for text, but if the bound field of the combo is a Number, omit the extra
quotes.

Dim strFilter As String
Dim lngLen As Long

If Not IsNull(Me.cboSelectTaskName) Then
strFilter = strFilter & "(Task Like ""*" & Me.cboSelectTaskName & "*"")
AND "
End If
If Not IsNull(Me.cboSelectDepartment) Then
strFilter = strFilter & "([" & Me.tglDepartment.Caption & " Department]
= " & Me.cboSelectDeparement & ") AND "
End If
'etc.
lngLen = Len(strFilter) - 5 'Without trailing " AND ".
If lngLen <=0 Then
MsgBox "No criteria."
Else
Me.Filter = Left(strFilter, lngLen)
Me.FilterOn = True
End If
 
Allen,

Thanks for the response, but it's still not working - see comments
interspersed with your reply:

Allen Browne said:
1. You probably need to test if the combos are Null rather than zero-length
strings.
Click to expand...

Done. My If statements are now of the form
If Not IsNull(Me.cboSelectDepartment) Then
rather than
2. If your field names have spaces in them, you will have to enclose the
names in square brackets.
Click to expand...

The field names did not have any spaces, but I've now put them in square
brackets as a matter of good coding practice
3. I'm not sure how the "AND" between clauses is happening in your code.
Click to expand...

The various clauses in strFilter are separated (as it's being constructed)
by either "AND" or "OR", depending on the state of the toggle buttons on the
form. The string for each toggle button's state is read from its caption,
in code such as:
strFilter & " " & tglDepartment.Caption & "...
4. Different delimiters are needed for different field types. The quotes are
for text, but if the bound field of the combo is a Number, omit the extra
quotes.
Click to expand...

I've only got quotes (entered as Chr$(34) in my code) for the contents of
cboSelectTaskName, as it's the only control containing text; the other two
combo boxes and the text box contain numbers.

What makes it really annoying to me, as I said, is that if I use the string
constructed by my code in the Filter property of the form, the appropriate
records are displayed - the filter works as I expect. It's only when I
apply the filter in code that an error occurs, and I can't understand why -
in particular, the error description seems non-sensical to me, as I haven't
(as far as I'm aware) cancelled any previous operation.

The only other thing that I'm missing is any bracketing of the various
AND/OR clauses to prevent ambiguity in the logic of the filter operation.
I'm not overly concerned about that, since my expectation is that users will
not select entries in all possible fields to construct the filter criteria,
but probably only in one or two - and I'm trapping no matching records in
the error handler to let them know if they're too restrictive in their
criteria. Is bracketing of multiple filter criteria essential?

Rob

Dim strFilter As String
Dim lngLen As Long

If Not IsNull(Me.cboSelectTaskName) Then
strFilter = strFilter & "(Task Like ""*" & Me.cboSelectTaskName & "*"")
AND "
End If
If Not IsNull(Me.cboSelectDepartment) Then
strFilter = strFilter & "([" & Me.tglDepartment.Caption & " Department]
= " & Me.cboSelectDeparement & ") AND "
End If
'etc.
lngLen = Len(strFilter) - 5 'Without trailing " AND ".
If lngLen <=0 Then
MsgBox "No criteria."
Else
Me.Filter = Left(strFilter, lngLen)
Me.FilterOn = True
End If

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

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

Rob Parker said:
I have a form which contains several combo/text boxes and three toggle
buttons in the header section, which are used with a command button to
select the records to be displayed in the form. The code for this
Click to expand...
button
is
as follows:
________________________

Private Sub cmdSelect_Click()

On Error GoTo Err_cmdSelect_Click

Dim strFilter As String

strFilter = ""
strFilter = "Task Like " & Chr$(34) & "*" & cboSelectTaskName & "*" &
Chr$(34)

If cboSelectDepartment <> "" Then
strFilter = strFilter & " " & tglDepartment.Caption & " Department =
Click to expand...
"
&
cboSelectDepartment
End If

If cboSelectWorkplace <> "" Then
strFilter = strFilter & " " & tglWorkplace.Caption & " Workplace = " &
cboSelectWorkplace
End If

If txtSelectTaskNo <> "" Then
strFilter = strFilter & " " & tglTaskNo.Caption & " TaskID = " &
txtSelectTaskNo
End If

Debug.Print strFilter
Filter = strFilter
FilterOn = True
' Enable appropriate browse buttons in form footer
SetNavButtons "FIRST"

Exit_cmdSelect_Click:
Exit Sub

Err_cmdSelect_Click:
If Err.Number = 3021 Then 'No matching records for filter
MsgBox "There are no records matching the select conditions you have
entered " _
& vbNewLine & vbNewLine & "Please try again ...", vbOKOnly, "Select
Failed"
cboSelectTaskName.SetFocus
Resume Exit_cmdSelect_Click
Else
MsgBox Err.Description
Resume Exit_cmdSelect_Click
End If

End Sub
________________________

My problem is that this only works on the first occasion, and then only if
cboSelectTaskName is not null. Subsequent clicks on this command button
produce:
"Run-time error 2001: You cancelled the previous operation"
The offending line of code is indicated as being:
FilterOn = True

The tglXXX.Caption strings are either "AND" or "OR", and the constructed
strFilter works fine if I copy it from the debug window into the form's
Filter property, and then open the form.

Where is this RTE 2001 coming from, and how can I stop it from occurring?

TIA,

Rob
Click to expand...
Click to expand...
 
Ah: so tblDepartment.Caption will contain either "AND" or "OR".

There is a logical problem here:
(a AND b) OR c
is not the same as:
a AND (b OR c)
The bracketing is very significant, and without the brackets the meaning is
not fully defined.

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

Reply to group, rather than allenbrowne at mvps dot org.
Rob Parker said:
Allen,

Thanks for the response, but it's still not working - see comments
interspersed with your reply:

Allen Browne said:
1. You probably need to test if the combos are Null rather than zero-length
strings.
Click to expand...

Done. My If statements are now of the form
If Not IsNull(Me.cboSelectDepartment) Then
rather than
2. If your field names have spaces in them, you will have to enclose the
names in square brackets.
Click to expand...

The field names did not have any spaces, but I've now put them in square
brackets as a matter of good coding practice
3. I'm not sure how the "AND" between clauses is happening in your code.
Click to expand...

The various clauses in strFilter are separated (as it's being constructed)
by either "AND" or "OR", depending on the state of the toggle buttons on the
form. The string for each toggle button's state is read from its caption,
in code such as:
strFilter & " " & tglDepartment.Caption & "...
4. Different delimiters are needed for different field types. The quotes are
for text, but if the bound field of the combo is a Number, omit the extra
quotes.
Click to expand...

I've only got quotes (entered as Chr$(34) in my code) for the contents of
cboSelectTaskName, as it's the only control containing text; the other two
combo boxes and the text box contain numbers.

What makes it really annoying to me, as I said, is that if I use the string
constructed by my code in the Filter property of the form, the appropriate
records are displayed - the filter works as I expect. It's only when I
apply the filter in code that an error occurs, and I can't understand why -
in particular, the error description seems non-sensical to me, as I haven't
(as far as I'm aware) cancelled any previous operation.

The only other thing that I'm missing is any bracketing of the various
AND/OR clauses to prevent ambiguity in the logic of the filter operation.
I'm not overly concerned about that, since my expectation is that users will
not select entries in all possible fields to construct the filter criteria,
but probably only in one or two - and I'm trapping no matching records in
the error handler to let them know if they're too restrictive in their
criteria. Is bracketing of multiple filter criteria essential?

Rob

Dim strFilter As String
Dim lngLen As Long

If Not IsNull(Me.cboSelectTaskName) Then
strFilter = strFilter & "(Task Like ""*" & Me.cboSelectTaskName & "*"")
AND "
End If
If Not IsNull(Me.cboSelectDepartment) Then
strFilter = strFilter & "([" & Me.tglDepartment.Caption & " Department]
= " & Me.cboSelectDeparement & ") AND "
End If
'etc.
lngLen = Len(strFilter) - 5 'Without trailing " AND ".
If lngLen <=0 Then
MsgBox "No criteria."
Else
Me.Filter = Left(strFilter, lngLen)
Me.FilterOn = True
End If

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

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

Rob Parker said:
I have a form which contains several combo/text boxes and three toggle
buttons in the header section, which are used with a command button to
select the records to be displayed in the form. The code for this
Click to expand...
button
is
as follows:
________________________

Private Sub cmdSelect_Click()

On Error GoTo Err_cmdSelect_Click

Dim strFilter As String

strFilter = ""
strFilter = "Task Like " & Chr$(34) & "*" & cboSelectTaskName & "*" &
Chr$(34)

If cboSelectDepartment <> "" Then
strFilter = strFilter & " " & tglDepartment.Caption & " Department
Click to expand...
Click to expand...
=
"
Click to expand...
"
&
Click to expand...
only
if
Click to expand...
Click to expand...
 
Finally nailed it!

My problem was due to having the [Department] field incorrectly set as a
text field in the table definition; it should have been a number field
(which displays text from a combo lookup in the table in datasheet view -
the bound number is in a hidden column). I tracked it down when I got a
"type mismatch" error after copying a generated filter string to the form's
filter property - although I'd done this previously, I hadn't got an error
message here before.

As a non-expert user, it seems to me that if the code had given an error
message about the basic problem (ie. type mismatch) rather than "You
cancelled the previous operation", then I would have been able to track this
down myself fairly quickly. Does Access/VBA behave this way often? Or, do
many of the error codes have underlying causes different to that described?

Thanks for your help, Allen.

Rob

And one more follow-up point in your previous answer below:

Allen Browne said:
Ah: so tblDepartment.Caption will contain either "AND" or "OR".

There is a logical problem here:
(a AND b) OR c
is not the same as:
a AND (b OR c)
The bracketing is very significant, and without the brackets the meaning is
not fully defined.
Click to expand...

As I said previously, I'm aware of the ambiguity, and not overly concerned
about it. What I really needed to know was whether, if the filter string is
ambiguous, an error is generated. I would expect not - I guess the string
is simply processed in order, and the result may not be what an "unaware"
user was expecting.
<snip>
 
Hi Rob.

Glad to know you solved this. Well done.

You performed a 2 step operation: assigning your string to the Filter
property, and then switching the FilterOn. You can assign an invalid string
to the Filter - no problem with that - but when you come to use the filter
(by setting FilterOn) of course it doesn't work. At that point, Access tries
to say that the previous thing you did (assigning the Filter) must have been
faulty.

The same problem does occur if you use an invalid Criteria argument in
DLookup() etc.

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

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

Rob Parker said:
Finally nailed it!

My problem was due to having the [Department] field incorrectly set as a
text field in the table definition; it should have been a number field
(which displays text from a combo lookup in the table in datasheet view -
the bound number is in a hidden column). I tracked it down when I got a
"type mismatch" error after copying a generated filter string to the form's
filter property - although I'd done this previously, I hadn't got an error
message here before.

As a non-expert user, it seems to me that if the code had given an error
message about the basic problem (ie. type mismatch) rather than "You
cancelled the previous operation", then I would have been able to track this
down myself fairly quickly. Does Access/VBA behave this way often? Or, do
many of the error codes have underlying causes different to that described?

Thanks for your help, Allen.

Rob

And one more follow-up point in your previous answer below:

Allen Browne said:
Ah: so tblDepartment.Caption will contain either "AND" or "OR".

There is a logical problem here:
(a AND b) OR c
is not the same as:
a AND (b OR c)
The bracketing is very significant, and without the brackets the meaning is
not fully defined.
Click to expand...

As I said previously, I'm aware of the ambiguity, and not overly concerned
about it. What I really needed to know was whether, if the filter string is
ambiguous, an error is generated. I would expect not - I guess the string
is simply processed in order, and the result may not be what an "unaware"
user was expecting.
<snip>
Click to expand...
 
Back
Top