Primary key ContactID not migrating to subforms

Darren,

As mentioned by Tina, it appears that including a form bound to
Materials table into this scenario is not really appropriate. And what
you have omitted from your schema is an Estimates table. Now that you
have explained further, if I am not mistaken, this is the real-life
situation as regards the data you are trying to manage?...

Contacts
1
|
M
Estimates
1
|
M
Estimate Details
M
|
1
Materials

But, onced again pointed out already by Tina, the Materials table serves
the purpose of a lookup table, and does not need to be represented in a
form or subform for your purposes.

Of course, this may be incorrect if your reality is that each Contact
only ever has one Estimate. If this is the case, your situation is
simpler. The tables are like this...

Contacts
1
|
M
Estimate Details
M
|
1
Materials

.... which is how you've been working at the moment. But it's still not
appropriate to have a Materials subform or subsubform. You will simply
use the Materials table as the row source of a combobox on the Estimate
Details subform for the data entry of the MaterialsID for each Detail
record.

I have really only repeated pretty much what Tina said, but maybe it
helps to see it explained in two slightly different ways.

--
Steve Schapel, Microsoft Access MVP


Darren Kozey via AccessMonster.com wrote:
Hi folks.

OK, I've tried the same trick with my tiny "first principles" database, but
it seems there is a different problem. I am getting the "Index or Primary
Key can not contain a Null value" message when I go to enter data in the
subforms. Apparently, this is a common problem that many people experience,
but I haven't seen a good explanation as to what causes the problem.

It is a very simple db as follows:

Contacts--bound to Contacts--ContactID as PK
|
|__sfrmEstimate--bound to EstimateDetails (joining table)
--ContactID,MaterialsID as PK's
|
|__ssfrmMaterials--bound to Materials--MaterialsID as PK

A M:M relationship is established between Contacts and Materials using the
EstimateDetails table.

I don't understand why this doesn't work?

Regards,
Darren

thanks, Steve! bows, blushing it certainly helps when the person reading
the post already understands what i'm saying... bg


"Steve Schapel" wrote in message
...
Superb, Tina. :-)

--
Steve Schapel, Microsoft Access MVP


tina wrote:
"a customer can have many materials in their
estimate, and each material can likely be used with many customers"


okay, i see your problem. you have to define the relationship between
*two*
tables at a time, not three.
1) one customer can have many estimates (i presume?), and each estimate
belongs to only one customer. this is a standard 1:n relationship.
2) one estimate can have many materials, and each material may be
included
in many estimates. this is a standard n:n relationship, and you'll need
a
linking table to express it as two 1:n relationships.
note: **there is no direct relationship at all between customers and
materials.**

so your tables would be

tblContacts (customers, i assume?)
ContactID (primary key)
ContactName
[other fields that describe a specific contact]

tblEstimates
EstimateID (pk)
ContactID (foreign key from tblContacts)
[other fields that describe a specific estimate]

tblMaterials
MaterialID (pk)
MaterialName
[other fields that describe a specific material]

tblEstimateDetails (linking table)
EstDetailID (pk)
EstimateID (fk from tblEstimates)
MaterialID (fk from tblMaterials)
[any other fields that describe an estimate detail]

to express these relationships in a data entry form, the heart of it
would
be a form bound to tblEstimates with a subform bound to
tblEstimateDetails;
and in the subform, a combo box with its' RowSource based on
tblMaterials.

depending on the workflow in your company, you might enter records in
tblContacts in a separate form at a separate time. in that case, the
form
bound to tblEstimates would include a combo box with RowSource based on
tblContacts, so you can select the contact for each estimate record.

or you might need to enter contacts records at the same time as
estimates;
in that case, use a mainform/subform/sub-subform setup, as
Contacts/Estimates/EstimateDetails

tblMaterials is just a list of materials that may be used in an
estimate. i
call this type of table a "supporting" table. usually you don't have to
add/update records in a supporting table often, and when you do, you do
it
in a separate form that is not connected to your normal daily data
entry. a
supporting table is usually only used as the RowSource in a combo box in
a
data entry form. (in this case, you use tblMaterials as the RowSource
for a
combobox in the EstimateDetails form.)

this is the sort of thing that's a lot easier (for me) to explain in
person,
with drawing examples and pointing and gesturing g so i hope i haven't
confused you too much!

hth


"Darren Kozey via AccessMonster.com" wrote in
message news:1eec41d7c5d64dcc826b9dab7bac1e2f@AccessMonste r.com...

Hi Steve and Tina.

You got it bang on!

Thanks for the tip Steve. I tried it and it works. However, it didn't
work
without a sfrmEstimate qry, so I used "SELECT Contacts.ContactID FROM
Contacts; ". Then I put a ContactID textbox control on my master
Contacts
form, and one on the sfrmEstimate (as you prescribed), both not visible,
and now there is perfect tracking, without having to save first.

