Table data being deleted in query, help

[Donna]

I posted this last week, but did not get to follow up with the properties,
so I will toss this out to the group with more info.

I have a main Input table and 5 reference tables: Location, CostCenter,
ExpenseElement and UserID and ReportHdr ( ReportHdr is linked to UserID). I
have relationships set to show all data in Input table and in all 5 other
tables when the records are equal.

I have a query that with all the data from the Input table and the 5
reference tables.

I have a form using the query as record source.

I have 4 combo boxes on the form, using the corresponding reference tables
as their source. (ReportHdr is used at the report level, not on form)

Form properties Allow Deletions set to No, Edits-Yes, Additions-Yes.

I set each of the combo boxes LimitToList to yes, and there is OnNotInList
msg box attached to each of their AfterUpdate events.

I made a copy of this form, gave it a slightly different name,
InputEditData. In my custom menu, I have a menu item set to open the
"InputEditData" form in datasheet view only.

Although the form propery AllowDeletions is set to No, it WILL let you
delete a record in this datasheet view. (In the "form view" form, if you go
to datasheet view, you can not delete a record)

I wouldn't mind if a record needs to be deleted from this editing datasheet
view, however, the big problem is that when a record is deleted, it is also
deleting the selected items from each of the combo boxes, with the exception
of the UserID. For example: the user has selected this:
UserID: Me
Location: Area A
CostCenter: 000
ExpenseElement: 000

The user goes to the Edit datasheet view and decides to delete this record.
When you go to the underlying reference tables: Location: the Area A record
is gone. CostCenter: The record for 000 is gone. ExpenseElement: The record
for 000 is gone. And the HeaderRpt: the record linked to the UserID is gone.
I'm not talking about one cell on the row, I'm talking about the entire
row/record has been deleted. (not hiding)

Can anyone explain to me why/how this happening and how do I protect the
records in the reference tables from being deleted? This is a nightmare!
I've never had anything like this ever happen before. I am totally stumped
trying to figure out how this can occur. I'm obviously missing something
critical. Can anyone explain or point me in the right direction to search
for solution? Thanks everyone! You are a tremendous help!

Donna

 

[Klatuu]

Check your relationship definitions. I think you have Cascading Deletes set
to Yes. This will cause all related records to be deleted when you delete
the base record. In this case, it needs to be set to No.

 

[Donna]

The relationships is the first area I went to. I do not have referential
integrity checked, nor is the cascading deletes or edits checked. I wondered
about having the "Row Level Tracking" checked in the Input table, but that
only appears to let you see the subdatasheet records. I unchecked it anyway,
and still lost records after testing again.I sure have something set up
wrong.... just not sure where it is.

As an added note, in the Input table, I am not using the Lookup section of
the table properties.

also, I completely did away with the relationships in the Relationship
window, and it still deleted records. That only leaves one other place...
the query. It looks like any ordinary select query. No totals, just pulling
in the description fields from all the various reference tables that will be
needed on the reports and forms.

Its got me baffled. Donna

 

[Donna]

The relationships is the first area I went to. I do not have referential
integrity checked, nor is the cascading deletes or edits checked. I wondered
about having the "Row Level Tracking" checked in the Input table, but that
only appears to let you see the subdatasheet records. I unchecked it anyway,
and still lost records after testing again.I sure have something set up
wrong.... just not sure where it is.

As an added note, in the Input table, I am not using the Lookup section of
the table properties.

also, I completely did away with the relationships in the Relationship
window, and it still deleted records. That only leaves one other place...
the query. It looks like any ordinary select query. No totals, just pulling
in the description fields from all the various reference tables that will be
needed on the reports and forms.

Its got me baffled. Donna

 

[Klatuu]

It probably is the query, then. If you are using it as the row source for
your form, then it will act just like a table. Also, with referential
integrity not checked, that is sure to happen. Turn referential integrity
on, but do not turn on cascading deletes.

 

[Donna]

Thanks Klatuu, I will apply that solution and test it out. I think I should
go back to reading Access101 and retouch the topic of Relationships. The
query is my row source for the form. The query has the main table and those
reference tables I mentioned.

One other thing that I forgot to mention: I was able to go to the table view
and delete records without affecting the sources from the reference tables.
For example: the user has selected this:I could delete this record and it did not delete the UserID (Me), Location
(Area A), CostCenter (000), ExpenseElement (000) as was the case in the
datasheet view of the form.

Thank you so very much. It fixed the deletion problem. What makes the
difference in deleting from the query opposed to deleting from the table?
(still scratching my head on this one....) Donna