By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,600 Members | 1,108 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,600 IT Pros & Developers. It's quick & easy.

Error on: DoCmd.RunCommand acCmdDeleteRecord

P: n/a
I have a command button on a subform to delete a record.
The only statement in the subroutine is:
DoCmd.RunCommand acCmdDeleteRecord

The subform's recordsource is "select * from tblVisit order by VisitDt"

I'm getting this error message:
Errno is 2465. Err.description is "Can't find field '|' referred to in your
expression"

The record is successfully deleted.

Where should I look for this problem? Completely mystified.

thx in advance for any advice anyone can offer.

Linda

Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Hi, Linda.

Perhaps you have a similar problem to this one:

http://groups.google.co.uk/groups?hl...b0bc52e&rnum=1

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
"Squirrel" <wi*****@covad.net> wrote in message
news:78*************************@msgid.meganewsser vers.com...
I have a command button on a subform to delete a record.
The only statement in the subroutine is:
DoCmd.RunCommand acCmdDeleteRecord

The subform's recordsource is "select * from tblVisit order by VisitDt"

I'm getting this error message:
Errno is 2465. Err.description is "Can't find field '|' referred to in your expression"

The record is successfully deleted.

Where should I look for this problem? Completely mystified.

thx in advance for any advice anyone can offer.

Linda


Nov 13 '05 #2

P: n/a
Gunny,

thx for your advice. My problem is occurring in an Access 2002 (SP3)
database. I have Access 2003 on my laptop. I'll try it there and see if it
performs differently. I guess the good news is that, even though an error
message is generated, the attempt to delete the record is successful...

Linda
"'69 Camaro" <Fo**************************@Spameater.orgZERO_SP AM> wrote in
message news:khVCd.25840$L7.21511@trnddc05...
Hi, Linda.

Perhaps you have a similar problem to this one:

http://groups.google.co.uk/groups?hl...b0bc52e&rnum=1

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
"Squirrel" <wi*****@covad.net> wrote in message
news:78*************************@msgid.meganewsser vers.com...
I have a command button on a subform to delete a record.
The only statement in the subroutine is:
DoCmd.RunCommand acCmdDeleteRecord

The subform's recordsource is "select * from tblVisit order by VisitDt"

I'm getting this error message:
Errno is 2465. Err.description is "Can't find field '|' referred to in

your
expression"

The record is successfully deleted.

Where should I look for this problem? Completely mystified.

thx in advance for any advice anyone can offer.

Linda



Nov 13 '05 #3

P: n/a
Hi, Linda.

If the problem is due to the bug in MS Office XP SP-3, then the other good
news is that this version is the only version where you'll see this error
message, because Access 2003 and other service packs for MS Office XP don't
have the bug.

If this is the case, then using the menu or either of the RunCommand or
obsolete DoMenuItem methods to delete the record will result in the error
message, but it can be ignored because the record will obviously be deleted.
If you want to avoid the error message, then use VBA code in your command
button's OnClick( ) event such as the following:

' *** Code Start ***

Private Sub DeleteBtn_Click( )

On Error GoTo ErrHandler

Dim db As Database
Dim sqlStmt As String
Dim fOpenedDB As Boolean

sqlStmt = "DELETE * " & _
"FROM tblStuff " & _
"WHERE ID = " & Me!txtID.Value & ";"

Set db = CurrentDb()
fOpenedDB = True
db.Execute sqlStmt, dbFailOnError
Me.Requery

CleanUp:

If (fOpenedDB) Then
db.Close
fOpenedDB = False
End If

Set db = Nothing

Exit Sub

ErrHandler:

MsgBox "Error in DeleteBtn_Click( ) in " & vbCrLf & Me.Name & " form." &
_
vbCrLf & "Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp

End Sub

' *** Code End ***

In this example, ID is the primary key of the record to be deleted, txtID is
the text box bound to this field, and tblStuff is the name of the table the
form is bound to.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
"Squirrel" <wi*****@covad.net> wrote in message
news:ef**************************@msgid.meganewsse rvers.com...
Gunny,

