fk id not apearing

J

John thomas

Registered User.
Local time
Today, 11:11
Joined
Sep 4, 2012
Messages
206
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
 
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
 
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.
 
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
 
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.
 
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
 
Hi Pat
Understand
do i need to ask the question agaain or wil i get replies as it is
 
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:
Code: 
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:
Code: 
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".
 
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
 
You have not correctly defined the relationship and/or not populated the FK field correctly.
 
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.
 
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
 
Glad you have it working, and thanks for telling us the solution.
 
You must log in or register to reply here.

Similar threads

S
Relationships for standard & special rates
Replies
14
Views
910
RogerCooper
R
J
Form Not Showing All Records From Table
Replies
4
Views
735
Gasman
Gasman
W
Solved From with One-To-Many Relationship table
Replies
19
Views
1,485
wmix
W
W
Service Ticket Table(s)
2 3
Replies
51
Views
2,210
wmix
W
riktek
Mandatory FK values? "You cannot add or change a record because a related record is required in table"
2
Replies
22
Views
2,827
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom