Hi Guys,

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.

BG

Have a look at the Query qryMaintContract. Try enetering data directly into this query. You will get the same error.

Hi Rainlover... Good Point..

I suppose the question is WHY..

Looking at the Query I think the anomoly is that Contract record needs an Address and Company record to be made an actual record (You'll notice that there is no ContractID populated in the query)... BUT in order for the Address and Company records to be added to the Contract record... they need a ContractID!!!!

1. This seems to be the issue to me anyway, am I on the right track? If so it would be a flaw in Access itself
wouldnt it?

I wonder if it would be possible to write a script into the onLoad or BeforeUpdate sections of frmContract that somehow cloned the record as a false ID of some sort until an AddressID and CompanyID are selected.

2. Would that work? What might that look like?

Thanks for your thoughts.

BG

This is not a problem with Access.

To be honest I cannot understand your Relationships. Why are all three tables related to each other. I cannot see the purpose of relating the TblContract to tblAddress.

Also tblAddress is not Normalised. City should be a FK to another table which hold PK, City, StateOrProvince and PostalCode.

Perhaps Contact Notes could also be held in a Table of its own. And get rid of the space between Contact and Notes.

Have a look at the attached Mod. It may help.

Hi Rainlover,

I can see that you added the Execute strSQL2 into the main IF statement of the NotInList event, It produced a error in the code because of the strSQL2 being executed in the Error Statement. I'd tried this before I posted my initial attachment.

Did you alter anything else? This really seems like an ugly problem doesnt it... I wonder if its even possible to have RI enabled on these relationships..

Is there somthing I'm missing in your modifications?

Thanks for your time,

BG

A dicussion of my realtionships is in this thread . That being said I think this my be an issue with Access.

Has anyone seen or worked around this issue before? Sorry to be forward, I'm just really stuck on this little hickup!

Thanks as Always,

BG

Forget my previous attachment.

Try this one instead. I have made a lot of changes to your tables. So you will get errors in the form. But will this structure solve the problem for you?