Please help..

I am using Access '97 to hold design information and the process information behind it to achieve the design.

I have a table for design and a separate table for process information. These are joined by a 1 to many relationship on a part number field (there can only be one design but may processes that can achieve that design).

The problem is when I go to input the data through a form. The part number is the same for both design and process so I don't want to have to enter it twice so I have one text box to enter the part number.

The problem is that if i set the text box data source to the part number in the process table, if the database can't find the part number (i.e. It has not been entered yet), I get an error message saying that "Microsoft Jet Database engine can't find the record with matching field".

On the other hand if I set the data source to the part number in the design table, I get an error message telling me that the record can't be added because it already exists.

HERE'S THE TRICKY PART:

Ideally what I want to do is set the part number field data source to the process table and tell Access to create a new record in that table if the record can't be found.

Can I do this through code?

Any help would be greatly appreciated.

Not sure if I understand your problem but I assume your main form has the design no. and a sub form for the process linked by the part number?

I'm not actually using a sub form. The form is based on a query which includes the fields from the two tables.

Thanks for the response.

I believe that query is the problem. Create a main form based on the design table and a subform based on the process table. Be sure to set the subform's data properties, link child and link master to your part number fields. If you create the subform with a wizard it should take care of that for you.

After re-designing the forms in this way, the process side of things works perfectly. The process information is automatically displayed when I enter a part number. The problem now is, if I enter a part number of which there is already a design, this part does not automatically fill the fields and creates an error message saying that there is a duplicate. Is there a way I can change it so that I still do not have duplicates but it will display the stored design information and allow updates but not actually create a new record in design but view and edit the existing one?