Click to view attachmentI have a form where I am trying to add multiple records to at a time.
• The form “Form1” pulls data in from two tables
• Table “Orders” is just order details and is pre-populated of the necessary data (No data manipulation will be needed here but the data will need to be displayed on the form)
• Table “OrderItems” is essentially empty in my form, this is the table I want to add data to. The thought be here is that a person will be going though each record and manually adding in the additional information required. Each item could have multiple entries, hence the need to be able to add multiple entries at once.
o I will need the ability to add multiple records per time to the table
o For each order number, all records will need to have the same Order ID as the original order
I have added a sample DB which I have included narratives to better explain what I am trying to accomplish.
Any help would be greatly appreciated.

Hi: It would seem you should try a form/subform approach. In this approach the OrderItems in the subform are automatically linked to the OrderID in the main form. And this will allow any number of OrderItems to be created, automatically being put in the table. See the revision to your db attached.

HTH
Joe

That is perfect. Thank you for your help on this, it is much appreciated.

Happy to help. Glad that is what you needed.

Regards,
Joe

Quick question, how are you making the table "OrderID" field pre populated from the frmOrders "OrderID"?

Nevermind, I figured it out. Thanks again.

When you insert a subform in a form, the wizard asks for the linking fields. Since the OrderID is in both the form and subform, that is the linkage you choose - and thus the criteria (see the link master field and the link child field in the subform properties). This linkage then automatically finds all the like OrderIDs to populate the subform - and by default prepares the next record to be added. Since the link can only be with the same OrderID, that OrderID is automatically inserted for the new record.

I usually hide the link in the subform records, since it is redundant - but it is there and is added to the record that is stored in the OrderItems table. I just left it there for clarity in this demo.

Also, you should consider a couple of other things for your form: See the revised2 demo attached for the use of a combobox to select the size - saves having to type it in and maybe make a mistake. In addition, you might consider (not shown) having a pricing table that looks up the SKU and automatically inserts the price (which can be overridden if desired). Just some thoughts...

HTH
Joe

(Just noticed your "nevermind" - and you are welcome - glad to help.)