thx for your advice. My problem is occurring in an Access 2002 (SP3)
database. I have Access 2003 on my laptop. I'll try it there and see if it performs differently. I guess the good news is that, even though an error
message is generated, the attempt to delete the record is successful...

Linda
"'69 Camaro" <Fo**************************@Spameater.orgZERO_SP AM> wrote in message news:khVCd.25840$L7.21511@trnddc05...
Hi, Linda.

Perhaps you have a similar problem to this one:

http://groups.google.co.uk/groups?hl...b0bc52e&rnum=1

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
"Squirrel" <wi*****@covad.net> wrote in message
news:78*************************@msgid.meganewsser vers.com...
I have a command button on a subform to delete a record.
The only statement in the subroutine is:
DoCmd.RunCommand acCmdDeleteRecord

The subform's recordsource is "select * from tblVisit order by VisitDt"
I'm getting this error message:
Errno is 2465. Err.description is "Can't find field '|' referred to
in your
expression"

The record is successfully deleted.

Where should I look for this problem? Completely mystified.

thx in advance for any advice anyone can offer.

Linda




Nov 13 '05 #4

P: n/a
Hi Gunny,

The database and my original code runs fine on Access 2003. I took the lazy
way out in the Access 2002 version - see below. Really appreciate your
help. I had no idea where to look for any error in my code - since there
was only one statement!! :-)

On Error Resume Next
DoCmd.RunCommand acCmdDeleteRecord

Is this OK or probably bad style and I should have rewritten the code per
your sample code so generously offered below?

thx so much.
Linda
"'69 Camaro" <Fo**************************@Spameater.orgZERO_SP AM> wrote in
message news:7bdDd.17099$hc7.5636@trnddc06...
Hi, Linda.

If the problem is due to the bug in MS Office XP SP-3, then the other good
news is that this version is the only version where you'll see this error
message, because Access 2003 and other service packs for MS Office XP don't have the bug.

If this is the case, then using the menu or either of the RunCommand or
obsolete DoMenuItem methods to delete the record will result in the error
message, but it can be ignored because the record will obviously be deleted. If you want to avoid the error message, then use VBA code in your command
button's OnClick( ) event such as the following:

' *** Code Start ***

Private Sub DeleteBtn_Click( )

On Error GoTo ErrHandler

Dim db As Database
Dim sqlStmt As String
Dim fOpenedDB As Boolean

sqlStmt = "DELETE * " & _
"FROM tblStuff " & _
"WHERE ID = " & Me!txtID.Value & ";"

Set db = CurrentDb()
fOpenedDB = True
db.Execute sqlStmt, dbFailOnError
Me.Requery

CleanUp:

If (fOpenedDB) Then
db.Close
fOpenedDB = False
End If

Set db = Nothing

Exit Sub

ErrHandler:

MsgBox "Error in DeleteBtn_Click( ) in " & vbCrLf & Me.Name & " form." & _
vbCrLf & "Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp

End Sub

' *** Code End ***

In this example, ID is the primary key of the record to be deleted, txtID is the text box bound to this field, and tblStuff is the name of the table the form is bound to.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
"Squirrel" <wi*****@covad.net> wrote in message
news:ef**************************@msgid.meganewsse rvers.com...
Gunny,

thx for your advice. My problem is occurring in an Access 2002 (SP3)
database. I have Access 2003 on my laptop. I'll try it there and see if
it
performs differently. I guess the good news is that, even though an error message is generated, the attempt to delete the record is successful...

Linda
"'69 Camaro" <Fo**************************@Spameater.orgZERO_SP AM> wrote

in
message news:khVCd.25840$L7.21511@trnddc05...
Hi, Linda.

Perhaps you have a similar problem to this one:

http://groups.google.co.uk/groups?hl...b0bc52e&rnum=1

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message will be forwarded to me.)
"Squirrel" <wi*****@covad.net> wrote in message
news:78*************************@msgid.meganewsser vers.com...
> I have a command button on a subform to delete a record.
> The only statement in the subroutine is:
> DoCmd.RunCommand acCmdDeleteRecord
>
> The subform's recordsource is "select * from tblVisit order by

