Northwind - how they do that?

[Alex]

Hi everyone,
Here is my question.
The sample database - Northwind has a form "Orders".
This form has a subform - "Order Details" - where one can
choose a product for the Order, enter the price and the
quantity.

The question:
How the hell they (Microsoft) did that so when I enter a
new product with quantity and price in the subform, this
record automatically has OrderID field filled out with
appropriate Order ID from the Main form "Orders"? (I
don't even have to enter it myself..)

In my case (Invoices and Invoices Details) the moment I
am trying to enter a new record in Invoices Details
subform it says something - the appropriate record must
be in Invoices table?!?- can't add it up.
(Invoices has one key - InvoiceID, Invoices Details has
two keys - InvoiceID, ItemID). Connected one to many
through InvoiceID field.

Thank you for your explanations and time.

Alex

 

[Allen Browne]

Open your main form in design view.
Right-click the edge of the subform control and choose Properties.
On the Data tab of the Properties box, check the properties:
LinkMasterFields
LinkChildFields

The LinkMasterFields property should be set to the primary key of the main
form's table, and the LinkChildFields property should be set to the foreign
key of the subform's table's.

If these properties are correct, the LinkChildFields should inherit the
values from the LinkMasterFields.

 

[Guest]

Allen,

They both (LinkMaster and LinkChild) have the same value -
in my case - InvoiceID.
Still it does not work.
And when I am trying to enter a new record in subform
(Invoice Details), putting the Quantity amount, it says
first:"
"You can't assign a value to this object".
Reasons:read only form
design view
too large for the field.
(nothing of the kind for my form).
Then suddenly it let it to be place in the field, but as
soon as I move to the next record in the subform, it says:

"You can not add or change a record because a related
record is required in table 'Invoices'"?!?!
Why? The relationship is set to One to Many between the
tables 'Invoices' and 'Invoices Details'. !??

What wrong am I doing, so it does not work as should?
Two simple tables ..well..I have described them above.
AccessXP.

ANY IDEAS?!?!
Thanks.

-----Original Message-----
Open your main form in design view.
Right-click the edge of the subform control and choose Properties.

 

[Alex]

Allen

I got it. Sorry. Just missed the data type on the Foreign
Key in the sub form.
It should be just Number..I automatically put Autonumber.

Thanks for you time and knowledge sharing.

Respectfully,

alex

-----Original Message-----
Allen,

They both (LinkMaster and LinkChild) have the same value -
in my case - InvoiceID.
Still it does not work.
And when I am trying to enter a new record in subform
(Invoice Details), putting the Quantity amount, it says
first:"
"You can't assign a value to this object".
Reasons:read only form
design view
too large for the field.
(nothing of the kind for my form).
Then suddenly it let it to be place in the field, but as
soon as I move to the next record in the subform, it says:

"You can not add or change a record because a related
record is required in table 'Invoices'"?!?!
Why? The relationship is set to One to Many between the
tables 'Invoices' and 'Invoices Details'. !??

What wrong am I doing, so it does not work as should?
Two simple tables ..well..I have described them above.
AccessXP.

ANY IDEAS?!?!
Thanks.

 

[Albert D. Kallal]

Allen,

They both (LinkMaster and LinkChild) have the same value -
in my case - InvoiceID.
Still it does not work.

What wrong am I doing, so it does not work as should?
Two simple tables ..well..I have described them above.
AccessXP.

Ok, we have the parent table, or the so called "main" table. This has a
autonumber PRIMARY KEY field called InvoiceID.

In this case, the field in the main Invoice table will be a primary key, and
be a autonumber field.

In the table you are trying to relate, that field called InvoiceID is going
to be a PLAIN JANE regular field. It needs to be a long number field (the
same type of field you are using to join it to). However, the field MUST not
be a autonumber (you can't modify autonumber fields..they are read only).
Further, there CAN NOT be a index on the field that is set to unique. So,
think about this for a second...as you need to realize that the field in the
child table is just a regular field..and has no special settings. However,
that primary key field in the parent table is going to be a autonumber
field.

Further, you should in the relationships window create the relation (but,
you DO NOT have to do this..as the link master/link child fields is the way
the value gets set (the relationship window does NOT automatically set the
child values for you due to this fact. The relationship window does enforce
things..and for example will NOT allow you to create child record without an
existing parent record..but YOU have to set the values. Of course..using the
child/link master settings in a form is one way to do this. So, this value
is set for you when you use a sub-form..but in other cases..you will have
to set the value yourself).

It is also VERY important which way you do create the relationship. You
always want select the primary key id in the parent table..and then drag the
mouse to the child field in the child table (in your case invoice details).
Also, since it is possible to have a invoice..but NOT YET enter
details..then the relation join should be what we call a left join. (so,
don't use the option where both tables have a equal value..but choose the
join option that says:

All records from the "main table" and only those from child tables...