Subfor delete not allowed

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


--
Dick Patton
Boy Scout Troop 403
--
Dick Patton
Boy Scout Troop 403

On Sun, 4 Apr 2010 13:38:01 -0700, Dick Patton
wrote:

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

John W. Vinson [MVP]

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.


--
Dick Patton
Boy Scout Troop 403


"John W. Vinson" wrote:

On Sun, 4 Apr 2010 13:38:01 -0700, Dick Patton
wrote:

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

John W. Vinson [MVP]
.

On Wed, 7 Apr 2010 13:15:01 -0700, Dick Patton
wrote:

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

John W. Vinson [MVP]

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


"John W. Vinson" wrote:

On Wed, 7 Apr 2010 13:15:01 -0700, Dick Patton
wrote:

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

John W. Vinson [MVP]
.