By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,136 Members | 1,212 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,136 IT Pros & Developers. It's quick & easy.

Duplicate Record with Associated records

P: 6
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()
  2.  
  3. Dim dbs As DAO.Database
  4. Dim rst As DAO.Recordset
  5. Dim F As Form
  6.  
  7. 'return database variable pointing to current database
  8.  
  9. Set dbs = CurrentDb
  10. Set rst = Me.RecordsetClone
  11.  
  12. 'tag property to be used later by the append query
  13.  
  14. Me.Tag = Me![NewMakeup]
  15.  
  16. 'add new record to end of Recordset object
  17.  
  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
  28.  
  29. 'duplicate all the associated lines
  30.  
  31. DoCmd.SetWarnings False
  32. DoCmd.OpenQuery "cpyMakeup"
  33. DoCmd.SetWarnings True
  34.  
  35. 'requery the subform to display the newly appended records
  36.  
  37. Me![Lines Subform].Requery
  38.  
  39. End Sub
  40.  

Results of clicking button:

Duplicate Output Destination 'Line'.

debugger then highlights this line:

DoCmd.OpenQuery "cpyMakeup"
Jun 17 '08 #1
Share this Question
Share on Google+
1 Reply


P: 6
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)

and

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.