I have no idea how this is working...and it sure seems odd that just

having

the tables linked properly wouldn't be sufficient to do the job.

Are there any books or other resources out there that really go into the
inner workings of Access? (I've read several, and I have to say, I'm

really

frustrated at how similar they all are, and how none of them go into the
working principles, they seem more interested in showing you how to use

the

wizards, which is exactly how one learns very little!) I mean, this is
the
type of thing that I run into constantly, and it usually bogs me down
for
weeks. I have to say I'm fairly frustrated, but thanks to good folks
like
yourselves (saviours at times!) people can actually learn a few things.

As a side note, in order to try to understand the workings of Access
better, and to resolve my problem, I created a tiny database as a means
of
going back to "first principles". I set it up trying to follow 3 levels
of
normalization, and considering table relationships as well. Needless to
say, I'm experiencing the same problem, in that the subsubform doesn't
get
any form of linking. This time however, I used a joining table to create
a
many-to-many relationship between the estimate details and the contact.
Is
this sound reasoning?... "a customer can have many materials in their
estimate, and each material can likely be used with many customers". The
joining table was used to bind the "sfrmEstimate" form, therefore no qry
was used. I'll try adding the controls as I did in my real database and

see

if it also fixes the problem.

Many regards,
Darren

--
Message posted via http://www.accessmonster.com

Thank you Tina.

Everything you said makes sense. You're right about my tables, I was
missing a linking table between the Estimate and the Materials tables. One
question about linking tables. I've seen examples of them where the PK
consists only of the two FK fields, set as a PK pair. Then there is the
other method where they are only FK's in a table with its own PK. What is
the difference, and when does one use method A vs. B?

However, I'm not certain I want to treat the Materials as a lookup
function, because the materials in my case are meant to be entered by the
user, as anything they want (i.e. there are separate fields for
description, qty, and unit price). This subform is in datasheet view for
this purpose.

I am structuring the forms as in your second description (i.e.
mainform/subform/sub-subform setup), whereby the contact information is
entered first, then the user goes to the Estimate tab to enter this data.
The Estimate tab also contains a tabbed form with page 1 containing a
single control (again this is just my test DB), and page 2 has the
Materials subform in datasheet view for entry of each material item.

So, aside from the Materials datasheet subform, I'm going to alter my db
accordingly and see how it goes. I'll post again with my results, and/or
questions.

Thanks very much!

Darren

--
Message posted via http://www.accessmonster.com

Hi Steve,

Thank you. See my response to Tina below.

In addition, Yes you have drawn (in scenario 1) the structure I want
exactly! I do want to leave the option open that a customer may have more
than one estimate...after all, we want repeat customers right? Also, this
allows for the comparison of two slightly different estimates for the one
customer.

I'm going to give this design a shot using my tiny test database, and see
what happens.

Thanks again.

darren

PS. Access db design can be frustrating as heck, but the rewards seem
greater also when things finally work!

--
Message posted via http://www.accessmonster.com

Tina, Steve,

I've spent some time re-designing my tables, relationships to include the
M:M, and the Forms, according to our last posts, and now I am beginning to
see the problem.

What has happened, is the Materials subform, (ssfrmMaterials) is left
orphaned by this structure, and hence the PK does not ripple down to it.

To re-cap what I've done, see below:

tblContacts - PK is ContactID, bound with Contacts form.
|
|__tblEstimate - PK is EstimateID, bound with sfrmEstimate form, Link
| Child/Master fields is ContactID
|
|__tblEstimateDetails - PK is EstimateDetailsID, bound with no
| form, EstimateID & MaterialsID as FK's
|
|__tblMaterials - PK is MaterialsID, bound with
ssfrmMaterials, Link Child/
Master is not possible

So with this configuration, the ssfrmMaterials, and hence the tblMaterials
is orphaned. No data can be entered, and none returned. Putting the extra
table tblEstimateDetails creates a dead-end for the sfrmEstimate----
ssfrmMaterials link. The only way I see to "cheat" the system, is to
perform the trick Steve prescribed above where two controls (one on each
form) provide the linking ability.

Have I missed something? Is there any way things could be re-arranged in
order to make it work?

Thanks for the help.

Darren

--
Message posted via http://www.accessmonster.com

well, to address your second remarks first: it sounds like the table
structure as it relates to materials needs to be further explored.
presumably you do need a master list of materials - after all, if you're
providing estimates for construction contracts (for instance), you're not
going to be entering materials that you would need to sew custom evening
clothes, or film a movie; you're going to have a finite list of materials
that are used in construction projects.

your tblMaterials should only describe materials as entities, NOT how
specific materials are used in an estimate. so tblMaterials would include
MaterialID, MaterialName, MaterialDescription. probably UnitPrice as well.

