Run-time error 2001

[Sean]

Hey all,

I've been having an issue with a form I'm trying to run a filter on.
I've looked all over this newsgroup and can't seem to find the right
answer to solve my problem, so I've resorted to actually post!

Below is the code I'm having trouble with. The form has four combo
boxes that allow the user to filter the data on the form. Once a combo
box is updated, the appropriate sub below is called and sets the string
in the FilterArray. All four filters should work the same. However,
when I try to filter using cboFindTermBlock I get the following error:

Run-time error '2001':
You canceled the previous operation.

which debugs at the following line of code:

Me.Filter = strFilter

The other three filters all work perfectly. I should note that the
data in the cboFindTermBlock combo box is populated from a query that
splits a longer string into pieces, i.e. TB102-14 (text) becomes TB102
in the query.

I've checked the data types on all the combo boxes and they all contain
text, so I'm really at a loss to try to figure this thing out. Any
help is greatly appreciated!!!

---------------------------------------------------------------------------------------------
Private Sub cboFindCabinet_AfterUpdate()
i = cboFindCabinet.ListIndex
If i = -1 Then Exit Sub

FilterArray(1) = "[CAB_MMOD] = '" & cboFindCabinet & "'"
FilterDetails
End Sub

Private Sub cboFindTermBlock_AfterUpdate()
i = cboFindTermBlock.ListIndex
If i = -1 Then Exit Sub

FilterArray(2) = "[TermBlock] = '" & cboFindTermBlock.Value & "'"
FilterDetails
End Sub

Private Sub cboFindCableNum_AfterUpdate()
i = cboFindCableNum.ListIndex
If i = -1 Then Exit Sub

FilterArray(3) = "[Cable_Num] = '" & cboFindCableNum & "'"
FilterDetails
End Sub

Private Sub cboFindSystem_AfterUpdate()
i = cboFindSystem.ListIndex
If i = -1 Then Exit Sub

FilterArray(4) = "[SystemAlt] = '" & cboFindSystem & "'"
FilterDetails
End Sub

Private Sub FilterDetails()
Dim strFilter

strFilter = "("
strFilter = strFilter & FilterArray(1)
If FilterArray(2) <> "" Then strFilter = strFilter & " AND " &
FilterArray(2)
If FilterArray(3) <> "" Then strFilter = strFilter & " AND " &
FilterArray(3)
If FilterArray(4) <> "" Then strFilter = strFilter & " AND " &
FilterArray(4)
strFilter = Replace(strFilter, "( AND ", "(") & ")"
strFilter = Replace(strFilter, "()", "")
strFilter = strFilter

Me.Filter = strFilter
Me.FilterOn = True
End Sub
---------------------------------------------------------------------------------------------

 

[Klatuu]

This error is usually caused by a misnamed field or table. Setting a filter
is the same as running query. Access creates SQL behind the scene and sends
it to Jet. Jet can't resolve the references and returns an error to Access
saying it could not do the operation. Access interprets that as a canceled
operation. Misleading at best, but I have done some experimenting with this
problem, and determined it is a naming problem.

Hey all,

I've been having an issue with a form I'm trying to run a filter on.
I've looked all over this newsgroup and can't seem to find the right
answer to solve my problem, so I've resorted to actually post!

Below is the code I'm having trouble with. The form has four combo
boxes that allow the user to filter the data on the form. Once a combo
box is updated, the appropriate sub below is called and sets the string
in the FilterArray. All four filters should work the same. However,
when I try to filter using cboFindTermBlock I get the following error:

Run-time error '2001':
You canceled the previous operation.

which debugs at the following line of code:

Me.Filter = strFilter

The other three filters all work perfectly. I should note that the
data in the cboFindTermBlock combo box is populated from a query that
splits a longer string into pieces, i.e. TB102-14 (text) becomes TB102
in the query.

I've checked the data types on all the combo boxes and they all contain
text, so I'm really at a loss to try to figure this thing out. Any
help is greatly appreciated!!!

---------------------------------------------------------------------------------------------
Private Sub cboFindCabinet_AfterUpdate()
i = cboFindCabinet.ListIndex
If i = -1 Then Exit Sub

FilterArray(1) = "[CAB_MMOD] = '" & cboFindCabinet & "'"
FilterDetails
End Sub

Private Sub cboFindTermBlock_AfterUpdate()
i = cboFindTermBlock.ListIndex
If i = -1 Then Exit Sub

FilterArray(2) = "[TermBlock] = '" & cboFindTermBlock.Value & "'"
FilterDetails
End Sub

Private Sub cboFindCableNum_AfterUpdate()
i = cboFindCableNum.ListIndex
If i = -1 Then Exit Sub

FilterArray(3) = "[Cable_Num] = '" & cboFindCableNum & "'"
FilterDetails
End Sub

Private Sub cboFindSystem_AfterUpdate()
i = cboFindSystem.ListIndex
If i = -1 Then Exit Sub

FilterArray(4) = "[SystemAlt] = '" & cboFindSystem & "'"
FilterDetails
End Sub

Private Sub FilterDetails()
Dim strFilter

strFilter = "("
strFilter = strFilter & FilterArray(1)
If FilterArray(2) <> "" Then strFilter = strFilter & " AND " &
FilterArray(2)
If FilterArray(3) <> "" Then strFilter = strFilter & " AND " &
FilterArray(3)
If FilterArray(4) <> "" Then strFilter = strFilter & " AND " &
FilterArray(4)
strFilter = Replace(strFilter, "( AND ", "(") & ")"
strFilter = Replace(strFilter, "()", "")
strFilter = strFilter

Me.Filter = strFilter
Me.FilterOn = True
End Sub

 

[Sean]

I double-checked everything in the naming convention I can think of. I
even deleted the combo box and completely remade it from scratch, code
behind and all. Still the same error. Any other suggestions are much
appreciated...

 

[Klatuu]

Set a break point on this line:
Me.Filter = strFilter
And look at the value of strFilter. The way your code is constructed, it is
difficult to tell what it will look like. It should end up looking like an
SQL WHERE clause without the word WHERE.

 

[Sean]

strFilter value is "([TermBlock] = 'TB102')" which should work as far
as I'm aware... The other filters look the same and when there are
multiple filters there is simply an " AND " in between... But I'm only
having issues on this one for the TermBlock.

 

[Klatuu]

I really don't see a problem, then.
Try this.
Open your form.
Open the VBA editor and go to the immediate window.
Eneter the code by hand (you will have to use the form name instead of Me
for this to work, for example
Forms!MyFormName.Filter = "[TermBlock] = 'TB102'"
and see what happens.
Notice I left out the parenthises, you don't really need them.

strFilter value is "([TermBlock] = 'TB102')" which should work as far
as I'm aware... The other filters look the same and when there are
multiple filters there is simply an " AND " in between... But I'm only
having issues on this one for the TermBlock.

Set a break point on this line:
Me.Filter = strFilter
And look at the value of strFilter. The way your code is constructed, it is
difficult to tell what it will look like. It should end up looking like an
SQL WHERE clause without the word WHERE.

 

[Sean]

I'm currently rewriting all the queries (there are quite a few) that
are used for the form. I'll let you know what turns up!

 

[Sean]

Klatuu, thanks for all the help. I tried your last recommendation and
it gave the same error. I just finished rewriting and completely
revamping all the queries on the backside and it now works. Imagine
that! Thanks again.