Which table is affected by the drop when the form is based on a query?
In MS Access 2003 I have a form whose record source is equal to a query that includes an INNER JOIN. The connection is between the location table and the containers table. Containers are objects that are stored in specific locations, each location and container is indicated by ID values.
SELECT DISTINCTROW Container.Container_ID, Location.Location_ID
FROM Location INNER JOIN Container
ON Location.[Location_ID] = Container.[Location_ID]
What I am trying to figure out is .... When I delete a record (using form controls) in a form based on the above query, which tables are affected? Are the records stored in the container and location tables, or are they only found in the locations table?
a source to share
Without defining the relationship, you will remove the current row from the Container and leave the location unchanged.
However, I think you should add relationships and enforce referential integrity to make sure you can't delete the location where the container is still stored. Otherwise, you might end up writing to the container that references a Location_ID that no longer exists. Relationships may not help with this form and request, but it can protect against errors in other situations.
There are other ways to design a form that will make it easier to understand which record will be removed from that location. You can create a main form in Location and include a container based subform. The properties are set to Location_ID as the link master / child field. Then, to display the Location_ID in the main form, all containers stored in that location will be displayed in the subform. Use subform to remove any container you want.
a source to share
It depends on whether there is a link between tables or not, and for this link the "Cascade Delete Linked Tables" option is enabled.
If you have relationships and Cascading Deletes are enabled, it will make sure to delete the records in the related table.
If referential integrity is applied between two tables, Access will complain if you try to delete the parent record without first deleting all the child records.
It is not clear to me what happens on the form if both tables are present in the request for the form and a delete is requested and cascading deletes are disabled. But this would be easy enough to check.
http://office.microsoft.com/en-us/access/ha011739511033.aspx
a source to share