Write to table when necessary

[Autoeng]

Hi guys,

Been a while since I've been on as work has been hectic to say the least. I've got a problem that I hope someone can help me out with though.

Got a new db that I'm building that is linked to an AS400 mainframe db. From the mainframe db I am linking to the primary table (tblAS400Data) of my new db. I am pulling in information related to parts. I cannot alter / add / delete to this table. I want to be able to add information to the part information in that table so I constructed a new table (tblComments) with PartNumber as pk and a Comments field. I established a relationship between tblAS400Data field PRDNO and tblComments field PartNumber.

I built a form based on a query of tblAS400Data with a subform based on a query of tblComments. Linked the form / subform. How can I, when I add comments to a part, write the part number (PRDNO) to the tblComments so that it gets saved? Currently when I try I get "PK cannot be null" as tblComments has yet to be populated with the PRDNO/PartNumber. I'd like to do this as I add comments to each part instead of populating the tblComments with all parts at one time because parts are constantly being to add tblAS400Data.

Any help would be greatly appreciated.

 

[neileg]

I constructed a new table (tblComments) with PartNumber as pk and a Comments field. I established a relationship between tblAS400Data field PRDNO and tblComments field Comments

Surely the relationship is between the part number field in both tables. Am I missing something obvious?

 

[Autoeng]

Thanks for catching that error Neil. You are correct in that the relationship is between PRDNO and PartNumber in the tables.

 

[Friday]

Auto: I can't link to an outside db, but I built the rest of your scenario. As long as I only add one record for each part number to tblComments, it works ok for me. Field partnumber in tblComments auto fills in the value from tbleAS400.

 

[Autoeng]

Friday:

You the man! See a pm I sent ya.