Primary key ContactID not migrating to subforms

[Darren Kozey via AccessMonster.com]

Hi Tina...Thanks again.

I believe my tables are structured ok, i.e. as per the following:

Contacts - PK=ContactID, Field=Name
|
|__Estimate - PK=EstimateID, FK=ContactID, Field=EstimateValue
|
|__Materials - PK=MaterialsID, FK=EstimateID, Field=Materials

I understand that there's a natural flow, but in my case anyway, there are
times when the EstimateValue field would not be entered, in which case, the
EstimateID would not be created, and then the syncronization of the
materials table is lost.

I guess there's no way around this problem, other than two things I can
think of, 1) is force the user to enter "something" in the EstimateValue
field, regardless if they have one or not, and 2) have a date field in the
Estimate table that is filled automatically when the Estimate tab is
selected. Am I incorrect in thinking this would work?

Thanks,
Darren

 

[tina]

well, the solutions you suggested should work, but you might prefer to
*programmatically* force the creation of the parent record, rather than
making the user do it. you can do this without adding an extra field in the
parent table that you wouldn't otherwise need. in the *subform's* module,
try adding

Private Sub Form_BeforeInsert(Cancel As Integer)

If Me.Parent.NewRecord Then
Me.Parent!EstimateValue = 0
Me.Parent.Dirty = False
End If

End Sub

as soon as the user begins entering data in the first subform record, the
subform event fires. if the parent record is a New Record, which it will be
*only if* the user did not already enter an EstimateValue manually, then the
code sets an EstimateValue of zero, just to dirty the record. then setting
the Dirty property to False saves the record, creating an EstimateID primary
key (if the key field is not Autonumber, you can create the primary key
value before setting Dirty to False - rather than setting the EstimateValue
field's value). the primary key value then automatically populates the
foreign key field in the subform, so it's already there when the subform
record saves to the table, so there should be no error on the save.

if you really want the EstimateValue to be Null until the user enters a
"real" value, just reset the value, as

Private Sub Form_BeforeInsert(Cancel As Integer)

If Me.Parent.NewRecord Then
Me.Parent!EstimateValue = 0
Me.Parent.Dirty = False
Me.Parent!FieldB = Null
Me.Parent.Dirty = False
End If

End Sub

hth

 

[Darren Kozey via AccessMonster.com]

Wow, Tina

That's great!

I'm not at the level of Access db design that I would have come up with
that myself. I really appreciate your input.

Thanks!

Darren

 

[tina]

you're very welcome, glad to help! the code itself is minor; the trick is in
understanding how subforms relate to main forms and the sequence of events
firing in both. subforms are an odd duck, hard to handle at first; but the
more you use them, the easier it becomes to manipulate them.

btw, i noticed i made an error in the "alternate" code i posted, used the
wrong field name in one line. hope i didn't confuse you (i confused myself
when i first re-read it <g>)! here it is again, fixed, just in case you
need it:

Private Sub Form_BeforeInsert(Cancel As Integer)

If Me.Parent.NewRecord Then
Me.Parent!EstimateValue = 0
Me.Parent.Dirty = False
Me.Parent!EstimateValue = Null
Me.Parent.Dirty = False
End If

End Sub

hth

 

[Darren Kozey via AccessMonster.com]

Hi Tina.

I feel a little silly asking this question, but I now have two working
versions of my test database, one with only 1:n relationships and one with
a n:n relationship as you noted above where I have an EstimateDetails table
acting as the junction table between Estimates and Materials.

My question is how do I test to see that my n:n version is working...that
is how can I test that one estimate can have many materials, and each
material can be on many estimates?

Thanks,
Darren

 

[tina]

no question is silly, Darren - if you don't know something, the silly thing
is to *not* ask. sometimes the person you ask is silly <g> and doesn't get
the point right away, though. the obvious answer to your question is to
enter some dummy data into your tables to see if the linking table accepts
them. i would throw together a form (Estimates)/subform
(EstimateDetails)/combo box (*Row* Source based on Materials) setup to test
this. the only other thing is to double check your table relationships in
the Relationships window - make sure each parent-to-child link is identified
by the system as one-to-many, not one-to-one or indeterminate.