if each EstimateDetail record describes the use of one material in a
specific estimate, then the Quantity field would go in that table, as well
as a MaterialID foreign key field. MaterialDescription would NOT be a field
in the tblEstimateDetails - that data is already stored in tblMaterials.

if EACH EstimateDetail record may describe the use of more than one
material, then you would need an additional child table, as

tblEstimateDetailMaterials
EstimateDetailID (fk from tblEstimateDetails)
MaterialID (fk from tblMaterials)
Quantity

the table would have an n:1 relationship with tblEstimateDetails, and an n:1
relationship with tblMaterials.

in either scenario, you need a tblMaterials as a supporting table to
populate a combo box droplist. and that table would not be part of your main
data entry setup. if your user needs to be able to add a new material to
tblMaterials "on the fly", usually you do this by setting up a data entry
form that can be opened from the combo box's NotInList event, as needed.

i'll address your "combo primary key" question in a separate post.

hth


"Darren Kozey via AccessMonster.com" wrote in
message news:da710e1617a44902a158ef0c36069798@AccessMonste r.com...
Thank you Tina.

Everything you said makes sense. You're right about my tables, I was
missing a linking table between the Estimate and the Materials tables. One
question about linking tables. I've seen examples of them where the PK
consists only of the two FK fields, set as a PK pair. Then there is the
other method where they are only FK's in a table with its own PK. What is
the difference, and when does one use method A vs. B?

However, I'm not certain I want to treat the Materials as a lookup
function, because the materials in my case are meant to be entered by the
user, as anything they want (i.e. there are separate fields for
description, qty, and unit price). This subform is in datasheet view for
this purpose.

I am structuring the forms as in your second description (i.e.
mainform/subform/sub-subform setup), whereby the contact information is
entered first, then the user goes to the Estimate tab to enter this data.
The Estimate tab also contains a tabbed form with page 1 containing a
single control (again this is just my test DB), and page 2 has the
Materials subform in datasheet view for entry of each material item.

So, aside from the Materials datasheet subform, I'm going to alter my db
accordingly and see how it goes. I'll post again with my results, and/or
questions.

Thanks very much!

Darren

--
Message posted via http://www.accessmonster.com

One
question about linking tables. I've seen examples of them where the PK
consists only of the two FK fields, set as a PK pair. Then there is the
other method where they are only FK's in a table with its own PK. What is
the difference, and when does one use method A vs. B?

first of all, note that a combination primary key isn't always made up of
foreign key fields only. it may be one or more foreign keys and one or more
"native" fields, or it may consist of native fields only.

next, this question always opens up a can of worms. the answer depends a lot
on who you're asking. some developers are rigid adherents to the "natural
key" solution; in child tables, especially linking tables, that's often a
multiple-field primary key - the "combination primary key". other folks
always use a single-field Autonumber primary key in every table. each group
may tell you that their way is best, and have reasonable arguments for using
one method over the other.

personally,
1) i generally use Autonumber single-field primary keys in any table that
will contain production-type data entry. mainly for convenience; i don't
want to worry about generating a primary key, and i don't want to worry
about cascade updates causing problems in a multi-user database. both of
those reasons are tied to my general level of skill in Access - if i were a
more expert developer, they might be non-issues. however, you work at the
skill level you're at, so that's a valid consideration in some design
decisions.
2) i often use code-generated single-field primary keys in supporting tables
where the number of records is pretty limited, and data is entered
infrequently, and usually by only a few authorized users. i base this
decision on field size: if i have a supporting table listing the various
titles of address such as Mr., Mrs, etc, i don't want to use a Long Integer
field size when all i need is a Byte-size field. no point in wasting space,
especially when Access has to pull all table data across a LAN in order to
run queries, update tables, etc. i try to cut down on the volume of LAN
traffic if i can, even in small ways.
3) i rarely use combo primary keys, and then only in tables that will never
be the parent table in a parent-child relationship - in other words, never
the 1 side of a 1:n relationship. i simply find combo foreign keys a pain in
the butt, so i just don't use them.

if anyone else is still watching this thread, you'll probably get other
responses that will provide different viewpoints and considerations, that
will help you make a good decision for your situation. if not, you can
always post a new thread and invite comment.

hth


"Darren Kozey via AccessMonster.com" wrote in
message news:da710e1617a44902a158ef0c36069798@AccessMonste r.com...
Thank you Tina.

Everything you said makes sense. You're right about my tables, I was
missing a linking table between the Estimate and the Materials tables. One
question about linking tables. I've seen examples of them where the PK
consists only of the two FK fields, set as a PK pair. Then there is the
other method where they are only FK's in a table with its own PK. What is
the difference, and when does one use method A vs. B?

However, I'm not certain I want to treat the Materials as a lookup
function, because the materials in my case are meant to be entered by the
user, as anything they want (i.e. there are separate fields for
description, qty, and unit price). This subform is in datasheet view for
this purpose.

