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
- Private Sub btnDuplicate_Click()
- Dim dbs As DAO.Database
- Dim rst As DAO.Recordset
- Dim F As Form
- 'return database variable pointing to current database
- Set dbs = CurrentDb
- Set rst = Me.RecordsetClone
- 'tag property to be used later by the append query
- Me.Tag = Me![NewMakeup]
- 'add new record to end of Recordset object
- With rst
- .AddNew
- !ID = Me!NewMakeup
- !Comments = Me!Comments
- !Customer = Me!Customer
- !Description = Me!Description
- .Update 'save changes
- .Move 0, .LastModified
- End With
- Me.Bookmark = rst.Bookmark
- 'duplicate all the associated lines
- DoCmd.SetWarnings False
- DoCmd.OpenQuery "cpyMakeup"
- DoCmd.SetWarnings True
- 'requery the subform to display the newly appended records
- Me![Lines Subform].Requery
- End Sub
Results of clicking button:
Duplicate Output Destination 'Line'.
debugger then highlights this line:
DoCmd.OpenQuery "cpyMakeup"