VisitDt" >
> I'm getting this error message:
> Errno is 2465. Err.description is "Can't find field '|' referred to in your
> expression"
>
> The record is successfully deleted.
>
> Where should I look for this problem? Completely mystified.
>
> thx in advance for any advice anyone can offer.
>
> Linda
>
>
>
>
>



Nov 13 '05 #5

P: n/a
Hi, Linda.
The database and my original code runs fine on Access 2003. I took the lazy way out in the Access 2002 version - see below. Really appreciate your
help. I had no idea where to look for any error in my code - since there
was only one statement!! :-)

On Error Resume Next
At least you won't take offense when I tell you that this is the lazy way to
do it. ;-)

The tradeoff to using the code I provided is that there's more code to
maintain. The issue is only a problem in the one version of Access that has
the bug (and this bug may be fixed in the next service pack, if there is
one), so putting in extra effort that no other version of Access needs is a
judgement call.

However, you mentioned that this was the only line of code in the procedure
before you added the "On Error Resume Next" statement, which means that you
didn't have any error handling previously. Personally, I put error handling
in most procedures even if there's only one line of executable code. I do
this because I will know exactly which procedure to look at when something
unexpected happens, and I get the benefit of being shown the error number
and description. Also, the next time I revisit the procedure and need to
add code that ought to have error handling, I don't have to stop to add the
error handling, too.

If the "On Error Resume Next" statement is your preferred method of error
handling, then you _will_ be offended when I tell you that's the lazy way.
I've probably written more than 250,000 lines of VBA code and I've only
found five occasions so far that warranted the "On Error Resume Next"
statement instead of code that either avoided or handled the error.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
"Squirrel" <wi*****@covad.net> wrote in message
news:89**************************@msgid.meganewsse rvers.com... Hi Gunny,

The database and my original code runs fine on Access 2003. I took the lazy way out in the Access 2002 version - see below. Really appreciate your
help. I had no idea where to look for any error in my code - since there
was only one statement!! :-)

On Error Resume Next
DoCmd.RunCommand acCmdDeleteRecord

Is this OK or probably bad style and I should have rewritten the code per
your sample code so generously offered below?

thx so much.
Linda
"'69 Camaro" <Fo**************************@Spameater.orgZERO_SP AM> wrote in message news:7bdDd.17099$hc7.5636@trnddc06...
Hi, Linda.

If the problem is due to the bug in MS Office XP SP-3, then the other good
news is that this version is the only version where you'll see this error message, because Access 2003 and other service packs for MS Office XP don't
have the bug.

If this is the case, then using the menu or either of the RunCommand or
obsolete DoMenuItem methods to delete the record will result in the error message, but it can be ignored because the record will obviously be

deleted.
If you want to avoid the error message, then use VBA code in your command button's OnClick( ) event such as the following:

' *** Code Start ***

Private Sub DeleteBtn_Click( )

On Error GoTo ErrHandler

Dim db As Database
Dim sqlStmt As String
Dim fOpenedDB As Boolean

sqlStmt = "DELETE * " & _
"FROM tblStuff " & _
"WHERE ID = " & Me!txtID.Value & ";"

Set db = CurrentDb()
fOpenedDB = True
db.Execute sqlStmt, dbFailOnError
Me.Requery

CleanUp:

If (fOpenedDB) Then
db.Close
fOpenedDB = False
End If

Set db = Nothing

Exit Sub

ErrHandler:

MsgBox "Error in DeleteBtn_Click( ) in " & vbCrLf & Me.Name & " form." &
_
vbCrLf & "Error #" & Err.Number & vbCrLf &
Err.Description Err.Clear
GoTo CleanUp

End Sub

' *** Code End ***

In this example, ID is the primary key of the record to be deleted, txtID is
the text box bound to this field, and tblStuff is the name of the table

the
form is bound to.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
"Squirrel" <wi*****@covad.net> wrote in message
news:ef**************************@msgid.meganewsse rvers.com...
Gunny,

thx for your advice. My problem is occurring in an Access 2002 (SP3)
database. I have Access 2003 on my laptop. I'll try it there and see

if
it
performs differently. I guess the good news is that, even though an

