Foreign key not populating

[sparkyrose]

Hi all,

I suspect I'm missing a database 101 issue here, so apologies if so.

I have two tables in my Db: tblMaster & tblAddresses. They are joined with a one-to-many relationship, with the addresses being the "one" side. Many entries in tblMaster have the same address.

I use a form, based on a query, to add records which creates the new vendor in tblMaster, and (if I have contact details), a record in tblAddresses. The problem is that while the PK is created in tblAddresses once I add the address info, the related FK field in tblMaster is not populated with that #.

Is this because I'm trying to do a kind of "reverse cascade", and if so any suggestions to resolve it? I'm sure this is a v simple question I'm missing.

Thanks in advance!

 

[jdraw]

Can you tell us in plain English WHAT is in tblMaster?

 

[sparkyrose]

Sure. The Db tracks certification requirements where vendors have to provide a compliance certificate to us annually. tblMaster has the basic information about each vendor: name, contract date, section ref., cert requirement, audit rights, etc.

Many primary vendors have multiple contracts so would have more than one record in tblMaster, but the contact would be the same for them all.

Hope that helps.

 

[jdraw]

Sounds like you may be missing some tables. You may want to research Normalization.
There's a tutorial here that will help ensure you have your tables set up to match your requirments
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

 

[sparkyrose]

Thanks. I'll read over it and hopefully it'll explain what I need.

 

[Pat Hartman]

When you use a main form/sub form and set the master/child links correctly, Access will automatically populate the foreign key in the many-side records. When you use other techniques to populate the many-side table, you may have to populate the FK yourself.

From what you are describing, I would assume that tblMaster has a FK to tblAddress. To populate that, I would use a combo to select an address from tblAddress. If you want to be able to add addresses on the fly, use the NotInList event to open up the Address form. You should find instructions on what needs to be done. I don't ever use this method so I don't want to give you bad advice.