Linked combo boxes on subform

My database consists of Stewards who can be members of committees. I have a
committee table which is ID, Type, and Title , plus a StewardsComm table
which has SCID, StewardID, CommID, plus a few "detail" fields eg date of
joining, role etc.

I have a form with the stewards' details as the main form and a continuous
subfrom (based on StewardsComm) to add them onto committees.

Since the committees are divided into subgroups of types I have a first
combo box that lists the committee types and then I want to second combo to
be limited to the titles within that type.

So on the after update of the first combo I have:
Me!Titlecbo = Null
Me!Titlecbo.Requery

However if I try to alter an existing record I get run time error 3331 - to
make changes to this fieldyou must first save the record.

If I try to add a new record then I get error 3162 - you tried to assign a
null value to a variable that is not a Variant data type.

I tried making the second combo unbound - but then of course it doesn't
store the data...

Any thoughts please

HelenJ

Hi, Helen.

Unfortunately, your current table design and form design can't accommodate
your plans. The StewardsComm table isn't normalized to Third Normal Form.
It contains a transitive dependency between ID, Type and Title. (ID
determines the Title via the Type attribute.) This will cause insertion and
deletion anomolies in your table. And combo boxes on continuous forms or
datasheet forms make the combo box selection apply to all records visible on
the form, instead of just for the current record.

My recommendation is to postpone the idea that committee types determine the
title until after you've had a chance to take a formal course in relational
database design. That would mean removing the first combo box (including any
code that refers to this control), and just keeping the bound Titlecbo combo
box on the subform to record the title.

And I would recommend avoiding Reserved words (such as Type) for identifiers
(identifiers are names for table, queries, fields, or other objects,
variables, procedures, et cetera), because these will introduce bugs into
your application.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
See http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


"HelenJ" wrote:

My database consists of Stewards who can be members of committees. I have a
committee table which is ID, Type, and Title , plus a StewardsComm table
which has SCID, StewardID, CommID, plus a few "detail" fields eg date of
joining, role etc.

I have a form with the stewards' details as the main form and a continuous
subfrom (based on StewardsComm) to add them onto committees.

Since the committees are divided into subgroups of types I have a first
combo box that lists the committee types and then I want to second combo to
be limited to the titles within that type.

So on the after update of the first combo I have:
Me!Titlecbo = Null
Me!Titlecbo.Requery

However if I try to alter an existing record I get run time error 3331 - to
make changes to this fieldyou must first save the record.

If I try to add a new record then I get error 3162 - you tried to assign a
null value to a variable that is not a Variant data type.

I tried making the second combo unbound - but then of course it doesn't
store the data...

Any thoughts please

HelenJ

Thanks Camaro - I see where I was going wrong now

"'69 Camaro" wrote:

Hi, Helen.

Unfortunately, your current table design and form design can't accommodate
your plans. The StewardsComm table isn't normalized to Third Normal Form.
It contains a transitive dependency between ID, Type and Title. (ID
determines the Title via the Type attribute.) This will cause insertion and
deletion anomolies in your table. And combo boxes on continuous forms or
datasheet forms make the combo box selection apply to all records visible on
the form, instead of just for the current record.

My recommendation is to postpone the idea that committee types determine the
title until after you've had a chance to take a formal course in relational
database design. That would mean removing the first combo box (including any
code that refers to this control), and just keeping the bound Titlecbo combo
box on the subform to record the title.

And I would recommend avoiding Reserved words (such as Type) for identifiers
(identifiers are names for table, queries, fields, or other objects,
variables, procedures, et cetera), because these will introduce bugs into
your application.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
See http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


"HelenJ" wrote:

My database consists of Stewards who can be members of committees. I have a
committee table which is ID, Type, and Title , plus a StewardsComm table
which has SCID, StewardID, CommID, plus a few "detail" fields eg date of
joining, role etc.

I have a form with the stewards' details as the main form and a continuous
subfrom (based on StewardsComm) to add them onto committees.

Since the committees are divided into subgroups of types I have a first
combo box that lists the committee types and then I want to second combo to
be limited to the titles within that type.

So on the after update of the first combo I have:
Me!Titlecbo = Null
Me!Titlecbo.Requery

However if I try to alter an existing record I get run time error 3331 - to
make changes to this fieldyou must first save the record.

If I try to add a new record then I get error 3162 - you tried to assign a
null value to a variable that is not a Variant data type.

I tried making the second combo unbound - but then of course it doesn't
store the data...

Any thoughts please

HelenJ

You're welcome. Glad to help.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
See http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.


"HelenJ" wrote in message
...
Thanks Camaro - I see where I was going wrong now

"'69 Camaro" wrote:

Hi, Helen.

Unfortunately, your current table design and form design can't
accommodate
your plans. The StewardsComm table isn't normalized to Third Normal
Form.
It contains a transitive dependency between ID, Type and Title. (ID
determines the Title via the Type attribute.) This will cause insertion
and
deletion anomolies in your table. And combo boxes on continuous forms or
datasheet forms make the combo box selection apply to all records visible
on
the form, instead of just for the current record.

My recommendation is to postpone the idea that committee types determine
the
title until after you've had a chance to take a formal course in
relational
database design. That would mean removing the first combo box (including
any
code that refers to this control), and just keeping the bound Titlecbo
combo
box on the subform to record the title.

And I would recommend avoiding Reserved words (such as Type) for
identifiers
(identifiers are names for table, queries, fields, or other objects,
variables, procedures, et cetera), because these will introduce bugs into
your application.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
See http://www.Access.QBuilt.com/html/ex...ributors2.html for
contact
info.

- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember
that
questions answered the quickest are often from those who have a history
of
rewarding the contributors who have taken the time to answer questions
correctly.


"HelenJ" wrote:

My database consists of Stewards who can be members of committees. I
have a
committee table which is ID, Type, and Title , plus a StewardsComm
table
which has SCID, StewardID, CommID, plus a few "detail" fields eg date
of
joining, role etc.

I have a form with the stewards' details as the main form and a
continuous
subfrom (based on StewardsComm) to add them onto committees.

Since the committees are divided into subgroups of types I have a first
combo box that lists the committee types and then I want to second
combo to
be limited to the titles within that type.

So on the after update of the first combo I have:
Me!Titlecbo = Null
Me!Titlecbo.Requery

However if I try to alter an existing record I get run time error
3331 - to
make changes to this fieldyou must first save the record.

If I try to add a new record then I get error 3162 - you tried to
assign a
null value to a variable that is not a Variant data type.

I tried making the second combo unbound - but then of course it doesn't
store the data...

Any thoughts please

HelenJ