General discussion


Changing data in a form with a sub form

By gmiller ·
I have created a form that includes a sub-form. The form shows BOAT details
(Attributes= BoatID, BoatName and TrailerRego) and the sub-form shows the
MemberID of the member/s that are owners of the boat. This is a many-to-many
relationship because a MEMBER can own more than one boat and a BOAT can be
owned by more than one MEMBER. I obviously have a MEMBER table and a BOAT
table. I also have an OWNS table that lists BoatID and MemberID to show who
owns what.
My form has to allow the user to add/remove/edit BOATs and the sub-form has
to allow the user to add one or more MemberIDs for any BOAT. This seems to
work ok when adding BOATs; MemberIDs must already exist in the MEMBER table
or it will complain (which is good).
My problem is that I can't delete a BOAT because I get the following error:
"The record cannot be deleted or changed because table 'OWNS' includes
related records".
If I try to remove the MemberID from the Owns sub-form for that BOAT I get
an error because teach BOAT must have at least one OWNer.
The only way I can delete a BOAT is to go to the OWNS table and delete the
record. Then I can delete the BOAT from either the table or from the Form.
I don't want the user to have direct access to any tables, so I need to find
a way to delete a BOAT and OWNer in the form/subform at the same time.
Any cluse anybody????

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Try and ADO or DAO solution

by talentonloan In reply to Changing data in a form ...

You may be running into record locking problems. One work around may be to capture the IDs as variables, move the form off the records in play, then use DAO or ADO code to find the records in each table and delete that way. (updating the recordset) It would be hidden from users.


Collapse -

Delete using code or use relationships

by John Gordon In reply to Changing data in a form ...

Have you tried writing a bit of VB, using the docmd.runsql to delete all records in relevant records in OWNS for example
"DELETE * FROM OWNS WHERE BoatID = xxxxx" - where xxxxx is the BoatID you want to delete.
Then do something similar to delete the records from the BOAT table for example

Alternatively create a relationship (Tools menu/Relationships) between BOAT and OWNS and select 'Cascade Delete Related Records'. This should ensure that all related records in OWNS are deleted when a record in BOAT is deleted. I would test this out on a copy of the database to make sure you have created the relationship properly.

Related Discussions

Related Forums