I am structuring the forms as in your second description (i.e.
mainform/subform/sub-subform setup), whereby the contact information is
entered first, then the user goes to the Estimate tab to enter this data.
The Estimate tab also contains a tabbed form with page 1 containing a
single control (again this is just my test DB), and page 2 has the
Materials subform in datasheet view for entry of each material item.

So, aside from the Materials datasheet subform, I'm going to alter my db
accordingly and see how it goes. I'll post again with my results, and/or
questions.

Thanks very much!

Darren

--
Message posted via http://www.accessmonster.com

Hi Tina...Thanks for the reply....gee you're up late too

I understand what you're saying (for the most part). Let me say again
though, that this tiny test database is only that. There are fields that
are left out, like qty etc. for the Materials table. I left many fields out
on purpose, so that I may get the structure working first. Later it is only
a matter of adding more fields.

In regards to the combobox for selecting from a list of materials, you have
me thinking to change it from just a text field. You're right, in that I
should have a standard list of items to choose from, and the user can add
items when necessary. As far as implementing that, no problem, I've already
got that working in my REAL database for selecting from a list of tasks for
estimating the number of hours for that task. Datasheet view is used here
also for adding many tasks and its associated data.

Anyway, not to worry about the details of the tables and forms...I'm just
trying to get the structure working first (i.e. writing the record all the
way down to the last subform and its table). That's why I've only used a
few fields in my test DB. Whether it is a combo box or text control, it
will have to be placed on the ssfrmMaterials subform in order for the flow
to work how I want. Incidently, I have this working very nicely with the
1:n version of this database (i.e. no linking table), and no tricks were
necessary for it to work properly. Without a linking table, it is very easy
to cascade mutual linking fields (all the way down to the end) to relate
the tables and link the forms together.

I suspect that the culprit causing all my problems is the fact that you can
not nest tab controls. As a result, I've had to create a subform
sfrmEstimate with a tab control on it, then drop sfrmEstimate onto the main
form's Estimate tab. This of course orphans all the subforms (and their
tables) on the sfrmEstimate, and hence the same problem.

Thanks,
Darren

--
Message posted via http://www.accessmonster.com

Hi Tina and Steve.

It turns out my simple test database was not working quite as flaw-free as
I thought. It just so happened that I was doing the data entry in the order
of main/sform/ssform, and hence the required PK was being generated as I
entered data.

Is this normal behaviour, and something the db developer needs to be aware
of when designing the database? So this is what is meant by knowing the
workflow of the business?

This concerns me a little, as I would prefer the database be relatively
idiot-proof, in that the data could be entered in any order, even though
there is a natural flow for it. There are cases also where some data may be
left blank, and if this is the only control that can trigger the generation
of a new PK in that table, then none will be created for that record, and
any tables that are downstream will also be affected, i.e. no data can be
entered.

Just learning as I go, and documenting it for others' benefit. Any input or
comments are welcome and appreciated.

Darren

--
Message posted via http://www.accessmonster.com

well, the bottom line is that in a pk/fk relationship, you need to have a
record in the parent (pk) table before you can enter a related record in the
child (fk) table. if you've structured your tables/relationships correctly,
that will be the natural flow probably 99 percent of the time. after all,
why would you want to enter OrderDetails records (for instance) before you
enter an Order record; why enter an Order record before you enter a record
about the Customer who is placing the order, etc, etc, etc.

a big part of the developer's job is to create a user interface that
protects the accuracy and integrity of the data at all times, while at the
same time making the user's job faster and easier, not slower and harder.
that is always a creative challenge, and often requires thinking outside of
the box in designing the user interface, as well as educating your users on
the WIFM (what's in it for "me") of data integrity,and managing their
expectations toward a satisfactory outcome for everyone.

hth


"Darren Kozey via AccessMonster.com" wrote in
message news:e3962f0c08b54d48a95b5a6d1e49a593@AccessMonste r.com...
Hi Tina and Steve.

It turns out my simple test database was not working quite as flaw-free as
I thought. It just so happened that I was doing the data entry in the
order
of main/sform/ssform, and hence the required PK was being generated as I
entered data.

Is this normal behaviour, and something the db developer needs to be aware
of when designing the database? So this is what is meant by knowing the
workflow of the business?

This concerns me a little, as I would prefer the database be relatively
idiot-proof, in that the data could be entered in any order, even though
there is a natural flow for it. There are cases also where some data may
be
left blank, and if this is the only control that can trigger the
generation
of a new PK in that table, then none will be created for that record, and
any tables that are downstream will also be affected, i.e. no data can be
entered.

Just learning as I go, and documenting it for others' benefit. Any input
or
comments are welcome and appreciated.

Darren

--
Message posted via http://www.accessmonster.com