Deleting a record deletes item from combo box's underlying table

[Guest]

A strange problem has recently appeared -- when a user deletes a contact in a
contact data entry form, items from two combo boxes on the form (that draw
their values from two separate tables) are deleted from their respective
tables. The delete button was created with the Wizard, and contains the
following code.

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

I am suspicious of corruption, because I recently could not get an external
report that was correct in my development database to import properly (the
report changed when imported). I finally decompiled, recompiled, and
compacted, and the report imported properly.

Should I decompile again? Is there something else I should look at? (I tried
locking the two controls, but that of course meant that the selections did
not write to the main contacts table, and I need to have them there.)

Any help would be appreciated.

 

[Guest]

Hi Susan

This can happen by setting certain properties although you wouldn't normally
want to.

I presume the form has an underlying query and that this query gets data
from the contacts table and the 2 tables used by the combo boxes.

When a record is viewed in the form and deleted Access has to decide whether
to delete the whole record, ie all items from all tables or just to delete
the items in the contacts table.

To only delete contact info it has to know that the other items are just
"looked up" and the only way it can know this is if the query joins to the
"look up" tables using a primary key.

So, check the "look up" tables to see if they have a primary key and that
this is the column the query uses when joining from the contact table.

If this solves the problem I would also check the following...

Go to the form's property sheet - data tab - Recordset Type should normally
be Dynaset (yours might be Dynaset (inconsistent updates))

Also, what happens if you go into the form and use one of the combo boxes to
change one of these attributes?

Does it just change that contact or does it change the attribute value in
the look up table itself? BACKUP DATABASE before checking this!!!

hth

Andy Hull

 

[Guest]

thanks, Andy. I checked the primary keys on the two combo box tables: in one
the primary key was the same as the column in the contacts table; in the
other it was not, so I changed the PK so it matched the column in the
contacts table.

This change did not solve the problem. The forms recordset type is Dynaset
(Inconsistent Updates). I checked changing the value in the combo box to see
what the change affected. I could only do this in one of the combos (I had
the other set to Limit To List). It only affected the record as it was
recorded in the contacts table -- not in the lookup table. (I changed that
one to Limit to List also, after doing the test.)

Any other suggestions? (the weird thing is that this is only a recent
problem. In past months, there have been a number of records deleted without
this unintended side effect.)

 

[Guest]

Hi again Susan

You changed the primary key for one of the tables - I know I'm stating the
obvious but do make sure that you are happy the new primary key is the right
one.

Also, having done this, are you still getting the same problem?

To test changing the combo box value I meant to just pick another value from
the drop down - sorry for not being clear.

I would be concerned that the form's recordset type is Dynaset (inconsistent
updates). This setting is often used to get around the "problem" of having a
read only query.

If you set it to just Dynaset, are you able to make any changes to records
in the form?

Also, can you post the SQL of the query underlying the form?

Sorry I have posted lots of questions and no answers!

I'm hoping the SQL will give us an idea

Regards

Andy Hull

 

[Guest]

Well, the problem is solved, thanks to your questions and suggestions. The
source of the problem was the Dynaset (Inconsistent Updates). I must have
made that selection because the query was uneditable. What probably made it
uneditable was that the join from one of the lookup tables to the main table
was not the primary key of its table. Once that was changed and I made the
RecordSource Dynaset, the problem went away.

I am so appreciative of your help. This part of our database has suddenly
ssumed greater importance, so "the heat was on" to get this ugly problem
fixed. Thank you again!