Full Version: Capturing the Primary Key from a Parent Form
UtterAccess Forums > Microsoft® Access > Access Forms
kalkns
I have a database in which I am trying to keep track of letters being sent to utility companies for specific areas and client job numbers. I have 4 tables: 1 keeps track of the File Numbers (i.e. client job numbers) and is used as a lookup table; 1 keeps track of the area, who the letter is sent by, has a foreign key for the File Number ID and a primary key (utility letter id) to assign a unique value to each combination of file number, area, and who sent the letter; 1 keeps track of how the letter was sent (i.e. UPS, FedEx, Mail, etc.), the date it was sent, when a response is needed by, any comments and has a foreign key for the Utility Letter ID and a primary key (Letter ID); and the final table keeps track of who we sent the letter to, when we received a response, what type of response we received, and has a foreign key for the Letter ID and a primary key (Information ID). The Information table/form is a subform of the Letter Sent table/form. The Letter Sent form is a linked subform of the Area/Sent By form which is a linked subform of the File Number form.
I have to be able to add file numbers as new ones come up, and when we sent out utility letters we send multiple letters from 1 person for 1 file number and 1 area. Everything works okay except for capturing the primary keys from the parent forms and inputting them into the foreign key fields on the child forms.
I have tried inserting a line into the OnOpen event handler of the opening child form (Me.Foreign_Key=Forms!ParentForm.Primary_Key) with the appropriate field control names inserted in place of "Foreign_Key" and "Primary_Key" and the appropriate form name in place of "ParentForm". I have also tried using the OpenArgs by adding ",,,,,,Primary_Key" after the DoCmd.OpenForm "Child Form" line in the parent form visual basic event procedure code and adding "Me.Foreign_Key=Me.OpenArgs" in the OnOpen event handler of the opening child form to retrieve the value being passed by the parent form. However, it is not working, it neither passes the value to the Foreign Key field on the child form nor to the Foreign Key field in the child table.
What am I doing wrong and how do I correct it?
strive4peace
if the primary key is autonumber,
the foreign key should be long integer
If you set
LinkMasterFields
LinkChildFields
of the subform control to the key field, then when new records are creatd in the subforms, they will automatically be given the value in the parent form with no further effort on your part
kalkns
I do have the primary keys set to autonumber and the foreign keys set to long integer, but for some reason the values are not being passed from the parent form to the child form. That's why I had to use the methods I mentioned in the previous post.
quest4
In the PKey do you have referential integrety checked? How about Cascade update and delete? Just a thought. hth.
strive4peace
make sure you have used the Control Name to link, not the ControlSource
kalkns
Yes, I have referential integrity checked and cascade update checked. I am also using the control name on the form not the control source.
strive4peace
do you have the ControlSource property set and is it in your underlying recordsource for your subform? If the Visible property is No, change it to Yes until you get this problem resolved.
Please paste in the values for
LinkMasterFields
LinkChildFields
and the control source for each
and the recordsource for the main form and subform
kalkns
The controlsource propoerty is set in the parent forms, but what to you mean by being in the underlying recordsource for the subform? I'm s newbee at Access (although I know a little about programming and logic).
lso the Main form is the File Number Form, its subform is the Area/Sent By form titled Utility Letter, the LinkMasterField in File Number is File Number ID to the LinkChildField in Utility Letter is also named File Number ID. The other forms are similar being the master and child fields linked are named the same.
The control source for each form is the corresponding table where the data will be stored. The recordsource for the main form is the File Number table and the subform is the Utility Letter table.
strive4peace
perhaps you'd better attach your db so I can see exactly what you are doing...
kalkns
can I e-mail it to you? It has address and contact names of the companies we send the letters to.
quest4
Why not just make a copy of your dbase and strip it of everything except the tables and forms that are messing up. The strip must of the data, just have a couple records in there to play with, and they can be fictishious. hth.
kalkns
Okay, here is the database stripped with fictional data. I haven't yet fixed or finished any of the reports, or some of the queries, so please ignore those that aren't relevant to the problem I have at hand.
Thanks!
quest4
Where is it? I must be going blind, because I can not see any attachment.
strive4peace
attachments must be zipped up
lso, please tell me exactly which objects to look at
kalkns
For some reason the database is not fully uploading.
kalkns
Let me try one more time. I zipped the file.
kalkns
Sorry, for whatever reason, the database file will not completely upload.
kalkns
I'm trying to post the database again.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.