A 'how did they do that' question about the Northwind DB

  • Thread starter kelly d via AccessMonster.com
  • Start date
K

kelly d via AccessMonster.com

The Northwind DB, access-101 which I'm certain everybody is familiar with,
the orders form, when you enter a new product into the orders subform that
gets recorded into the order details table, how does that subform know to
associate that new entry in the subform with the orderID from the main form
when recording that entry into the table. I couldnt see any relationship or
event procedure that would tell the order details table what orderid to
associate with the entries in the subform. I saw the subforms link-field was
set to orderID but I didnt think that link-field could also auto-populate a
field (such as the orderID field) in a table when a new record is added to a
subform.
Would anybody be willing to offer an explanation as to how the subform knows
what orderID to put into the orderID field of the order details table when a
new record is added to the order subform.

Thanks.
Kelly D
 
K

Ken Snell [MVP]

See the LinkMasterFields and LinkChildFields properties of the subform
control (the control that holds the form object that is being used as the
subform). You'll see the linking fields entered there.
 
K

kelly d via AccessMonster.com

Thanks for your answer.
I did see the link fields. and thats the only orderID connection between the
sub and the main that I found.
Is it that the subform uses that link field to know what to put into a field
on a table that the subform itself doesnt have but it's main or parent form
does have. (I.E. is the linkChild-linkMaster the only thing the orders sub
needs in order to know what to put into the orderID field on the underlying
table when a new product is entered on the sub?)
In other words, I always just assumed that the linkChild-linkMaster was a
table reading relationship. I didnt know it was a table writting
relationship also.

thanks
Kelly D
 
K

Ken Snell [MVP]

The LinkChildFields and LinkMasterFields is used to synchronize the main
form and the subform for "filtering" the subform's data, and it's also how
the subform record gets the value for the foreign key that is the link. To
work correctly in a form or report, be sure that the subform's form's
recordsource has that linking field in it. For a report, it's a good idea to
actually have a control bound to that field on the report (usually as an
invisible textbox); I usually do this for forms too.
 
K

kelly d via AccessMonster.com

thank you very much. that clarified how the child-master link works.
on another subject touched breifly by your answer,
I've been reading up on Primary keys, indexes and relationships. most all the
discussions
i've read on the topic make mention of a foreign key in a relationship but I
have yet to find a good definition as to what exactly a foreign key is.
would you be able to offer a little insight into what a foreign key is and
how one sets a foreign key.

thanks
Kelly D
 
K

Ken Snell [MVP]

A foreign key is a field in a child table that holds the value of the
primary key in the parent table. You then can link the tables via those
fields.

For example, suppose you have a table of customers, each having a unique
CustomerID (the primary key). Then you have a table of orders, where one of
the fields is the CustomerID of the customer who placed the order. The
CustomerID field in the orders table is the foreign key, as it identifies
the customer to whom that record belongs.

You can set the value of the foreign key using a main form / subform
setup -- usually the best way to do it. Otherwise, you must write the value
of the primary key into that foreign key when you create a new record in the
child table -- by query, by code, etc.

It's kind of like branding cattle and then letting all the cattle mix
together in the field. You can always identify your cattle by your brand.
 
K

kelly d via AccessMonster.com

Thanks for the explanation. that makes alot more sense now.
P.S. that cattle thing was fine analogy. :D
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top