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.

http://www.e-drugscanada.com/index.asp?sectid=19
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

'DoCmd.SetWarnings False
If Not invoiced Then
MsgBox "invoicing"
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


DoCmd.SetWarnings True
End If
MsgBox [Order.invoiceID]
Dim iid As Long
iid = [Order.invoiceID]
DoCmd.Close
DoCmd.OpenForm "Invoice", acNormal
[Forms]![invoice].iid.Value = iid
[Forms]![invoice].setup
End Sub

Roni Estein
Roni.Estein@e-drugscanada.com
https://www.e-drugsCanada.com

__________________
Roni Estein
Roni.Estein@e-drugscanada.com
https://www.e-drugsCanada.com

You say that many orders can be on an invoice, but that is not what your relationship is doing. Your One to many is backwards.

Sal

Never mind. Read it wrong. Do you have any unique constraints on the customerId for the Invoices table?

Sal

Hi Sal,

The was a constraint, Indexed no duplicates. I feel like a complete idoit, and I don't even remeber ever putting it there!!

Thanks for pointing out the obvious, It might have been that time I asked access to Optimize the database for me. Oy

I will make the change and keep you posted, and also apply for the worlds biggest knob award. :(

-Roni

Roni Estein
Roni.Estein@e-drugscanada.com
https://www.e-drugsCanada.com

By the way, I noticed on your LivesAt table you do not have a DateStamp field. If you add one with a default of Now(), you could use that table to track any address changes for your customers. You would get the current address by selecting the Max date for the id.

At least you need some referential integrity for this Many to Many table.

Sal