if i'm not getting the point of what you're asking, give it to me in words
of one syllable, and i'll try again.

hth

 

[Darren Kozey via AccessMonster.com]

Hi Tina.

I think I already have essentially what you described, as far as forms.

I have a main form with 2 tabs, ContactInfo, and Estimate. The contactInfo
tab only has the Name control, and the Estimate tab contains the
sfrmEstimate, which is also a tab form with 2 tabs, Value which has the
EstimateValue control, and Materials which has the ssfrmMaterials subform
in datasheet view.

I have navigation buttons on both the main form, and the sfrmEstimate, so
that I can add more than one estimate for each contact.

The only thing is my Materials ssfrm uses a textbox, and is in datasheet
view. Is this going to make a difference? If so, why?

My thinking of how to test that the n:n relationship was working, was to
enter "screws" for example in the materials ssfrm for the first estimate,
then enter "screws" again for the second estimate (both under the same
contactID). Is this the right logic? It must be flawed, because both the
simpler 3-table 1:n-only database, and the 4-table n:n version will allow
me to do this.

Where have I gone wrong?

Thanks again for your help. (I think I'll be off and running once I
understand what's going on behind the scenes in regards to the n:n
relationship).

Darren

 

[tina]

I have an EstimateDetails table acting as the junction

table between Estimates and Materials

if EstimateDetails is the junction or linking table between the other two,
that means that Estimates is a parent table to the EstimateDetails child
table, *and* Materials is also a parent table to the EstimateDetails child
table.

a linked subform's underlying table is always the child table to the parent
table bound to the main form. you can't link a subform based on Materials to
a main form bound to EstimateDetails - that's putting the parent/child
relationship backward.

as i said several posts earlier, the standard way to express a
parent-linking-parent relationship between three tables is with one parent
bound to the main form, the linking or child table bound to the subform, and
the other parent table used as the RowSource in a combo box in the subform,
with the combo box's ControlSource set to the matching foreign key field in
the linking table.

if you're still trying to use your Materials table to hold data related to
specific estimates records and/or estimate details records, rather than as a
list of materials complete in itself - then you need to review your tables
structure and relationships, and probably alter them.

hth

 

[Darren Kozey via AccessMonster.com]

Hi Tina.

I wish there was a standard way to express graphically and in text, both
the table relationships AND the forms that are bound to them. Looking at a
nice table relationships diagram is great, it allows one to see that
everything can be put back together properly, but it does little to show
how the data will get there, and how it will be synchronized. Not to
mention, it would be tons easier to illustrate in these forums, what
exactly one has done in their db.

I've tried to carefully analyse what you have said.

I understand para 1, and I believe what I have is already correct.

Para 2:

you can't link a subform [ssfrmMaterials] based on Materials to
a main form [sfrmEstimate] bound to EstimateDetails

If you are saying that ssfrmMaterials is the subform, and sfrmEstimate is
the main form, sfrmEstimate is not bound to the EstimateDetails linking
table, I have it bound to the Estimates table.

a linked subform's underlying table [Materials] is always the child >table

[EstimateDetails] to the parent table [Estimates] bound to the >main form
[sfrmEstimate].

Para 3:

as i said several posts earlier, the standard way to express a
parent-linking-parent relationship between three tables is with one
parent [Estimates] bound to the main form [sfrmEstimate], the linking >or

child table [EstimateDetails] bound to the subform [ssfrmMaterials], >and
the other parent table [Materials] used as the RowSource in a combo >box in
the subform [ssfrmMaterials], with the combo box's ControlSource >set to
the matching foreign key field [MaterialsID] in the linking >table
[EstimateDetails].

Are the above in [] correct?

What I have done is bound ssfrmMaterials to both the EstimateDetails and
Materials tables with a query. The query selects all the fields in both
tables i.e. EstimateDetails.EstimateID, EstimateDetails.MaterialsID,
Materials.MaterialsID, Materials.Materials.

