deleting causes program to hang

[Guest]

Hi,
I wanted to ask if anyone has experienced a program hang caused by selecting
a row in an Access database on a form and pushing the delete key?

There are many forms in this program and in a few areas I have written code
in form_delete to perform some other actions along with the deletion of that
record. There are places where this works fine but there is one place where
certain users are reporting that this causes the program to hang and they
only way they can get out of it is to use Task Manager and kill the program.

I cannot recreate this problem on my machine. The program has the code and
data tables separated. I have copied data tables to my machine and the
problem will not occur on my machine so I don't think this has to do with the
data.

One thing I am very curious about is whether this could have anything to do
with Access Runtime libraries. I distribute this program with runtime
libraries. Most users have Office (including Access) installed on their
machines but there are some that do not. I have noticed that some of the ones
that report this problem do not have Access installed on their machines. But
yet, the deleting will work under the same circumstances in other areas.

Example: I have two tables that contain medical information tblLog and
tblHealth. These tables are not related using referential integrity so when a
record is deleted from tblLog I find the record in tblHealth using a sql
statement and delete that record with a db.execute(sql) statement. This works
fine

But in financial records I have tblIncome and tblIncomeDetail that are
related with cascading deletes. I also have tblOtherIncome which is not
related using Access but has records that I need to delete based on the
tblIncome record being deleted. Most of the time this works fine but a few
users are experiencing a system hang as soon as they hit the delete key to
perform the deletion of any row.

Can anyone shed some light on this situation? Thanks!!!

 

[Ken Snell \(MVP\)]

Does your code loop at any point until the deletion is done (for example, if
it finds a lock on a table's record that must be deleted, does it loop back
and try the deletion again)?

 

[Guest]

No - there is just an if statement (if condition then set sql to delete
record and run db.execute(sql)) followed by a requery statment
(parent![frmincomeSF].requery) followed by one more if statement (if records
exist in another table then set sql to delete that record and run
db.execute(sql).

I added a bunch of msgbox statements to try to find out if any of the code
was being executed. I put them around each line of code and I saw all the
msgbox's before the first if statement and in the if statement but I did not
see any after the endif. So I commented out the the requery statement and
then it ran the entire first if statment and then I saw the msgbox's before
the second if statment and inside the second if statment but did not see the
one after the endif. Program was hung again.

This is only in the income form. In the medical form there is also an if
statement and sql to delete a record from a different table and everything
works fine there.

The deletion doesn't actually take place until after the end of form_delete
right? Because I do use the ID key from the record being deleted in order to
delete the records in the other tables.

 

[Ken Snell \(MVP\)]

Sorry - have been busy with work items.. Will reply as soon as possible.

 

[Ken Snell \(MVP\)]

Without seeing your code and better understanding the process, I don't think
I can add any specific suggestions at the moment. The form deletes the
record from the form during the Form_Delete event, but the record is not
actually deleted from the table until after the Form_Delete event is
completed, I believe. As for using the PK of a record, I'd have to know more
about when you read it before I can give suggestions. However, it's always
best to read the PK value in the Form_BeforeDeleteConfirm event (and store
it in a global, private, form-level variable or write it to an invisible
textbox on the form) so that you can be assured that the value is available
when you need it.

--

Ken Snell
<MS ACCESS MVP>

No - there is just an if statement (if condition then set sql to delete
record and run db.execute(sql)) followed by a requery statment
(parent![frmincomeSF].requery) followed by one more if statement (if
records
exist in another table then set sql to delete that record and run
db.execute(sql).

I added a bunch of msgbox statements to try to find out if any of the code
was being executed. I put them around each line of code and I saw all the
msgbox's before the first if statement and in the if statement but I did
not
see any after the endif. So I commented out the the requery statement and
then it ran the entire first if statment and then I saw the msgbox's
before
the second if statment and inside the second if statment but did not see
the
one after the endif. Program was hung again.

This is only in the income form. In the medical form there is also an if
statement and sql to delete a record from a different table and everything
works fine there.

The deletion doesn't actually take place until after the end of
form_delete
right? Because I do use the ID key from the record being deleted in order
to
delete the records in the other tables.

Does your code loop at any point until the deletion is done (for example,
if
it finds a lock on a table's record that must be deleted, does it loop
back
and try the deletion again)?