Synchornized Updates to Multiple Forms and Tables After Data Entry

[PinkDuster]

Hello All,

I think I am missing a fundamental command needed when updating records in auxiliary tables which link to the main tables used in my Customers and Orders forms, and the Shipping Addresses subform. After entering shipping address data or selecting a shipping address, and attempting to save the Order record there are two errors:



When I force a close and check the Orders, I can see that a record was added, however something isn't right because the Invoice numbering sequence is not updating (shows "00000" in below image).



Any guidance is appreciated.

Thank you,

[davegri]

I don't get those errors in the DB that I provided in your previous thread. I can see changes to the form in the the above post, so the db has changed. You'll need to post your latest version.

[PinkDuster]

Hello davegri, yes I did make a couple of changes since then (when it worked). I added a button to copy data from tblCustomers to tblShippingAddresses, added a feature to automatically increment an invoice number (not using an autonumber field), and revised a report tied to Orders. Attached is current db. Thank you,v4.3f.zip

[davegri]

v4.3f -davegri-v03.zip
Have a look. Got invoice number increments working.
The combobox on the main form for shipping address is now synchronized with the shipping address subform tab.

[PinkDuster]

v4.3f -davegri-v03.zip
Have a look. Got invoice number increments working.
The combobox on the main form for shipping address is now synchronized with the shipping address subform tab.

Hello davegri, yes, thank you they are working now, but I am still getting table error messages when I try to capture an order. Am I missing some kind of Refresh or Requery statement to include prior to saving the order in order to synchronize tblOrders?

I added a few new customers and then added shipping addresses and cc info in the subforms on the order page to test out, since I think the previously used ones are not working correctly from using the Copy button. They update perfectly, as you fixed, but when I hit the Complete Order button, I get an error message. The action must still work because if I close the database and re-open, then I can see the transaction in the transaction table of the Customer form...although I can no longer view the actual transaction by way of the Review Details of Previous Orders button.v4.3f -davegri-v03 - Copy.zip

[ssanfu]

PMFJI, but I see some issues...

tblOrder.fkPmtTerms is Integer but linked to tblPmtTerms.ID which is an Autonumber/Long Integer.


In tblOrder, "Sequence" is a reserved word.
In tblProduct, "Description" is a reserved word.

tblProduct.ProductID (PK) is a text type field. Not a real good idea.

tblShippingAddress has a compound PK.
Only ShipAddID should be the PK field and you should set a compound Index on ShipAddID and fkCustID.


See Microsoft Access Tables: Primary Key Tips and Techniques http://www.fmsinc.com/free/newtips/primarykey.asp



"ID" is a terrible field name...


In "tblOrderDetail", the look up fields should be removed.
See The Evils of Lookup Fields in Tables http://access.mvps.org/access/lookupfields.htm

[PinkDuster]

Yikes! Thank you, I didn't know that those were reserved words, I will need to make changes. I appreciate your review and suggestions. I wasn't sure how to use compound identifications, and am trying to get rid of the Lookup fields.

[orange]

Here is info on composite unique index

Allen Browne's info re reserved words in MS Access

Good stuff Davegri and ssanfu