I have a db setup to track customers and invoices. Design is as follows.

customers tbl:
customerID-PriKey (autonumber)
name
address
phone

invoices tbl:
invoiceID-PriKey (autonumber)
customerID
subtotal
tax
total

invoiceitems tbl:
ID-PriKey (autonumber)
invoiceID
item
price
quantity
total

One-to-many relationships are setup between customers and invoices based on customerID and between invoices and invoiceitems based on invoiceID. I have a single form for customers with nested subforms for invoices and invoiceitems. My goal is to have a single form to browse and add customers and browse and add invoices.

The challenge I am running into is that a new records has to be created in the invoices table before items can be entered for a new invoice. This is a challenge because all of the fields in the invoices table are either autonumbered (invoiceID), based on a foreign key (customerID) or calculated (subtotal, tax, total). So it doesn't make sense to require the user to enter data that creates a new record in the invoices table before they start entering invoice items.

My solution so far has been to use the On Enter event of the invoices subform to set the customerID field to that of the current customer and save the record before the user can enter invoice items. That works except if the user is browsing invoice records and browses to a new record, it bypasses the On Enter event and the user gets an error trying to enter invoice items. I've tried adding VBA code to set the focus on a control on the main form and to enable/disable the subforms, but that has just caused other issues. Another challenge is that invoice records can get created without associated invoice items ever being entered.

How can I improve my table and form design given my goal of managing customers and invoices on one form? Is there a way to ensure an invoice record gets created before items are entered for a new invoice? What can I do to prevent or cleanup invoice records that get created with no associated invoice items?

Thanks in advance for your help and sorry this is kinda long winded.

How can I improve my table and form design given my goal of managing customers and invoices on one form?

The basic table design is fine, although you're storing "total" in the invoice table and the invoice details table. Store it only once, in the invoice table.

So it doesn't make sense to require the user to enter data that creates a new record in the invoices table before they start entering invoice items.

Actually it makes perfect sense. An invoice record must be created before invoice details records can be created. It depends what you mean by "entering invoice items", though. You can provide unbound fields your users can enter invoice items into, then hit a button to create a new invoice record and then the associated invoice items record(s).

What can I do to prevent or cleanup invoice records that get created with no associated invoice items?

The best option is to prevent it from happening. You can easily have a "Delete Invoice" button but to my way of thinking, it makes no sense to create records which aren't necessary. They will bloat your database. The space consumed by creating these records isn't reclaimed when you delete them. You must compact the database to reclaim it.

Using a single form to both browse archived data and create new records can be challenging, but is absolutely possible. You'll need a combination of bound and unbound controls on the form in order to accomplish this, along with some creative code to move the focus around and refresh or requery certain controls as necessary. It would be easier to use separate forms for browsing old invoice records and for creating new invoices, but that would take the fun out of it then, wouldn't it? :)

I'm using total in the invoices table for the invoice total and total in the invoiceitems table as a line total.

Thanks for the advice about using unbound controls. I think I'd been fighting with this too long to take a step back and try a fresh approach. Should be just a matter of moving data between the bound and unbound controls as records are browsed/created and making it all transparent to the user. Also lets me ensure it's a valid invoice before saving to the invoices table. Hopefully it will be smooth[er] sailing from here.

I wouldn't store totals at all. They are easy enough to calculate and you don't have to worry about them becoming incorrect if someone adds/deletes/changes an item after the order is entered.

Base the invoices subform on a totals query that joins to the details table so that it can sum the item amounts. This won't cause a problem because the form won't be used for updating. You will be adding invoice records behind the scenes (see below). Set the Invoice subform to be read only so that the "new record" won't appear at the end of the list. Add a double-click event to the invoiceID field on this subform. Use the where argument of the OpenForm method to open the invoice details form to the correct invoice.

To solve the add problem, I would add a button to the main form to add a new invoice. The code behind the button would create a new invoice record and then bring up the invoice details form. Add subtotals to the footer of the details form so you will have a running total as the items are entered. Base the details form on a query that calculates the extended total. It will be recalculated as form fields are changed. Use the Nz() function since in the beginning, at least one of the fields will be null.

Select fld1, fld2, fld3, nz(price,0) * nz(quantity,0) As ExtendedPrice
From yourtable;