Duplicating records to same parent record (1 Viewer)

[nhorton79]

Hi Everyone, I have another issue.

I have borrowed some of Allen Browne’s code for duplicating a record and its related subrecords from http://allenbrowne.com/ser-57.html

I have this assigned to a button on a form [frmItem]. The form allows staff to add materials and labour (Inputs) to an item which is attached to a Job. (see the attached relationship pic showing how they are linked).

When you click the button, it brings up another form [frmJobSelect] asking which Job you would like to duplicate the item to.

When you double-click the job in the listbox it copies the item to that Job, which works fine. However, if I want to duplicate the item to the same job, (which happens quite often as we use this for a quoting / job management system and want to have similar items but with slightly different options), it creates a new item but the inputs get duplicated to the original item creating doubles of all the inputs.

My code is

 [SIZE=3][FONT=Calibri]Private Sub btnSelect_Click()[/FONT][/SIZE]
 
 [SIZE=3][FONT=Calibri]Dim Response As Integer[/FONT][/SIZE]
 
 [SIZE=3][FONT=Calibri]Response = MsgBox("Are you sure you want to select this Job?", vbYesNo, "Continue")[/FONT][/SIZE]
 
 [SIZE=3][FONT=Calibri]If Response = vbYes Then[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]DoCmd.OpenForm "frmItem"[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]Forms!frmItem.txtNewJobNo = Forms!frmJobSelect.txtJobSelect[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]DoCmd.Close acForm, "frmJobSelect"[/FONT][/SIZE]
 
 
 [SIZE=3][FONT=Calibri]Forms!frmItem.SetFocus[/FONT][/SIZE]
 
 [SIZE=3][FONT=Calibri]'Duplication Code>>>>>>>>>[/FONT][/SIZE]
 
 [SIZE=3][FONT=Calibri] 'Purpose:   Duplicate the main form record and related records in the subform.[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]Dim strSQL As String    'SQL statement.[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]Dim lngID As Long       'Primary key value of the new record.[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]Dim iNewID As Integer[/FONT][/SIZE]
 
 [SIZE=3][FONT=Calibri]iNewID = Forms!frmItem.txtNewJobNo.Value[/FONT][/SIZE]
 
 
 
 [SIZE=3][FONT=Calibri]'Save any edits first[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]If Forms!frmItem.Dirty Then[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]Forms!frmItem.Dirty = False[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]End If[/FONT][/SIZE]
 
 [SIZE=3][FONT=Calibri]'Make sure there is a record to duplicate.[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]If Forms!frmItem.NewRecord Then[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]MsgBox "Select the record to duplicate."[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]Else[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]'Duplicate the main record: add to form's clone.[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]With Forms!frmItem.RecordsetClone[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri].AddNew[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]!ItemName = Forms!frmItem.ItemName[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]!ItemDescription = Forms!frmItem.ItemDescription[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]!ItemInvDescription = Forms!frmItem.ItemInvDescription[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]!JobID_FK = iNewID[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]!ItemActive = Forms!frmItem.ItemActive[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]!ItemPrice = Forms!frmItem.ItemPrice[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]'etc for other fields.[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri].Update[/FONT][/SIZE]
 
 [SIZE=3][FONT=Calibri]'Save the primary key value, to use as the foreign key for the related records.[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri].Bookmark = .LastModified[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]lngID = !ItemID[/FONT][/SIZE]
 
 [SIZE=3][FONT=Calibri]'Duplicate the related records: append query.[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]If Forms!frmItem.[fsubItemMaterial].Form.RecordsetClone.RecordCount > 0 Or Forms!frmItem.[fsubItemLabour].Form.RecordsetClone.RecordCount > 0 Then[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]strSQL = "INSERT INTO [tblItemInput] ( ItemID_FK, InputID_FK, InputTypeID_FK, ItemInputQty, ItemInputCost, ItemInputMarkup, ItemInputDescription ) " & _[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]"SELECT " & lngID & " As NewID, InputID_FK, InputTypeID_FK, ItemInputQty, ItemInputCost, ItemInputMarkup, ItemInputDescription " & _[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]"FROM [tblItemInput] WHERE ItemID_FK = " & Forms!frmItem.ItemID & ";"DBEngine(0)(0).Execute strSQL, dbFailOnError[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]Else[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]MsgBox "Main record duplicated, but there were no related records."[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]End If[/FONT][/SIZE]
 
 [SIZE=3][FONT=Calibri]'Display the new duplicate.[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]Forms!frmItem.Bookmark = .LastModified[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]MsgBox "Item Duplicated." & vbCrLf & "You are now viewing the duplicated item", , Nz(DLookup("[ServerName]", "[tblVersionServer]"), "")[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]End With[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]End If[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]End If[/FONT][/SIZE]
 
 [SIZE=3][FONT=Calibri]Exit_Handler:[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]Exit Sub[/FONT][/SIZE]
 
 [SIZE=3][FONT=Calibri]Err_Handler:[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]Resume Exit_Handler[/FONT][/SIZE]
 
 [SIZE=3][FONT=Calibri]'<<<<<<<<Duplication Code[/FONT][/SIZE]
  
 [SIZE=3][FONT=Calibri]End Sub[/FONT][/SIZE]

I have read through the code numerous times and can't seem to see where the issue lies.

Would appreciate any help or suggestions my fellow access programmers may have.

 

[nhorton79]

Does anyone have any ideas?

P.S. Just noticed how terrible my code looks, thought I had indented it correctly but it hasn't shown up. My apologies

 

[GohDiamond]

Your SQL Statement says Insert INTO tblItemInput .... FROM tblItemInput duplicating the data in that table. Is that what you wanted to do?

Cheers
Goh

 

[nhorton79]

Hi GohDiamond. Thanks for your reply.

Yes, I do want to insert into tblItemInput, this table handles the many-to-many relationship between Items and Inputs.

Have just realised that I didn't show that table on my attached ERD.

When a user adds an Input to an Item, tblItemInput keeps a record of the relationship, and any associated changes in cost and markup, specific descriptions etc that are specific to that particular Input on that particular Item.

Hope this makes sense...

Like I mentioned, this code works well when duplicating the Item to another Job, its only when it duplicates to the same job that I have the issue.

Anything else you spotted?

 

[nhorton79]

Hopefully someone out there can help...


Sent from my iPhone using Tapatalk

 

[JHB]

Hopefully someone out there can help...
..

I'm sure there are.
I've some difficult to understand what you're getting and what you want to get instead, so can you post some printscreens what you get and what you want.

 

[nhorton79]

Sorry for the late reply. I'll get some screenshots tomorrow morning and post them.

Thanks for the response


Sent from my iPhone using Tapatalk