PK not automatically filling FK (1 Viewer)

[George10988]

Hello, I have a very complex relationship of about 10 tables with many, many to many relationships. For some reason I am having a hard time getting the PK in one table to fill in the FK in another table. For example, IF my PK in the first table is an autonumber and i fill in a persons name I want that PK to transfer to the FK in the second table automatically as soon as I start to fill in the fields that are related to that person that reside on the second table. I have done this before and it worked but only when I did not have many to many relationships. IS there a way to use multiple subdatasheets to associate one table to many child fields?

 

[John Big Booty]

You will need to be using Combo boxes that use your prime table as their Row Source and are storing the PK/FK in the sub table. Short of using some code I'm aware of no other way to achieve this automagically.

 

[Vassago]

I'd also strongly urge against using an autonumber. They are tricky to deal with. Is there anything else you can create to use as a primary key?

 

[George10988]

Hello guys, thank you for the responses. I am so lost on this project it is my first plunge into access! John big booty, do you know of any tutorial online. I've looked into this using google and have found links to here and external sources basically saying the same thing you have told me; however, I have not been able to find an example of one. I am more of a visual learner. Do you think if I post a screenie of my relationships windows you can somehow give me an example of how I could set up a form for dataentry? The client wants a ONE form data entry page.

Vassago: I supposed I can use something besides an auto number. I have been under the assumption from many tutorials and example online that it is best to use autonumbers to associate between tables. I am not so sure now that you have brought it up.

 

[gemma-the-husky]

if you have a form/subform - with the pk/fk defined as the link - then access will know to insert the pk, as the fk when you add a record in the subform.

if you are doing it some other way, you need to set the fk manually in code, in some appropriate event

 

[jdraw]

Some great Access reference sites (free)

http://www.rogersaccesslibrary.com/forum/topic238.html
http://www.functionx.com/access2003/index.htm
http://www.accessmvp.com/strive4peace/
http://www.fontstuff.com/access/index.htm
http://allenbrowne.com/tips.html
http://www.databaseanswers.org/
http://www.sfubusiness.ca/areas/mis/tutorials/2np/lessons/map.pdf

 

[George10988]

Sorry for my late response guys I have had some issues lately that have kept me from getting online. Thank you for all the help and all of those links!

 

[boblarson]

I'd also strongly urge against using an autonumber. They are tricky to deal with.

Amd I would argue the opposite. As long as you do not care what the number is for the PK, then using an autonumber as a surrogate key has worked well for me for many, many years and I tend to use a surrogate key instead of a natural key because so many times over the years I have seen where a natural key somehow becomes not a natural key either by a business rule change or just simply not anticipating a certain scenario.