471,322 Members | 1,798 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,322 software developers and data experts.

Duplicate Record with Associated records

MS Acc 2003, XP

Thank you in advance for any help.

I have tables called "Makeup" and "Lines". Each makeup can have multiple lines.

Goal is to create a new "makeup" with identical "lines" except with a new makeup id.

I created a form Makeup Copy with Line Subform. In the main form there is ID (key field), Comments, Customer, Description plus an unbound field "NewMakeup". Each line of the subform has the fields "Makeup (primary key), Line (primary key), Product, mm"

I added a duplicate button with code below, having the intention of duplicating the Makeup record with the NewMakeup replacing ID.

Append query has

Field: Makeup
Append to: Forms!Makeup Copy.Tag

then the line, product, and mm columns as usual

with the final column having
Field: NewMakeup: CLng(Forms![Makeup Copy]!Makeup)
Append to: Line

And here is the VB for the "duplicate" button:

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnDuplicate_Click()
  3. Dim dbs As DAO.Database
  4. Dim rst As DAO.Recordset
  5. Dim F As Form
  7. 'return database variable pointing to current database
  9. Set dbs = CurrentDb
  10. Set rst = Me.RecordsetClone
  12. 'tag property to be used later by the append query
  14. Me.Tag = Me![NewMakeup]
  16. 'add new record to end of Recordset object
  18. With rst
  19.     .AddNew
  20.     !ID = Me!NewMakeup
  21.     !Comments = Me!Comments
  22.     !Customer = Me!Customer
  23.     !Description = Me!Description
  24.     .Update                     'save changes
  25.     .Move 0, .LastModified
  26. End With
  27. Me.Bookmark = rst.Bookmark
  29. 'duplicate all the associated lines
  31. DoCmd.SetWarnings False
  32. DoCmd.OpenQuery "cpyMakeup"
  33. DoCmd.SetWarnings True
  35. 'requery the subform to display the newly appended records
  37. Me![Lines Subform].Requery
  39. End Sub

Results of clicking button:

Duplicate Output Destination 'Line'.

debugger then highlights this line:

DoCmd.OpenQuery "cpyMakeup"
Jun 17 '08 #1
1 1985
I'm pretty sure the problem is with the query. I've made several attempted debugger always flags the line in the VB code for the button that points to the query.

Current status:

Field: Makeup
Append to: Forms!Makeup Copy!Tag

(I changed the Tag to equal the old ID rather than the new one)


Field: New: Cling(forms![Makeup Copy]!ID)
Append to: NewMakeup

Data type mismatch in criteria expression.
Jun 18 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Giloosh | last post: by
4 posts views Thread by Thomas Arthur Seidel | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.