Need help: Transfer data from one table to another with Insert Int

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an application that first sets up a quote, then if the quote becomes a
sale, the info transfers from the quote table to sales table. I had this
code working, but now I get the following error:
"The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
Change the data in the field or fields that contain the duplicate data,
remove the index, or redefine the index to permit duplicate entries and try
again."

Code:
Private Sub ProcessSale_Click()
On Error GoTo Err_ProcessSale_Click
Dim dbs As Database
Dim LInsert As String
Dim payfields As String
Dim qfields, sfields As String
Dim sid As Long
'Insert the Quote data into the Sales table
Set dbs = CurrentDb()
ifields = "CustomerID, ProductID, SaleDate, MileageOut, SalesPrice,
TradeInAllowance, SalesTax, TagTitleReg, DownPayment, DealersComp,
TotalPayments, PaymentPer, FirstPaymentDate"
LInsert = "Insert Into tblSales (" & ifields & ") Select " & ifields & "
From tblQuote Where QuoteID = " & Me!QuoteID
dbs.Execute LInsert, dbFailOnError
 
If I had to guess (I guess I do -- you didn't describe your table
structure), I'd guess that you are trying to pass an ID (CustomerID) into a
table that already has that value.

It would help to know your primary key structure, for both tables.

By the way, aside from being able to keep them apart, is there a business
need to keep the sales records in a separate table from the quote records?
That is, if you are holding the same information in two places, why?!
Another approach would be to add a Yes/No field to a single table, and use
that to record the fact that it is now a "sale" record. Or a date/time
field, if you wanted to know when it happened.
 
Back
Top