fk id not apearing

[John thomas]

Hi i am a Newbee
I have a related data base .and thought i was doing ok
Now i realise that that the primary key auto no from one table does not appear in the ralated table in the FK field asigned to it .
I think this must be wrong .Please clarify
If it is wrong what am i doing wrong
EG
customers table primary key auto
products table i have Primary auto no for that table and a FK long inteeger from the customer table . but the no from the customer table does not automatically go into the fk on the products table

 

[Alansidman]

How are you populating the tables. If in a form, you will need to set up a parent child relationship. Once this is established then it will self populate.

This should help you get started
http://www.datapigtechnologies.com/flashfiles/subforms1.html

 

[John thomas]

Hi Alan
Thanks for your time ,I thought just by setting the PK and FK and doing a relationship this would self populate clearly i was wrong .
So to clarify if i have a customer and orders table I need to sep a subform for those tables and if i have a orders detailds table do i need to set up a subform from orders to order details and so forth to make everything self populate l
I am using forms to enter dtails

 

[jdraw]

You might get a lot of information by watching these videos which are related to designing a database for Customers, Orders, OrderDetails....

You may get some ideas from these data models
http://www.databaseanswers.org/data_...ders/index.htm
http://www.databaseanswers.org/data_...ucts/index.htm

and if you want to see the videos to show how to determine such a model, watch these free video tutorials

These free video tutorials describe the processes of data modelling, normalization and entity relationship diagramming. There are other videos, but this group is by the same presenter and covers an example situation. The example relates Customers, Orders and Items.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

The first few topics at this site are well worth the read.

http://www.rogersaccesslibrary.com/forum/topic238.html

Good luck with your project.

 

[John thomas]

Hi alan realy apreciate your help
It seems I go 2 steps forward i step backwards But with perseverience and help from people like your self i will get there
Many thanks john

 

[Pat Hartman]

At it's most simplistic, if the master/child link for the subform is defined, then when you add a record to the subform, Access will automatically populate the foreign key.

 

[John thomas]

Pk Will not auto populate FK

Hi still having problems with this one .
To simplify things ,I have a Customers table and a orders table .there are no sub forms .I am entering data via forms .The tables are related to each other one to many.
The PK from the customers table has a fk in the orders table .I expected to se the customers ID in this field ,but it is not .
Can you please state if this is correct or not .I need to know!
If it should be there what am i doing wrong .If it shouldnt then i need to make that happen
Many thanks

 

[Pat Hartman]

Re: Pk Will not auto populate FK

Please don't start a new thread. People need to know what you have tried already so they don't waste time on those solutions.
http://www.access-programmers.co.uk/forums/showthread.php?t=240528

 

[John thomas]

Hi Pat
Understand
do i need to ask the question agaain or wil i get replies as it is

 

[Pat Hartman]

There is no magical way for Access to "know" what record you want to relate to any other record. My post #6 explains the standard way to link a "child" to its "parent". If your forms are independent, then YOU need to do the linking yourself. You must have a way to identify the PK of the record you want to link to so you can place this value in the FK of the current form.

One way to do this (if the popup form is only opened from ONE other form) is to reference the original form directly. This REQUIRES that the original form still be open. So - in the BeforeInsert event you would add something like:

Me.MyFKfield = Forms!frmOriginal!MyPKfield

If you want to close the original form, then you can pass in the value of MyPKfield by using the OpenArgs argument of the OpenForm Method. Then the code in the popup form's BeforeInsert event would be:

Me.MyFKfield = Me.OpenArgs

I use the form's BeforeInsert event for this specifically so that my code doesn't dirty the popup form. The BeforeInsert event runs only ONCE right after the user types the first character in a "new" record. So, you know for sure if this event runs that the user has tried to enter data and the record is "new".

 

[John thomas]

H pat
Before i start doing what you suggest .can you answer this question
If I open the customers table and click on he +far left ide of record I would expect to see the related child records but instead it shows every customer record

 

[Pat Hartman]

You have not correctly defined the relationship and/or not populated the FK field correctly.

 

[Alansidman]

John;
Since it appears that you are not making much headway here, perhaps it is time for you to upload your db for analyzation and specific instruction on how to resolve your issue. Sanitize it for confidential materials. Run a compact and repair before uploading. Also, it is not necessary to see all the data , but only a representative sample.

 

[John thomas]

Hi sorted my problem .
It may be of interest to other newbees
In my orders form I use a Combo box to select the customer from customer details record.
I Was Storing the surname in the surname field of my form .I did this becouse i wanted to see the surnames in my orders form .
I now realise that what I should have done was store the customers Id inthe FK of my orders form .Now everything works well .amd to get the Customers Surname into the orders form ,I have added the surname field from my customerrecords Table into my orders form and it automatically updates that .
Thanks to everybody who offered help

 

[jdraw]

Glad you have it working, and thanks for telling us the solution.