Help, Runtime error 2001, You Canceled the Previous Operation

  • Thread starter Thread starter DStrong
  • Start date Start date
D

DStrong

OK, so I am using Win XP, and running Access 2003. I have a 2003 database
downloaded from MS Office site called Issues DB.
I have added to this DB a group of tables for Root Cause Analysis. I have
set up a search just like the one for the Issues search and all works except
search by what I have called Record Owner. This is a first and last name of
the person who entered this in to the DB.

I have this macro copied from the Issues DB for a search:
Private Sub Search_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If Department
If Not IsNull(Me.Department) Then
'Create Predicate
strWhere = strWhere & " AND " & "[RCA Records].Department = " &
Me.Department & ""
End If

' If Record Owner
If Not IsNull(Me.[Record Owner]) Then
'Add the predicate
strWhere = strWhere & " AND " & "[RCA Records].[Record Owner] = " &
Me.[Record Owner] & ""
End If

' If Status
If Not IsNull(Me.Status) Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "[RCA Records].Status = '" &
Me.Status & "'"
End If


If strError <> "" Then
MsgBox strError
Else
'DoCmd.OpenForm "Browse RCAs", acFormDS, , strWhere, acFormEdit,
acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.[Browse All RCA Events].Form.Filter = strWhere
Me.[Browse All RCA Events].Form.FilterOn = True
End If
End Sub

When I search my Record Owener I get a 'runtime error 2001, You Canceled the
Previous Operation'. When I debug the line
Me.[Browse All RCA Events].Form.Filter = strWhere
is highlighted. I have tried to change everything but I am overlooking
something somewhere. I have now looked at this so long that I know I cannot
find this and need a good set of eyes to find my error.
My form seems to be pulling the data correctly, I just can't find where I
have gone wrong.

If you need more info, please ask. I need to get this working ASAP.
 
That error likely means that the strWhere variable has an incorrect syntax
in the string. I'm guessing it's because one of the fields on which you're
filtering is a text field, perhaps Department?

Assuming so, or else using it as an example, your code line

strWhere = strWhere & " AND " & "[RCA Records].Department = " &
Me.Department & ""

should be changed to this (note the use of the ' character to delimit the
value from Me.Department):

strWhere = strWhere & " AND " & "[RCA Records].Department = '" &
Me.Department & "'"
 
That did nothing different. In fact that cause my search by department to no
longer function. That has always worked fine. I have three search items for
the user, Department, Record Owner and Status. Department and Status have
always worked just fine. Its the Record Owner that cuases this error. Any
other ideas?
--
Let me macronize that for you!


Ken Snell (MVP) said:
That error likely means that the strWhere variable has an incorrect syntax
in the string. I'm guessing it's because one of the fields on which you're
filtering is a text field, perhaps Department?

Assuming so, or else using it as an example, your code line

strWhere = strWhere & " AND " & "[RCA Records].Department = " &
Me.Department & ""

should be changed to this (note the use of the ' character to delimit the
value from Me.Department):

strWhere = strWhere & " AND " & "[RCA Records].Department = '" &
Me.Department & "'"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



DStrong said:
OK, so I am using Win XP, and running Access 2003. I have a 2003 database
downloaded from MS Office site called Issues DB.
I have added to this DB a group of tables for Root Cause Analysis. I have
set up a search just like the one for the Issues search and all works
except
search by what I have called Record Owner. This is a first and last name
of
the person who entered this in to the DB.

I have this macro copied from the Issues DB for a search:
Private Sub Search_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If Department
If Not IsNull(Me.Department) Then
'Create Predicate
strWhere = strWhere & " AND " & "[RCA Records].Department = " &
Me.Department & ""
End If

' If Record Owner
If Not IsNull(Me.[Record Owner]) Then
'Add the predicate
strWhere = strWhere & " AND " & "[RCA Records].[Record Owner] = " &
Me.[Record Owner] & ""
End If

' If Status
If Not IsNull(Me.Status) Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "[RCA Records].Status = '" &
Me.Status & "'"
End If


