Erratic query behaviour?

[matt beamish]

Hi. I have a question prompted by database behaviour that I do not understand.

I have two tables with related records, one being a subset of the other. I have joined the tables in a query with a Left outer join relationship. I have put the query results in a form, and when a User clicks a Yes/No box, a new record is created in the subset table automatically generating an ID number using an Autonumber set to start at chosen integers.
However, recently error has occured. When I view the query results some records will not allow me to create a record in the subset table stating "You can not add or change a record because a related record is required in table x", eventhough that very record is the record which the query is displaying. Some records will. The behaviour is erratic and I cannot see what is causing it. Might this be indexing related?

Any help appreciated as ever

Matt

 

[Dennisk]

this is usually caused by incorrect referential Integrity. Another way this may occur is where the foreign key has its required property set to no.

I would create a query to check if you have any orphaned records on the many side of the one-many relationship.

 

[matt beamish]

Thanks for the input, but I cannot see an error with the Referential Integrity - there are no orphaned records - but I am using the Referential Integrity to force the creation of the new record on the subset and it is this which seems to be failing. If I were to post the database might you have a look? Ive stripped out much irrelevant data, but its still over 6Mb.

 

[matt beamish]

I am getting nowhere with this, and would really appreciate some help.
Query "Q_Pot_Spec_PR_entry" shows two joined tables
T_Pot_Spec_PR and T_Pot_FSN_PR
with all records from T_Pot_Spec_PR, and those that match from the T_Pot_FSN_PR.
Clicking in the "Featured Sherd" Yes/No should create a record in the second table with 1. a copy of the "Pot_Record" from the first table and 2. a new Autonumber PK

I find that I can create up to 4 new records, and then I get "You cannot add or change a record because a related record is required in Table T_Pot_Spec_PR" . Closing Dbase down and reopening seems to sort it for a few more records.

Help PLease?