error message is generated, the attempt to delete the record is successful...
Linda
"'69 Camaro" <Fo**************************@Spameater.orgZERO_SP AM> wrote in
message news:khVCd.25840$L7.21511@trnddc05...
> Hi, Linda.
>
> Perhaps you have a similar problem to this one:
>
>
http://groups.google.co.uk/groups?hl...b0bc52e&rnum=1 >
> HTH.
>
> Gunny
>
> See http://www.QBuilt.com for all your database needs.
> See http://www.Access.QBuilt.com for Microsoft Access tips.
>
> (Please remove ZERO_SPAM from my reply E-mail address, so that a message > will be forwarded to me.)
>
>
> "Squirrel" <wi*****@covad.net> wrote in message
> news:78*************************@msgid.meganewsser vers.com...
> > I have a command button on a subform to delete a record.
> > The only statement in the subroutine is:
> > DoCmd.RunCommand acCmdDeleteRecord
> >
> > The subform's recordsource is "select * from tblVisit order by

VisitDt"
> >
> > I'm getting this error message:
> > Errno is 2465. Err.description is "Can't find field '|' referred

to in
> your
> > expression"
> >
> > The record is successfully deleted.
> >
> > Where should I look for this problem? Completely mystified.
> >
> > thx in advance for any advice anyone can offer.
> >
> > Linda
> >
> >
> >
> >
> >
>
>



Nov 13 '05 #6

P: n/a
Hi Gunny,

I wasn't clear. When I said the routine had only one statement I meant one
executable statement
in the body of the routine, excluding error handling.

This is my usual style of error handling, with every routine in a form
module terminating in
this fashion:

Exit_cmdAddRecord_Click:
Exit Sub

Err_cmdAddRecord_Click:
MsgBox Me.Name & vbCrLf & _
Err.Number & vbCrLf & Err.Description & vbCrLf & _
"Patient cmdAddRecord"
Resume Exit_cmdAddRecord_Click

End Sub

and it was due to this error handler that the operation failed while
providing this info:
Errno of 2465. Err.description of "Can't find field '|' referred to in
your expression"

"On Error Resume Next" is not my usual style, I only chose this route
because the 'offending'
statement is good and the operation successful and there are no other
executable statements
in the routine, therefore nothing else can fail. I am shamed nevertheless.
:-)

I REALLY appreciate your sharing your knowledge with me. You're very
generous. Thank you very much.

BTW while testing my database on Access 2003 and discovering no error with
regard the record
deletion operation - good news, something that WAS working in Access 2002,
ungraciously
failed but I did manage to figure this one out by myself! :-)

In Access 2002 this recordsource was accepted for an unbound control on my
form:
=[subformPatientVisit]![cboVisit].[column](0)
Access 2003 required this statement:
=[subformPatientVisit].[Form]![cboVisit].[column](0)

(The Access 2003 version works fine in Access 2002 too.)

thx once again.
Linda
"'69 Camaro" <Fo**************************@Spameater.orgZERO_SP AM> wrote in
message news:EZVDd.1081$C.762@trnddc05...
Hi, Linda.
The database and my original code runs fine on Access 2003. I took the lazy
way out in the Access 2002 version - see below. Really appreciate your
help. I had no idea where to look for any error in my code - since there
was only one statement!! :-)

On Error Resume Next


At least you won't take offense when I tell you that this is the lazy way

to do it. ;-)

The tradeoff to using the code I provided is that there's more code to
maintain. The issue is only a problem in the one version of Access that has the bug (and this bug may be fixed in the next service pack, if there is
one), so putting in extra effort that no other version of Access needs is a judgement call.

However, you mentioned that this was the only line of code in the procedure before you added the "On Error Resume Next" statement, which means that you didn't have any error handling previously. Personally, I put error handling in most procedures even if there's only one line of executable code. I do
this because I will know exactly which procedure to look at when something
unexpected happens, and I get the benefit of being shown the error number
and description. Also, the next time I revisit the procedure and need to
add code that ought to have error handling, I don't have to stop to add the error handling, too.

If the "On Error Resume Next" statement is your preferred method of error
handling, then you _will_ be offended when I tell you that's the lazy way.
I've probably written more than 250,000 lines of VBA code and I've only
found five occasions so far that warranted the "On Error Resume Next"
statement instead of code that either avoided or handled the error.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
"Squirrel" <wi*****@covad.net> wrote in message
news:89**************************@msgid.meganewsse rvers.com...
Hi Gunny,