Doesn't this accomplish the same thing as having a "Materials" combobox on
ssfrmMaterials (which is bound to EstimateDetails) with its RowSource set
to Materials.* and its ControlSource set to EstimateDetails.MaterialsID ?

The table/form structure "seems" to be working, I just have no real idea
how to test the n:n relationship. To be honest, I really don't understand
what "each estimate can contain many MATERIAL TYPES, and each MATERIAL TYPE
can be on many estimates" means in terms of what's going on inside the
database, and how it affects the user interface. I guess I'm searching for
the literal definition of a n:n realtionship.

Does it mean Estimate A can contain Material Types A and B, AND, Estimate B
can contain Material types A and B also?

Or does it mean something else?

Darren

 

[tina]

you're right, Darren. it's a lot easier to do "show and tell" rather than
just "tell". right now we're going around in circles, i think. if you want
to email me a copy of your database - tables only - i will look at your
tables/relationships and create a "quick and dirty" form setup to illustrate
at least one common way to handle data entry in forms. i have A2003, so i
can handle any db A97 or newer. if you want go this route, do the following:

1. make a copy of your db.
2. in the COPY, delete any proprietary data. replace with a few
representative fake records so i have something to work with.
3. compact the db.
4. zip, if necessary, to under 1 MB. i have WinZip to unzip at this end.
5. email to tt2ac7cess188aty5ahood483otco9m. remove all the numeric
characters EXCEPT the number 1, to get my correct address. refer to the
newsgroups in the subject line, so i don't delete the email as spam.

Hi Tina.

I wish there was a standard way to express graphically and in text, both
the table relationships AND the forms that are bound to them. Looking at a
nice table relationships diagram is great, it allows one to see that
everything can be put back together properly, but it does little to show
how the data will get there, and how it will be synchronized. Not to
mention, it would be tons easier to illustrate in these forums, what
exactly one has done in their db.

I've tried to carefully analyse what you have said.

I understand para 1, and I believe what I have is already correct.

Para 2:

you can't link a subform [ssfrmMaterials] based on Materials to
a main form [sfrmEstimate] bound to EstimateDetails

If you are saying that ssfrmMaterials is the subform, and sfrmEstimate is
the main form, sfrmEstimate is not bound to the EstimateDetails linking
table, I have it bound to the Estimates table.

a linked subform's underlying table [Materials] is always the child

table
[EstimateDetails] to the parent table [Estimates] bound to the >main form
[sfrmEstimate].

Para 3:

as i said several posts earlier, the standard way to express a
parent-linking-parent relationship between three tables is with one
parent [Estimates] bound to the main form [sfrmEstimate], the linking >or

child table [EstimateDetails] bound to the subform [ssfrmMaterials], >and
the other parent table [Materials] used as the RowSource in a combo >box in
the subform [ssfrmMaterials], with the combo box's ControlSource >set to
the matching foreign key field [MaterialsID] in the linking >table
[EstimateDetails].

Are the above in [] correct?

What I have done is bound ssfrmMaterials to both the EstimateDetails and
Materials tables with a query. The query selects all the fields in both
tables i.e. EstimateDetails.EstimateID, EstimateDetails.MaterialsID,
Materials.MaterialsID, Materials.Materials.

Doesn't this accomplish the same thing as having a "Materials" combobox on
ssfrmMaterials (which is bound to EstimateDetails) with its RowSource set
to Materials.* and its ControlSource set to EstimateDetails.MaterialsID ?

The table/form structure "seems" to be working, I just have no real idea
how to test the n:n relationship. To be honest, I really don't understand
what "each estimate can contain many MATERIAL TYPES, and each MATERIAL TYPE
can be on many estimates" means in terms of what's going on inside the
database, and how it affects the user interface. I guess I'm searching for
the literal definition of a n:n realtionship.

Does it mean Estimate A can contain Material Types A and B, AND, Estimate B
can contain Material types A and B also?

Or does it mean something else?

Darren