The basic problem.. After completing most of our office DB, everything looked hunky dorry, until we tried to implement our first refill order. We end up with a key constraint violation which I cannot figure out.
This is a link to a picture of our Access relationships with all of the relevant tables currently used.
The Key violation occurs when a customer has ordered from us in the past and is now placing a second or refill order.
When trying to insert a customer into the invoice table which is already in the invoice table. Because 1 customer can have many orders and many orders can be on one invoice. It should stand to reason the each of the customers many orders can have their own invoice, since a 1 to 1 is not prohibited by the 1 - many relationship established. I have already removed a relationship between customer and invoice directly (just a link relationship) Yet I hesitate to remove the 1 to many cuurently in place. I seems it should work.
After trying for some time to figure out what may be going on, I brought this to the attention of a prof in DB at University. He is also unsure. Any advice would be appreciated.
Here is the code that inserts the information
' 1. Creates a new single person invoice for this order
' 2. Set the order status to invoice printed (1)
' 3. Opens the process invoice which allows you to choose a specific address
' to set to active (a future developments may be to add another address here)Private Sub cmdOpenInvoice_Click()
update ' <- a method used to update the data from the user interface back to the tables
Dim newInvoice As Long
If Not invoiced Then
DoCmd.RunSQL "INSERT INTO [Invoice] (customerID, totalAmount) VALUES (" & [Order.customerID] & "," & TOTAL.Value & ")" '<- Key violation occurs here
newInvoice = DMax("invoiceID", "[Invoice]", "Invoice.customerID = " & [Order.customerID])
DoCmd.RunSQL "UPDATE [Order] SET" _
& "[Order].invoiceID = " & newInvoice & ", " _
& "[Order].status = 1 " _
& "WHERE Order.orderID = " & orderID.Caption
Dim iid As Long
iid = [Order.invoiceID]
DoCmd.OpenForm "Invoice", acNormal
[Forms]![invoice].iid.Value = iid