Code quits after record delete

[Dave]

No matter what I've done, code quits running after a record deletion
(even a simple Msgbox "OK" to test after the deletion does not
trigger).
Relevant (?) form properties: continuous, allow additions (no); data
entry (no); record locks (edited record). I have tried both the
DoMenuItem and RunCommand acCmdDeleteRecord methods with no
difference. I also migrated the record deletion portion to a form
function thinking that if it were external to the button click event
sub, it would HAVE to return program control back to the sub and
execute the rest of the code. If I rem out the delete portion to test
the variables, all is OK. Here's my code (error trapping removed for
brevity):

Private Sub cmdDelRec_Click()
Dim recDeleted As Boolean
recDeleted = delRecord
msgbox "OK"
If recDeleted = True Then
Me.Requery
recalcAmounts strDept, lngRel
End If
End Sub

Function delRecord() As Boolean
If Me.CurrentRecord > 0 Then
delRecord = True
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
Else
delRecord = False
End If
End Function

Could someone please tell me why the code stops executing after the
record delete and how I might overcome it? Using Access 2002, XP, SP3
P.S. the form recordset is a temp table designed to facilitate
temporary calculations so it has no primary key. Using a delete sql
would be problematic due to reams of code that would have to be
modified.

 

[Dirk Goldgar]

No matter what I've done, code quits running after a record deletion
(even a simple Msgbox "OK" to test after the deletion does not
trigger).
Relevant (?) form properties: continuous, allow additions (no); data
entry (no); record locks (edited record). I have tried both the
DoMenuItem and RunCommand acCmdDeleteRecord methods with no
difference. I also migrated the record deletion portion to a form
function thinking that if it were external to the button click event
sub, it would HAVE to return program control back to the sub and
execute the rest of the code. If I rem out the delete portion to test
the variables, all is OK. Here's my code (error trapping removed for
brevity):

Private Sub cmdDelRec_Click()
Dim recDeleted As Boolean
recDeleted = delRecord
msgbox "OK"
If recDeleted = True Then
Me.Requery
recalcAmounts strDept, lngRel
End If
End Sub

Function delRecord() As Boolean
If Me.CurrentRecord > 0 Then
delRecord = True
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
Else
delRecord = False
End If
End Function

Could someone please tell me why the code stops executing after the
record delete and how I might overcome it? Using Access 2002, XP, SP3
P.S. the form recordset is a temp table designed to facilitate
temporary calculations so it has no primary key. Using a delete sql
would be problematic due to reams of code that would have to be
modified.

I don't see any reason why the code would stop, and it doesn't do that for
me in Access 2003 under Vista. Is that an exact quote of your code? If so,
something odd is going on, becase the function MsgBox is not capitalized
properly, and I'd expect the VB editor to do that automatically. Have you
defined something named "msgbox" that might be blocking the execution of the
real MsgBox function, or stopping your code?

Have you tried setting breakpoints in your code and stepping through to see
exactly what happens?

 

[Dave]

I don't see any reason why the code would stop, and it doesn't do that for
me in Access 2003 under Vista.  Is that an exact quote of your code?  If so,
something odd is going on, becase the function MsgBox is not capitalized
properly, and I'd expect the VB editor to do that automatically.  Have you
defined something named "msgbox" that might be blocking the execution of the
real MsgBox function, or stopping your code?

Have you tried setting breakpoints in your code and stepping through to see
exactly what happens?

--
Dirk Goldgar, MS Access MVP
Access tips:www.datagnostics.com/tips.html

(please reply to the newsgroup)- Hide quoted text -

- Show quoted text -

