Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 22nd, 2003, 07:17 PM
Authorized User
 
Join Date: Sep 2003
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default Help solving elusive key violation

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
 
Old October 23rd, 2003, 10:02 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old October 23rd, 2003, 10:06 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Sal
 
Old October 23rd, 2003, 02:41 PM
Authorized User
 
Join Date: Sep 2003
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old October 23rd, 2003, 03:11 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Violation of PRIMARY KEY constraint??? twyce C# 1 September 9th, 2008 01:23 AM
Violation of primary key dhoward VB.NET 2002/2003 Basics 4 May 31st, 2007 10:19 AM
Primary Key Violation error code hasanali00 BOOK: ASP.NET Website Programming Problem-Design-Solution 1 April 19th, 2005 07:41 PM
primary key violation muralikeane Classic ASP Databases 8 May 21st, 2004 09:24 PM
elusive XLA security problem in Japanese excel 2k monicai Excel VBA 1 July 8th, 2003 07:40 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.