Data error - help please

[matt beamish]

I have had little reponse to this on the Queries forum so am trying here in a hope that someone can advise me. You can tell Im only a dabbler and not a programmer from my language....

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 field of T_Pot_FSN_PR will 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

Within the query or a form view of the query 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" message. Closing the Dbase down and reopening seems to sort it for a few more records.

I have the same set up on a sister set of data and it works fine. However if this is a known problem I should not be setting up my tables this way. How should I then do it to create related records in separate tables that automatically enter the common field?

Help PLease?
Attached Files A2-2003.zip (205.2 KB, 0 views)

 

[The_Doc_Man]

First things first - posting in multiple sections of this forum is considered bad form and wasteful of resources. Please don't assume that because you had no takers under Queries that you'll get better results under General. It is a patently false assumption. In fact, you are more likely to get LESS results because we avoid impatient persons.

Second, from a form where there is a clear parent/child (or if you prefer, a clear one/many) relationship, make the "many" side on a sub-form and the "one" side on a main form. Then using the control wizard, put the sub-form in a subform control on the main form. The wizard will kick in to ask you which field is the "common" field between the two - and it doesn't even require them to have the same name. If you have a proper relationship defined between the two tables, the wizard won't even bother to ask. It will just create the relationship of the forms to match the relationship of the tables.

 

[matt beamish]

Thanks for reply. I do not want to put the child table in as a subform. I want to see the two tables joined in a query with a form showing the results. Subforms prevent searches or filtering on the child form.

My question is: Why is the query inconsistent in permitting and then not permitting record entry.

Thanks again
Matt

 

[matt beamish]

A database colleague has established that the erratic error is derived from the presence of a third table in my initial query. With the third table (which relates only to the parent table) removed, the use of the query to join to tables and be the basis for data entry works smoothly.
Matt