Runtime Error '2001': You canceled the previous operation

[Louie Warren]

What is this error and why am I getting it? Thanx!

Code:

Private Sub ComboBoxField_GotFocus()
Dim lngRed As Long, lngBlue As Long, lngGreen As Long,
lngBlack As Long
Dim TypeId As Long
Dim lngState As Long

Dim strSQL As String
strSQL = "SELECT tTypes.TypeId " & _
"FROM tTypes INNER JOIN tPeople ON
tTypes.TypeId = tPeople.TypeFK " & _
"WHERE tPeople.Id = '" &
Forms.fMyForm.ComboBoxField & "';"

Dim daoDatabase As DAO.Database
Set daoDatabase = CurrentDb

Dim daoQueryDef As DAO.QueryDef
Set daoQueryDef = daoDatabase.QueryDefs
("qry$DynamicSQL")

daoQueryDef.SQL = strSQL
DoCmd.OpenQuery "qry$DynamicSQL", acViewNormal,
acReadOnly

lngGreen = RGB(75, 200, 100)
lngBlue = RGB(0, 0, 255)
lngBlack = RGB(50, 50, 50)
lngRed = RGB(200, 0, 0)

If TypeId = 1 Then
Field.Caption = "Type 1"
Field.ForeColor = lngGreen
End If

If TypeId = 2 Then
Field.Caption = "Type 2"
Field.ForeColor = lngBlue
End If

If TypeId = 3 Then
Field.Caption = "Type 3"
Field.ForeColor = lngBlack
End If

If TypeId = 4 Then
Field.Caption = "Type 4"
Field.ForeColor = lngRed
End If

End Sub

 

[Louie Warren]

Wrong group? Not enough detail?

 

[Brendan Reynolds]

What kind of object is "Field"? Where is it declared and instantiated? And
which line of code raises the error?

The use of the word "Field" as an identifier is probably not a good idea, as
it is a keyword in both DAO and ADO.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Louie Warren]

What kind of object is "Field"? Where is it declared and
instantiated?

"Field" is a combo box on the form. The name is generic
for this posting because the database is on a classified
system. Therefore, "the names have been changed to
protect the innocent."

And
which line of code raises the error?

DoCmd.OpenQuery "qry$DynamicSQL", acViewNormal, acReadOnly

The use of the word "Field" as an identifier is probably not a good idea, as
it is a keyword in both DAO and ADO.

See above.

 

[Brendan Reynolds]

Well, a combo box doesn't have a Caption property, so if the code got that
far, you'd hit an error there. (You need the Caption property of the label,
if any, attached to the combo box, instead.) But it isn't getting that far.
I copied and pasted the 'OpenQuery' line that is raising the error, changing
only the name of the query, to the name of a query that exists in my test
MDB, and it runs without error. So it looks as though the problem may be in
the SQL - are you sure that 'tPeople.Id' is a text field? What about the
join? Are the fields involved in the join compatible numeric types?

If you still can't see the problem, I'd suggest testing the query in the
query designer. Enter your SQL in SQL View same as in the code, except
without the quotes and line continuation characters, obviously, make sure
the form is open and something is selected from the combo box, and switch
the query to datasheet view. Do you get any errors? Does the query return
any data?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Louie Warren]

Well, a combo box doesn't have a Caption property, so if
the code got that

far, you'd hit an error there. (You need the Caption

property of the label,

Once again, I didn't explain correctly... Yes I have a
combo box, but, no Field isn't it. Field is a text box.
Depending on the value in the combo box
(Forms.fMyForm.ComboBoxField) the text box Field has a
different caption and color scheme.

if any, attached to the combo box, instead.) But it isn't getting that far.
I copied and pasted the 'OpenQuery' line that is raising the error, changing
only the name of the query, to the name of a query that exists in my test
MDB, and it runs without error. So it looks as though the problem may be in
the SQL - are you sure that 'tPeople.Id' is a text field? What about the
join? Are the fields involved in the join compatible

numeric types?

tPeople.Id is an autonumber field. Another query
populates Forms.fMyForm.ComboBoxField with this value.

If you still can't see the problem, I'd suggest testing the query in the
query designer. Enter your SQL in SQL View same as in the code, except
without the quotes and line continuation characters, obviously, make sure
the form is open and something is selected from the combo box, and switch
the query to datasheet view. Do you get any errors? Does the query return
any data?

I have run the query in Access as suggested substituting
the known values for Forms.fMyForm.ComboBoxField and it
runs fine, returns the expected result with no errors.

 

[Brendan Reynolds]

Well, a text box doesn't have a Caption property any more than a combo box
does, so that doesn't change that part of my response. But if tPeople.Id is
an autonumber field, there's a problem there, because in your code you have
it surrounded by quotes, and that is incorrect for a numeric field. If you
entered the SQL into the query designer exactly as you had it in your code,
I would have expected a data-type mismatch error. I guess I should test that
....

SELECT * FROM tblTest WHERE TestID = '1'

Yup, data type mismatch in criteria expression.

Are you sure you didn't leave out the single quotes when you tested in the
query designer?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Louie Warren]

I'm such an idiot...

1. Not a text box, a label...

2. Removing the quotes fixed it.

Thanx!

L