OK this is a continuation of a thread started a while back (... this thread... )
The Situation: (follow through attached example and this will make more sense)
Get a: "Cannot add record because corresponding record needed in tbl Address" error when I attempt to enter new company data in a subform within the main Contract form.
(Each Company has an address FK (re billing address), and each contract has an address FK (location where service contract is taking place). I am using a main form "Contract" to enter a selection of a Company (paying for contract) and location (as desc above)... and other service-related details. The user is given the option of entering a new company directly into the contract, but this produces the error above.
The way I see it the problem lies in the NotInList event of the CompanyID combo box in the frmMaintContract.
I've done the following:
1. Added an 'Insert Company' record to tblCompany and had the Contract form default to that record.
2. Added an 'Insert Address' record to tblAddress and had the Contract form and Company form default to that record.
3. Made a NotInList event of the CompanyID combo to enter the data printed in the combo into the CompanyName feild of a new Company record in tblCompany
4. Edited the On Error of the NotInList Event of the CompanyID combo, to insert the "Insert Address" record in the event of Err 3021 (the error in question).
Current Status: When new company is added it gives the error (without going to debug), adds the "Insert Address" record to the AddressID of a new record BUT does not add the typed data to CompanyName, and tells user to select from list. The new Nameless record is in the list and when selected all is peachy.
I do I make this work with no errors?? I'm almost there.
Thanks guys, hopefully if we can solve this it will act as a resource for those in a similar predicament.