Duplicate Record inc. Subrecords (kind of!)

[randallst]

Hi there,

I am currently making a material movement database and I want to make a simple button command to return materials when finished with.

Basically I have managed to make a command where when I press the 'Create Returns Receipt' it will populate everything I want but the Sub Records are still the original records, not a duplication.

Any idea how I can duplicate a record and its subrecords? I have attached a bare copy of my database for reference.

Also, seems to keep popping up asking for Receipt ID. I just click OK and it runs as I want anyway....think im losing it now as I've been working on it for too long haha! :banghead:

All the best
Stu

 

[missinglinq]

Any idea how I can duplicate a record and its subrecords?

As is often the case, Allen Browne has a hack for that!

Duplicate the record in form and subform

Linq ;0)>

 

[randallst]

As is often the case, Allen Browne has a hack for that!

Duplicate the record in form and subform

Linq ;0)>

Thanks for that. I have been playing with the code on the webpage, the main Form information is transferring to a new record as I want it to, but it's not copying any of the information from the SubForm.

Here's my code for reference, any idea where I am going wrong? (Please Note: I have removed spacing out of the names of the cells);

Private Sub Command179_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.

'Save any edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!NameofOriginator = Me.[IntendedRecipient]
!LocationofOriginator = Me.[LocationofRecipient]
!OriginatorsReceiptNo = Me.[OriginatorsReceiptNo]
!IntendedRecipient = Me.[NameofOriginator]
!LocationofRecipient = Me.[LocationofOriginator]
'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the related records.
.Bookmark = .LastModified
lngID = !ReceiptID

'Duplicate the related records: append query.
If Me.[Materials Subform].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [Materials] ( Brand, Product, Category, Quantity, Remarks ) " & _
"SELECT " & lngID & " As NewID, Brand, Product, Category, Quantity, Remarks" & _
"FROM [Materials] WHERE ReceiptID = " & Me.[ReceiptID] & ";"
'DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler
End Sub

 

[JHB]

... but it's not copying any of the information from the SubForm.

[B][COLOR=Red]'[/COLOR][/B]DBEngine(0)(0).Execute strSql, dbFailOnError

Because you've comment out the line which should do the copy, (remove the apostrophe in front of the above code line).

 

[randallst]

Because you've comment out the line which should do the copy, (remove the apostrophe in front of the above code line).

Cheers, I've gone blind on the coding after staring at it for so long!

Changed it and now getting the following (highlighting the section I just took the apostrophe from);

Run-time error '3346':
Number of query values and destinations fields are not the same.

:banghead:

 

[JHB]

..
Run-time error '3346':
Number of query values and destinations fields are not the same.

:banghead:

Because you try to insert 6 values (NewID, Brand, Product, Category, Quantity, Remarks) into 5 fields (Brand, Product, Category, Quantity, Remarks).
I would also suggest you to add a space after "... Remarks" & _ (it should be "... Remarks " & _ )

 

[randallst]

Made the relevant adjustments and running with no errors, but still not populating the Subform.

 

[JHB]

Did you check if the data got added to the table?
What if you close and open the form again, does they show then, (if yes then requery the subform)?
If you can't get it then post your database with some sample data, + a description of how to reproduce the "problem", zip the database.

 

[nortonm]

As is often the case, Allen Browne has a hack for that!

Duplicate the record in form and subform

Linq ;0)>

And a beauty it is too - he has such elegant scripts that fella. Worked exactly like I needed it to with not too much adjustment at all.