Database Split problem

[Scoutime]

I just split an Access 2010 database I have been using to enter orders for the past 10 years.
Tables and Relationships in the Back-End, and all the rest and links to tables in the Front-End.

With a little maintenance it is working wonderfully. I have one error I can't find what is causing it.

I have a form whose source is a query with 5 related tables in it. I have built it like a wizard; one screen after another.
I enter the part numbers to be order on the first form which is placing them into a table [tblorderdetails] which is tied to a table [tblPartnumbers] for the parts the customer wants to order. The next screen allows me to assign a job number in a related table [tblorders] and then attach that job number [record] to a group of related part numbers.
This has worked flawlessly for 10 years.
However now as I enter a new part number into the above tables through this form - a blank record in the related [tblorders] table is created for every part entered on the first screen. So when I go to the second screen to start creating orders to attach to the parts, there are now two records for each part number entered. This is creating tons of blank unattached records in the [orders] table.

Any thoughts?

[John_G]

Your problem I think is that your are using a multi-table query, and when you add data to one table in it, it automatically creates new records in the other tables as well. My suggestion is to not use a query, as rededign your form as I suggest below.

So when I go to the second screen to start creating orders to attach to the parts,

That is doing things backwards. You should create the order first, then select parts to make up the order details. What you should be using is not a form based on a multi-table query - instead you should have a main form / subform arrangement, the main form having orders as its record source, with the subform having order details as its record source.

In this scenario, tblPartNumbers is a lookup table; you would select from a list of parts a value to place in the PartID field of tblOrderDetails.

(Just as an aside - I don't know your business practices, of course, but it looks like there are a number of fields in tblOrderDetails that perhaps should be in tblOrders. For example, PurchaseOrderNumber - why would the order details records have different purchase order numbers? )

If you need to add new parts to tblPartNumbers, create a separate form to do that; it could be opened on its own, or from a command button on (for example) the order details subform. The important thing to note is that that form would have only tblPartNumbers as its recordsource.

[Scoutime]

Thank you so much for your reply. I agree with your logic and it makes me think that I somehow had it assigning one Order place holder (10 years ago) so it didn't create a new order every time.

The reason for doing it the way I have done is that the customer sends orders for 100s of part numbers that fall into different groups of specs and configuration. This has made it so that my customer service could enter all the numbers to be ordered and by the next screen they have automatically been sorted into the various configuration groups and you then know how many Orders to create. This has worked with out flaw for 10+ years, so I think that in the splitting of things I lost whatever assigned a "placeholder" order, so that you can create new orders and attach them to the various groups of part numbers.

Thank you so much for your quick response. I will search through things for that missing piece.