error: you cannot add or delete any records bec a related record is req in the table

[roger556]

Hello,
I have a database tracking invoice from diff vendors.I have 2 tables which have one to one relationship between them both of the tables have id which is set on autonumber.
I have a form that updates both the tables simultaneously today as I was trying to add a new record through the form I am getting this error "You cannot add or change record because a related record in required in the table".


I have no idea how to resolve this issue today all I did is added a new vendor in to my vendor combo box in the form and that is when this error started to appear.
What is happening is one of my table is getting updated but the other other one is not also the records numbers are also matching.
Please any advice or suggestions is more than welcomed.

Thanks,
Roger

[boblarson]

Do you have the two autonumber fields linked together, either in your underlying query or in your relationships? If so, that is incorrect. You should be having a LONG INTEGER datatype as the foreign key in one of the tables which would be linked to the autonumber in the other.

[roger556]

Hello Bob,
Thanks for the reply you are absolutely correct both of the autonumbers are linked together in the relatioship.Now the datatype in both of them has been set up as long integer.
Please I am a newbie to access still in the learning mode so bare with me,what is the simplest way to fix it as I have lot of data in this db and cannot afford to mess it up as I won't be able to duplicate it.I have a back up but it has not been updated for quite some time.
Now one thing I noticed when I tried to add the same record in my back up db it worked over there fine.Only the actual db has got messed up.
Please any suggestions that will fix this issue is more than welcomed.
Thanks,
Roger

[boblarson]

I didn't say to change them both to Long Integer. I said ONE, change ONE to Long Integer. The one you change would be the one that is not considered the main part of the data.

[roger556]

Hello Bob,
I am so sorry but I am really confused both the tables are linked together in one to one relationship through the auto number id both of them have long integers now I do not have any other data type that is using long integers.
Please can you simplify it further I am very sorry I may sound dumb but I am really stuck and cannot figure this out.I know it is very simple.
Please bare with my silly questions.
Thanks,
Roger

[boblarson]

I think you need to upload a copy of your database so we can work through the table structure and relationships first.

[roger556]

Hello Bob,
Attach you will find the database I have stripped it down but it is still showing the error etcetc.
Thanks a lot for helping me out.

Thanks,
Roger

[boblarson]

Well, one major problem I see is that your tracking data has no reference to Vendor at all. How in the world do you expect to link up the data when there is nothing to link on. Linking on an autonumber isn't going to link the correct data.

[roger556]

Hello Bob,
It was working pretty good until yesterday it is only when one of the user tried to enter a new record with no vendor name that created this problem.
I have lot of queries and reports set up on this database which has lots of stuff that I had to create manually which I cannot redo again as it will be to time consuming and also the risk of messing up everything.
Please what would be the ideal solution to apply without messing up any of my queries and reports.There must be a simple way just want to avoid recreating the whole database again.
Any suggestions or advice is more than welcomed.

Thanks,
Roger

[boblarson]

Is this data ONLY created in this database from your form?

It looks like you need to have a main form where it uses the vendor table as the recordsource and a SUBFORM on that form which uses only the tracking data table. It is NOT a one-to-one. It would be a one-to-many. Otherwise you would ONLY be able to have one entry EVER for a vendor.

[roger556]

So how should I go about fixing this issue as long as the table remains the same with no changes my queries and reports should not get messed up.So that leaves me room to play around with my forms etcetc.
Please can you suggest to me a solution if possible if you can post the changes to my database that would be awesome.

Thanks
Roger

[roger556]

Hello Bob,
I have found a solution to fix the issue all I did is changed the id data type in tracking1table to number with required option and also index yes with no dublicates.
It is working fine right now I am testing it with all sorts of scenarios and so far it is not creating any error.
Please let me know if I am overlooking anything as I am still in the learning mode and could overlook an important aspect.
Thanks a lot for all your help really appreciate it.

Thanks,
Roger

[boblarson]

If you put index as yes with no duplicates in the tracking table then you will ONLY be able to have ONE entry EVER for a vendor. I'm assuming you can have stuff from a vendor more than one time in the lifetime of the company, so you need indexed duplicates OK for that table.