Subfor delete not allowed

[Dick Patton]

Hi All,

I know i am missing soething here but i can't seem to explain this condition.
The condition exists with all of my forms that contain subforms.

A DB table contains 2 fields, School_name and Unit_number (it is intended to
track which Scout units draw from which schools) both fields are many to many
relationships. A school may have many units drawing scouts from it and unit
may draw from many school but no duplicates are allowd. So both unit_number
and School_name are joint keys in the School-Unit Table. Each of these is
linked to its own tables called Schools (School_name is the Key) and Org_Unit
where Unit_Number is Indexed.

The main form "School" collects the school name, address etc. and the sub
form (School-Unit) collects which units are associated with it. On the
subform is a list box from which the user can select any Valid unit_number
(from the Unit_Org table) and The School_name (collected from athe School
main form)

That being said, The form works perfect when adding units to schools, or
changing units related to schools. But when you right click a record to
delete a Unit-School pair the delete feature is greyed out. Both subform and
Form have Add,Edit,Delete permissions set to yes.

Any help would be appreciated!!

 

[John W. Vinson]

That being said, The form works perfect when adding units to schools, or
changing units related to schools. But when you right click a record to
delete a Unit-School pair the delete feature is greyed out. Both subform and
Form have Add,Edit,Delete permissions set to yes.

My guess is that the relationships are preventing the deletion of a *parent*
record in a one-to-many relationship. What's the Recordsource of the form?
Might it be a query joining two of the tables? If so it's going to try to
delete a school (or perhaps a unit) which may well still have a different
related record.

 

[Dick Patton]

Thanks for replying John.

The record source in the subform reads:

SELECT [School-Unit].[Unit_number], [School-Unit].[School_Name] FROM
[School-Unit];

Basiclly the sub form creastes records in a table called School-Unit
containing two Key fields Unit_number and School_name. These form a pair of
Keys to link the Schools table with the Org_unit table in 1 to many fashon.
Once a record in this table is created the sub form will not allow deletion
(delete is Greyed out) of the link between the two tables. So the child
records can only be delted by manually editing the table. By the was
referential Integrity is turnd on for both sides of the link so if a school
or unit is delted the links will be deleted.

A picture is worth a 1000 words, oh how i wish i could paste the Schema in
her so you could see it.

 

[John W. Vinson]

Thanks for replying John.

The record source in the subform reads:

SELECT [School-Unit].[Unit_number], [School-Unit].[School_Name] FROM
[School-Unit];

Basiclly the sub form creastes records in a table called School-Unit
containing two Key fields Unit_number and School_name. These form a pair of
Keys to link the Schools table with the Org_unit table in 1 to many fashon.
Once a record in this table is created the sub form will not allow deletion
(delete is Greyed out) of the link between the two tables. So the child
records can only be delted by manually editing the table. By the was
referential Integrity is turnd on for both sides of the link so if a school
or unit is delted the links will be deleted.

A picture is worth a 1000 words, oh how i wish i could paste the Schema in
her so you could see it.

I would expect tables such as:

Schools
SchoolID (well, SchoolName if you unwisely use a name as a primary key)
School_Name
<information about the school as an entity>

Units
Unit_Number
Unit_Name
<other information about the unit>

SchoolUnits <many to many resolver table>
SchoolID <foreign key linked to Schools.SchoolID>
Unit_Number <foreign key linked to Units.Unit_Number>

Is this in fact your structure? Check the "Allow Deletes" property of the
subform: perhaps the problem is that the FORM isn't permitting deletion.

 

[Dick Patton]

Hi My Friend,

Yes, I Did, because the school name does not generally change and is
supplied by the master Boy Scout Data Base. I could chage the key, and use
an autonumber as I did with merit badges, however, a school is a school and
only needs to be correct one time!

I will send the DB as you suggested today so we can review the schema
together.

Ones again thanks for your support!
--
Dick Patton
Boy Scout Troop 403

Thanks for replying John.

The record source in the subform reads:

SELECT [School-Unit].[Unit_number], [School-Unit].[School_Name] FROM
[School-Unit];

Basiclly the sub form creastes records in a table called School-Unit
containing two Key fields Unit_number and School_name. These form a pair of
Keys to link the Schools table with the Org_unit table in 1 to many fashon.
Once a record in this table is created the sub form will not allow deletion
(delete is Greyed out) of the link between the two tables. So the child
records can only be delted by manually editing the table. By the was
referential Integrity is turnd on for both sides of the link so if a school
or unit is delted the links will be deleted.

A picture is worth a 1000 words, oh how i wish i could paste the Schema in
her so you could see it.

I would expect tables such as:

Schools
SchoolID (well, SchoolName if you unwisely use a name as a primary key)
School_Name
<information about the school as an entity>

Units
Unit_Number
Unit_Name
<other information about the unit>

SchoolUnits <many to many resolver table>
SchoolID <foreign key linked to Schools.SchoolID>
Unit_Number <foreign key linked to Units.Unit_Number>

Is this in fact your structure? Check the "Allow Deletes" property of the
subform: perhaps the problem is that the FORM isn't permitting deletion.