Changing data in a form with a sub form

[Guest]

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????

 

[Allen Browne]

You should be able to select the record in the subform (Select Record on
Edit menu), and delete it. That's different from trying to delete the value
in one of the controls in the subform.

If you want Access to automatically delete the entries from the OWNS table
when a boat is deleted in the main form, you can enable cascading deletes.
Choose Relationships from the Tools menu. Presumably you already have a
relationship between BOAT.BoatID and OWNS.BoatID, so:
- Double-click the line joining these 2 fields.
- Make sure the Referential Integrity box is checked.
- Check the Cascading Delete box as well.
Now when you delete a boat, any record(s) of its ownership are deleted as
well.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Guest]

Allen, thankyou, thankyou, thankyou! Nobody can imagine how many times I have
recreated that form/subform in how many different ways,...hours...days...
....you get the picture! Thanks again,
Regards, Greg