The database and my original code runs fine on Access 2003. I took the lazy
way out in the Access 2002 version - see below. Really appreciate your
help. I had no idea where to look for any error in my code - since there was only one statement!! :-)

On Error Resume Next
DoCmd.RunCommand acCmdDeleteRecord

Is this OK or probably bad style and I should have rewritten the code per your sample code so generously offered below?

thx so much.
Linda
"'69 Camaro" <Fo**************************@Spameater.orgZERO_SP AM> wrote

in
message news:7bdDd.17099$hc7.5636@trnddc06...
Hi, Linda.

If the problem is due to the bug in MS Office XP SP-3, then the other good news is that this version is the only version where you'll see this error message, because Access 2003 and other service packs for MS Office XP

don't
have the bug.

If this is the case, then using the menu or either of the RunCommand or obsolete DoMenuItem methods to delete the record will result in the error message, but it can be ignored because the record will obviously be

deleted.
If you want to avoid the error message, then use VBA code in your command button's OnClick( ) event such as the following:

' *** Code Start ***

Private Sub DeleteBtn_Click( )

On Error GoTo ErrHandler

Dim db As Database
Dim sqlStmt As String
Dim fOpenedDB As Boolean

sqlStmt = "DELETE * " & _
"FROM tblStuff " & _
"WHERE ID = " & Me!txtID.Value & ";"

Set db = CurrentDb()
fOpenedDB = True
db.Execute sqlStmt, dbFailOnError
Me.Requery

CleanUp:

If (fOpenedDB) Then
db.Close
fOpenedDB = False
End If

Set db = Nothing

Exit Sub

ErrHandler:

MsgBox "Error in DeleteBtn_Click( ) in " & vbCrLf & Me.Name & " form."
&
_
vbCrLf & "Error #" & Err.Number & vbCrLf &

Err.Description Err.Clear
GoTo CleanUp

End Sub

' *** Code End ***

In this example, ID is the primary key of the record to be deleted, txtID
is
the text box bound to this field, and tblStuff is the name of the table the
form is bound to.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a
message will be forwarded to me.)
"Squirrel" <wi*****@covad.net> wrote in message
news:ef**************************@msgid.meganewsse rvers.com...
> Gunny,
>
> thx for your advice. My problem is occurring in an Access 2002 (SP3) > database. I have Access 2003 on my laptop. I'll try it there and

see if
it
> performs differently. I guess the good news is that, even though an

error
> message is generated, the attempt to delete the record is

successful... >
> Linda
>
>
> "'69 Camaro" <Fo**************************@Spameater.orgZERO_SP AM> wrote in
> message news:khVCd.25840$L7.21511@trnddc05...
> > Hi, Linda.
> >
> > Perhaps you have a similar problem to this one:
> >
> > http://groups.google.co.uk/groups?hl...b0bc52e&rnum=1 > >
> > HTH.
> >
> > Gunny
> >
> > See http://www.QBuilt.com for all your database needs.
> > See http://www.Access.QBuilt.com for Microsoft Access tips.
> >
> > (Please remove ZERO_SPAM from my reply E-mail address, so that a

message
> > will be forwarded to me.)
> >
> >
> > "Squirrel" <wi*****@covad.net> wrote in message
> > news:78*************************@msgid.meganewsser vers.com...
> > > I have a command button on a subform to delete a record.
> > > The only statement in the subroutine is:
> > > DoCmd.RunCommand acCmdDeleteRecord
> > >
> > > The subform's recordsource is "select * from tblVisit order by
VisitDt"
> > >
> > > I'm getting this error message:
> > > Errno is 2465. Err.description is "Can't find field '|'
referred to in
> > your
> > > expression"
> > >
> > > The record is successfully deleted.
> > >
> > > Where should I look for this problem? Completely mystified.
> > >
> > > thx in advance for any advice anyone can offer.
> > >
> > > Linda
> > >
> > >
> > >
> > >
> > >
> >
> >
>
>



Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.