Looking for help with Runtime Error

[Susan Bricker]

Greetings! Can you help me?

Here's the error:
“Run-time error ‘2001’. You canceled the previous operation.”

Here's the form description:
[frmDataEntry] has 2 controls involved with this error:
[dogregnbr] = unbound text box (expecting user to type in dog
registration number (format: yy-nnnn)
[cboDogs] = unbound combobox with three columns: 1=dogID (key to tblDogs
record) bound column and not displayed , 2=resultant of query displaying
DogFullname, DogCallName, DogOwnerName and 3=regnbr (not displayed)

The intent of the form:
User will either (1) type in known dog reg. nbr and the combobox will
display the dog info or (2) select a dog from the dropdown combobox and
the dog Registration number will be displayed in the text box.
Then the user will select a command button to display record for display
or update.

Problem: When a valid [dogregnbr] is typed in and the TAB key it hit to
force the "afterupdate action" I get the error mentioned above on the
assignment statement to [cboDogs].

Here's the failing function:
Private Sub dogregnbr_AfterUpdate()
Dim strRegDate As String
Dim strRegNo As String
Dim lngRegNo As Long
Dim dummydate As Date
Dim varYear As Variant

strRegDate = "01/01/20" & Left(dogregnbr, 2) 'dummy date
strRegNo = Right(dogregnbr, (Len(dogregnbr) - 3))
varYear = Year(strRegDate)
lngRegNo = CLng(strRegNo)
Me!cboDogs = DLookup("[dogID]", _
"tblDogs", _
"regnbr = '" & lngRegNo & "' AND " & _
"datepart([regdt],yyyy) = " & varYear)
End Sub

The statement that is failing is:
Me!cboDogs = DLookup("[dogID]", _
"tblDogs", _
"regnbr = '" & lngRegNo & "' AND " & _
"datepart([regdt],yyyy) = " & varYear)
Here's the background:
I have a table [tblDogs] with a the following fields:
registration number [regnbr] defined as a long int and registration date
[regstartdt] defined as a short date.

(tblDogs has lots of other fields, but they are not involved in this
problem ... I think)

I envision having [regnbr] defined as an integer in the table but the
real registration number (for display and for reports) is a text field
(string) having the following format: yy-nnnn, where yy=year and nnnn is
the integer in string format.

This is so that the data stored is reduced in size and the user will see
the yy-nnnn registration number.

Questions:
1. Why is the error occurring?
2. Is there a better way to do this?

Thanks, so much.
SueB

*** Sent via Developersdex http://www.developersdex.com ***

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your use of DatePart() is incorrect. The correct usage is:

"DatePart(""yyyy"", regdt) = " & varYear

The interval needs 2 double quotes around it 'cuz it is inside another
pair of double quotes. Normally, it only requires a double qoute
delimiter.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlw9lYechKqOuFEgEQJgVQCg9/1mnAzbMHSxILH9PmmgOyxaLAMAn1I4
6Ht2+YZoaIHjE1f37tfFjbCl
=o965
-----END PGP SIGNATURE-----

Susan Bricker wrote:

Greetings! Can you help me?

Here's the error:
“Run-time error ‘2001’. You canceled the previous operation.”

Here's the form description:
[frmDataEntry] has 2 controls involved with this error:
[dogregnbr] = unbound text box (expecting user to type in dog
registration number (format: yy-nnnn)
[cboDogs] = unbound combobox with three columns: 1=dogID (key to tblDogs
record) bound column and not displayed , 2=resultant of query displaying
DogFullname, DogCallName, DogOwnerName and 3=regnbr (not displayed)

The intent of the form:
User will either (1) type in known dog reg. nbr and the combobox will
display the dog info or (2) select a dog from the dropdown combobox and
the dog Registration number will be displayed in the text box.
Then the user will select a command button to display record for display
or update.

Problem: When a valid [dogregnbr] is typed in and the TAB key it hit to
force the "afterupdate action" I get the error mentioned above on the
assignment statement to [cboDogs].

Here's the failing function:
Private Sub dogregnbr_AfterUpdate()
Dim strRegDate As String
Dim strRegNo As String
Dim lngRegNo As Long
Dim dummydate As Date
Dim varYear As Variant

strRegDate = "01/01/20" & Left(dogregnbr, 2) 'dummy date
strRegNo = Right(dogregnbr, (Len(dogregnbr) - 3))
varYear = Year(strRegDate)
lngRegNo = CLng(strRegNo)
Me!cboDogs = DLookup("[dogID]", _
"tblDogs", _
"regnbr = '" & lngRegNo & "' AND " & _
"datepart([regdt],yyyy) = " & varYear)
End Sub

The statement that is failing is:
Me!cboDogs = DLookup("[dogID]", _
"tblDogs", _
"regnbr = '" & lngRegNo & "' AND " & _
"datepart([regdt],yyyy) = " & varYear)
Here's the background:
I have a table [tblDogs] with a the following fields:
registration number [regnbr] defined as a long int and registration date
[regstartdt] defined as a short date.

(tblDogs has lots of other fields, but they are not involved in this
problem ... I think)

I envision having [regnbr] defined as an integer in the table but the
real registration number (for display and for reports) is a text field
(string) having the following format: yy-nnnn, where yy=year and nnnn is
the integer in string format.

This is so that the data stored is reduced in size and the user will see
the yy-nnnn registration number.

Questions:
1. Why is the error occurring?
2. Is there a better way to do this?

Thanks, so much.
SueB

*** Sent via Developersdex http://www.developersdex.com ***

[Susan Bricker]

Hi, MGFoster. Thanks for the suggestion. I hate to tell you, but I
made the change that you suggested and still got the same error at the
same location. Any other idea?
PLEASE! I'm starting to panic. Ok, maybe I overdramatize, here. But
I'm really stuck. Thanks.

Sueb

*** Sent via Developersdex http://www.developersdex.com ***

[Susan Bricker]

It looks like it's the DATEPART( ) portion of the criteria checking that
is failing.

I gave up on the DLOOKUP( ) and defined a DAO database and recordset.
Then I set a SQL string variable (strSQL) to the following SQL
statement:

strSQL = "SELECT * FROM tblDogs " & _
"WHERE [regnbr] = " & lngRegNo & " " & _
"AND DATEPART(""yyyy"",[regdt]) = " & varYear

and issued the db.opendataset(strSQL)
It still failed.

Then I changed the strSQL to:

strSQL = "SELECT * FROM tblDogs " & _
"WHERE [regnbr] = " & lngRegNo

and it worked.
So ... the question is how do I add the test for YEAR to the SQL
statement. I want to take the 2 digit year from the input Registration
Number and test the Registration Date in the tblDogs record and find a
match on REGNBR (long int) and REGYEAR.

Help!!

Thanks.

SueB
*** Sent via Developersdex http://www.developersdex.com ***

[Susan Bricker]

Is my face RED!!! The problem was that the date field name in the
tblDogs record is NOT [regdt], it's [regstartdt]. So, datepart( ) does,
indeed work, when it has the correct syntax. : )

Sorry for taking up your time with this silly error. But thanks for
your help, anyway.

Sueb

*** Sent via Developersdex http://www.developersdex.com ***

[rkc]

Susan Bricker wrote:

Is my face RED!!! The problem was that the date field name in the
tblDogs record is NOT [regdt], it's [regstartdt]. So, datepart( ) does,
indeed work, when it has the correct syntax. : )

Sorry for taking up your time with this silly error. But thanks for
your help, anyway.

A good argument for using actual words for field names.