I typed the line into my message after I realized I made reference to
it but didn't have it in the latest version. Otherwise, the code is an
exact cut and paste (you're very astute!). I have tried breakpoints,
but it generates error 2046 at DoCmd.RunCommand acCmdSelectRecord
because the VBE is active and not the form. I could not find a way
around that (even tried SelectObject on the form). If I comment out
the record deletion part, msgbox works. Because it worked for you, I
imported the table into a new 2002 db with default references, created
a form & button with the wizard, substituted my code and it worked.
Assuming db corruption, I imported all objects into a new db, set the
same refs as the original project and it didn't work. So it seems to
be either a form or reference issue. The project refs are (shortened):
VBA; MSA 10 Obj. Lib; OLE; DAO 3.6; VBA Extensibility 5.3 and Office
12 Obj. Lib.; ActiveX Data Obj. 2.7. Using Office 10 will generate the
2046 err (record can't be deleted), and AxDo 2.5 vs 2.7 makes no
difference to the problem-don't know what else to try. If a form is
corrupt and you import it into a new db, does it maintain the
corruption? Do you think this is a reference issue? Thanks for your
opinion!

 

[Dirk Goldgar]

I typed the line into my message after I realized I made reference to it
but didn't have it in the latest version.

Ah, okay.

I have tried breakpoints,but it generates error 2046 at DoCmd.RunCommand
acCmdSelectRecord because the VBE is active and not the form. I could not
find a way around that (even tried SelectObject on the form).

You could clear all breakpoints the would be reached before the deletion
itself, set a breakpoint on the line immediately following the "RunCommand
acCmdDeleteRecord", see if you get there, and step forward from there.
Incidentally, if you are going to use RunCommand acCmdDeleteRecord, I don't
believe you need the RunCommand acCmdSelectRecord before it.

Because it worked for you, I imported the table into a new 2002 db with
default references, created a form & button with the wizard, substituted
my code and it worked. Assuming db corruption, I imported all objects into
a new db, set the same refs as the original project and it didn't work. So
it seems to be either a form or reference issue.

Interesting; that sounds like a reasonable conclusion.

The project refs are (shortened):
VBA; MSA 10 Obj. Lib; OLE; DAO 3.6; VBA Extensibility 5.3 and Office 12
Obj. Lib.; ActiveX Data Obj. 2.7.

So you have both Office 10 and Office 12 installed on your computer? There
could be something going on there. You almost certainly don't need the
reference to the VBA Extensibility library, and if you're using Access 10,
your Office library reference should be to the Office 10 library, not Office
12.

Does the database open and run correctly under Access 2007? Or doesn't your
Office 12 installation include Access?

Using Office 10 will generate the 2046 err (record can't be deleted),

Could you expand on that, please?

and AxDo 2.5 vs 2.7 makes no difference to the problem

It shouldn't. I usually set the ADO reference to the earliest version
available.

-don't know what else to try. If a form is corrupt and you import it into a
new db, does it maintain the corruption?

Usually not, but it can. You can export the form to text using the hidden
Application.SaveAsText method:

Application.SaveAsText acForm, "YourFormName", CurrentProject.Path &
"\YourForm.txt"

And then load it from the text file in the new database using the
LoadFromText method:

Application.LoadFromText acForm, "YourFormName", CurrentProject.Path &
"\YourForm.txt"

Do you think this is a reference issue?

It could be, and/or it could be a problem with conflicting installations of
Office 10 and Office 12. Don't have both versions open at the same time.
Each one wants to reconfigure the installation when you open it. If you've
been switching back and forth, close all open Office programs, open Access
2007 and let it configure itself, then close it, open Access 2002 and let it
configure itself. Then open your database while holding the shift key down
to suppress startup code, open the VB Editor, check the references and make
sure you only have Access 10 and Office 10 references set. Recompile. See
if it works now.

 

[Dave]

Ah, okay.


You could clear all breakpoints the would be reached before the deletion
itself, set a breakpoint on the line immediately following the "RunCommand
acCmdDeleteRecord", see if you get there, and step forward from there.
Incidentally, if you are going to use RunCommand acCmdDeleteRecord, I don't
believe you need the RunCommand acCmdSelectRecord before it.


Interesting;  that sounds like a reasonable conclusion.


So you have both Office 10 and Office 12 installed on your computer?  There
could be something going on there.  You almost certainly don't need the
reference to the VBA Extensibility library, and if you're using Access 10,
your Office library reference should be to the Office 10 library, not Office
12.

Does the database open and run correctly under Access 2007?  Or doesn'tyour
Office 12 installation include Access?


Could you expand on that, please?


It shouldn't.  I usually set the ADO reference to the earliest version
available.

-don't know what else to try. If a form is corrupt and you import it intoa
new db, does it maintain the corruption?

Usually not, but it can.  You can export the form to text using the hidden
Application.SaveAsText method:

    Application.SaveAsText acForm, "YourFormName", CurrentProject.Path &
"\YourForm.txt"

And then load it from the text file in the new database using the
LoadFromText method:

    Application.LoadFromText acForm, "YourFormName", CurrentProject.Path &
"\YourForm.txt"


It could be, and/or it could be a problem with conflicting installations of
Office 10 and Office 12.  Don't have both versions open at the same time.
Each one wants to reconfigure the installation when you open it.  If you've
been switching back and forth, close all open Office programs, open Access
2007 and let it configure itself, then close it, open Access 2002 and letit
configure itself. Then open your database while holding the shift key down
to suppress startup code, open the VB Editor, check the references and make
sure you only have Access 10 and Office 10 references set.  Recompile.  See
if it works now.

--
Dirk Goldgar, MS Access MVP
Access tips:www.datagnostics.com/tips.html

(please reply to the newsgroup)

I like to address all suggestion, even if I think I they're not
related (because I have more info). So, the break point was set after
the deletion as you suggest, which was never reached, hence the msgbox
to try to force a means of knowing the code ran past the record
deletion. We only have Access 2002, so can't follow any suggestions
re: 2007. As I mentioned above, Office 10 causes error 2046, that's
why I used it. I will save your export suggestion, might come in handy
in the future. Now for the kicker:

A filter form opens the main form. If I load the main then paste
append a set of records into the table (because main opening ensures
no old table data related to the filter) it works fine. If I click the
add record button, update that record and go back to one of the
previous records and click to delete, still works fine. The moment any
ADDED record is deleted with the button, the undesirable behaviour
crops up and is propagated to any record, original or added, that is
deleted thereafter. Here's where I probably went wrong (enough
knowledge to be dangerous, I guess):
On opening, the form's recordsource is set to a sql statement and a
recordset declared at the module level (rstCoCalc) is created from it.
The add record button adds the record to the recordset, which adds it
to the table when the form is requeried:

Private Sub cmdListAdd_Click()
With rstCoCalc
.AddNew
!DeptCd = strDept
!RelNo = lngRel
.Update
End With
Me.Requery
End Sub
Perhaps I should delete the record from the recordset, then requery
the form instead? I've never deleted from a recorset, so I'd have to
figure out how. I would need to identify the selected record as there
is really no primary (or composite primary) key to rely on. If you
have anything to offer, I'd appreciate it. Does any of this make my
problem make sense now?
Thanks.