unable to add new dulplicate record, run time error 3426

[Chris]

I am trying to duplicate a record that is shown on form in order to create
a new record with the same data but new revision number. The following code
used to work but for some reason now gives me a run time error 3426 This
action was cancelled by an associated object and the .addnew is highlighted.

Darned if I can figure it out. Using Access 2000 with SP3 references to ADO
and DAO. Database has been split.

Hope someone can point me in the write direction.

Private Sub cmdRevise_Click()
Dim strSQL As String
Dim rec As DAO.Recordset
Dim strQuoteID As String
Dim db As DAO.Database

Set db = CurrentDb()

Set rec = Form_Quotes.RecordsetClone
Me.RecordsetClone.Bookmark = Form_Quotes.Bookmark

If Me.Dirty Then
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else

'Duplicate the main record
With rec
.AddNew

!QuoteDate = Date
!QuoteNumber = Me.QuoteNumber
!ProjectID = Me.ProjectID
!EmployeeID = Me.EmployeeID
!RevisionNumber = Me.RevisionNumber + 1
!QuoteID = Me.QuoteNumber & ("-R" + CStr(Me.RevisionNumber +
1)) 'primary key


.Update
.Bookmark = .LastModified
strQuoteID = !QuoteID

'Duplicate the related records.
If Form_QuoteDetails.Form.RecordsetClone.RecordCount > 0 Then
strSQL = "INSERT INTO QuoteDetails ( QuoteID,
ProductID,UnitPrice,Discount,Quantity) " & _
"SELECT " & "'" & strQuoteID & "'" & " As
NewQuoteID,QuoteDetails.ProductID, QuoteDetails.UnitPrice," _
& " QuoteDetails.Discount, QuoteDetails.Quantity FROM
QuoteDetails WHERE(QuoteDetails.QuoteID = " & "'" & Me.QuoteID & "'" & ");"
Debug.Print strSQL
db.Execute strSQL, dbFailOnError

Else
MsgBox "Main record duplicated, but there were no related
records."
End If

'Display the duplicate.
Me.Bookmark = .LastModified
End With
End If

Set db = Nothing
End Sub

 

[TC]

I am trying to duplicate a record that is shown on form in order to create
a new record with the same data but new revision number. (snip)

This is really not the way to go.

Try reading: http://support.microsoft.com/support/kb/articles/Q100139.ASP

HTH,
TC

 

[CP]

Thanks for the post but not sure how this helps. The reason we are
duplicating a record is to then make changes to it and or additions and have
a record of the new revision and any previous revision as they are made and
then sent out to the customer. If there is a preferred way of handling
revisions like this please let me know, can't really see it from the link
you sent.

Thanks
Chris

 

[TC]

Say the record has certain details 'X' that are the same for every revision,
& other details 'Y' that are different for different revisions.

You don't do this by having 'X' and 'Y' in the same table, & duplicating
records when you need to add a new revision.

You do it by having one table for the 'X' details, and another (related)
table for the 'Y' details. Then, to add a new revision, you just add a
record to the 'Y' table. You do not need another record in the 'X' table -
you just use the existing one.

Then you can use queries & other mesna to pull that data back together from
the 2 tables.

This is the concept of "normalization" - the topic addressed by the
reference I gave you.

HTH,
TC

 

[CP]

I can see your point if the new revision is just adding products but what if
the new revision is using a number of different items instead of the ones
that were in the earlier revision. Its in unlikely that the details X will
be the same for every revision. It is also going to happen that the new
revision will have different quantities from the earlier revision as well as
additions and deletions of products. Would your recommendation still work in
that case. I suppose our instance the wording revision really implies a new
quote, but is the current way that a history is kept of the details sent to
the client.

Thanks
Chris

 

[TC]

Chris, all of your questions are covered by the related topics of "data
modelling" and "normalization".

Data modelling is (roughly) the process of answering the question, what key
"things" do I want to store data about? In your case, the answer might be
Products, Suppliers, Customers, Quotations, Revisions.

Then for each of those things - independently - you need to answer the
questions:

o What data do I need for that thing? (For Products, that might be
Product ID, Product Name, Supplier ID.)

o What is the "primary key" of that thing? (That is the data element or
combination of elements which uniquely identifies a particular instance of
that thing, distinct from any other instance of that thing. For Product,
that might be Product ID.)

Then on the basis of all that information, you design a table structure
compliant with the guidelines in the article I referred you to.

This might all seem very complicated, but it is the database equivalent of
building a proper foundation for a house. You could certainly build a house
by laying the formwork directly onto the ground (without any foundation) -
but if you did that, we all know what would happen when the first strong
wind came along!

So, read-up more on the topics of "data modelling" and "normalization". All
the time you spend on that, will be time well spent. It will be more
productive, in the long run, than having someone tell you what the tables
should be.

HTH,
TC