If strError <> "" Then
MsgBox strError
Else
'DoCmd.OpenForm "Browse RCAs", acFormDS, , strWhere, acFormEdit,
acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.[Browse All RCA Events].Form.Filter = strWhere
Me.[Browse All RCA Events].Form.FilterOn = True
End If
End Sub

When I search my Record Owener I get a 'runtime error 2001, You Canceled
the
Previous Operation'. When I debug the line
Me.[Browse All RCA Events].Form.Filter = strWhere
is highlighted. I have tried to change everything but I am overlooking
something somewhere. I have now looked at this so long that I know I
cannot
find this and need a good set of eyes to find my error.
My form seems to be pulling the data correctly, I just can't find where I
have gone wrong.

If you need more info, please ask. I need to get this working ASAP.
Click to expand...
Click to expand...
 
You may have missed the point of my reply. My point is that the string that
you build in the strWhere variable is not a valid WHERE clause, and I cannot
tell you what the actual error might be because I have no information about
the datatypes of the fields, etc. What I suggested is that a common error is
the lack of ' delimiters for string values that are concatenated into a
WHERE string. I only used Department as an example.

So, based on what you say that the other fields have always worked, but
Record Owner is new, look at the Record Owner field with respect to my
suggestion.

Otherwise, you'll need to give us more details about the fields and their
data types.

One other possibility is that one of your form's controls (whose value
you're concatenating into the strWhere variable) contains a NULL value. This
too will prompt the error message that you get.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


DStrong said:
That did nothing different. In fact that cause my search by department to
no
longer function. That has always worked fine. I have three search items
for
the user, Department, Record Owner and Status. Department and Status have
always worked just fine. Its the Record Owner that cuases this error. Any
other ideas?
--
Let me macronize that for you!


Ken Snell (MVP) said:
That error likely means that the strWhere variable has an incorrect
syntax
in the string. I'm guessing it's because one of the fields on which
you're
filtering is a text field, perhaps Department?

Assuming so, or else using it as an example, your code line

strWhere = strWhere & " AND " & "[RCA Records].Department = " &
Me.Department & ""

should be changed to this (note the use of the ' character to delimit the
value from Me.Department):

strWhere = strWhere & " AND " & "[RCA Records].Department = '" &
Me.Department & "'"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



DStrong said:
OK, so I am using Win XP, and running Access 2003. I have a 2003
database
downloaded from MS Office site called Issues DB.
I have added to this DB a group of tables for Root Cause Analysis. I
have
set up a search just like the one for the Issues search and all works
except
search by what I have called Record Owner. This is a first and last
name
of
the person who entered this in to the DB.

I have this macro copied from the Issues DB for a search:
Private Sub Search_Click()
Const cInvalidDateError As String = "You have entered an invalid
date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If Department
If Not IsNull(Me.Department) Then
'Create Predicate
strWhere = strWhere & " AND " & "[RCA Records].Department = " &
Me.Department & ""
End If

' If Record Owner
If Not IsNull(Me.[Record Owner]) Then
'Add the predicate
strWhere = strWhere & " AND " & "[RCA Records].[Record Owner] =
" &
Me.[Record Owner] & ""
End If

' If Status
If Not IsNull(Me.Status) Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "[RCA Records].Status = '" &
Me.Status & "'"
End If


If strError <> "" Then
MsgBox strError
Else
'DoCmd.OpenForm "Browse RCAs", acFormDS, , strWhere, acFormEdit,
acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight +
Me.FormFooter.Height
End If
Me.[Browse All RCA Events].Form.Filter = strWhere
Me.[Browse All RCA Events].Form.FilterOn = True
End If
End Sub

When I search my Record Owener I get a 'runtime error 2001, You
Canceled
the
Previous Operation'. When I debug the line
Me.[Browse All RCA Events].Form.Filter = strWhere
is highlighted. I have tried to change everything but I am overlooking
something somewhere. I have now looked at this so long that I know I
cannot
find this and need a good set of eyes to find my error.
My form seems to be pulling the data correctly, I just can't find where
I
have gone wrong.

If you need more info, please ask. I need to get this working ASAP.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top