Linked combo boxes on subform

[Guest]

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

 

[Guest]

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

 

[Guest]

Thanks Camaro - I see where I was going wrong now

 

['69 Camaro]

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/expert_contributors2.html for contact
info.