Creating a duplicate record using VBA

duke_44
2StarLounger
Posts: 115
Joined: 07 Feb 2017, 13:31

Creating a duplicate record using VBA

Post by duke_44 »

Hello, thank you in advance for any help provided.

I am looking to have a button create a new editable record with some/most fields duplicated from a previous record.

How my form(s) are set up:

frmASRTool is the main form - this is where most data is collected and stored in tblVehRec - this is where the majority of the duplicated fields will be sourced from.

sfrmFinding is part of frmASRTool it captures specific data stored in tblFinding. This form has also check button that indicate a yes/no type response. This form would also have information to copy (but only items indicated as NO - the YES items are considered resolved and not required to copy)

sfrmASR is also part of frmASRTool - this sfrm is used as means to search for previous records. I have given my user has a few options as search criteria. I have the record selectors on so when a record is selected they currently have the ability to display (or go to) the searched record by means of a button (btnViewInsp). This is where instead of displaying a previous record I would like to have a "Copy" type button to create a new duplicated record with certain fields copied and others left uncopied.

I hope I have explained myself clear enough.

User avatar
HansV
Administrator
Posts: 78230
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Creating a duplicate record using VBA

Post by HansV »

What are the Link Master Fields and Link Child Fields properties of sfrmFinding as a subform on frmASRTool?
Best wishes,
Hans

duke_44
2StarLounger
Posts: 115
Joined: 07 Feb 2017, 13:31

Re: Creating a duplicate record using VBA

Post by duke_44 »

Both are FileNum

User avatar
HansV
Administrator
Posts: 78230
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Creating a duplicate record using VBA

Post by HansV »

Thanks. Is that an AutoNumber field in tblVehRec?
If not, how should we assign a FileNum to the new record?
Best wishes,
Hans

duke_44
2StarLounger
Posts: 115
Joined: 07 Feb 2017, 13:31

Re: Creating a duplicate record using VBA

Post by duke_44 »

Yes this is an auto number field

User avatar
HansV
Administrator
Posts: 78230
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Creating a duplicate record using VBA

Post by HansV »

I will look into it later.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78230
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Creating a duplicate record using VBA

Post by HansV »

The following is "air code" since I haven't seen your database. You will have to modify it before using it.

Code: Select all

Private Sub cmdDuplicate_Click()
    Dim strSQL As String
    Dim lngOldFileNum As Long
    Dim lngNewFileNum As Long
    ' Check if we have a FileNum
    If IsNull(Me.FileNum) Then
        MsgBox "Please create a record, then try again!", vbExclamation
        Exit Sub
    End If
    ' Save the record, if necessary
    Me.Dirty = False
    ' Get the current FileNum
    lngOldFileNum = Me.FileNum
    ' Copy the record
    RunCommand acCmdSelectRecord
    RunCommand acCmdCopy
    RunCommand acCmdPasteAppend
    ' If you wish, clear some fields/controls
    Me.ThisField = Null
    Me.ThatField = Null
    ' Get the new FileNum
    lngNewFileNum = Me.FileNum
    ' Copy the records form the subform
    strSQL = "INSERT INTO tblFinding (FileNum, OtherField, OneMoreField) " & _
        "SELECT " & lngNewFileNum & ", OtherField, OneMoreField " & _
        "FROM tblFinding WHERE FileNum=" & lngOldFileNum & _
        " AND YesNoField=True"
    CurrentDb.Execute strSQL, dbFailOnError
    ' Update the subform
    Me.sfrmFinding.Requery
End Sub
Best wishes,
Hans

duke_44
2StarLounger
Posts: 115
Joined: 07 Feb 2017, 13:31

Re: Creating a duplicate record using VBA

Post by duke_44 »

Thank you very much. I have updated the code to be specific to my DB. I select the record (using the record selector from sfrmASR) - which I notice hasn't a Link Master or Link Child (possibly an issue). All that happens when I click the button to "Copy" the MSGBox comes up as coded but nothing else happens. I have checked the appropriate tables and no new records have been added.

Here is the code modified:

Code: Select all

    Dim strSQL As String
    Dim lngOldFileNum As Long
    Dim lngNewFileNum As Long
    ' Check if we have a FileNum
    If IsNull(Me.FileNum) Then
        MsgBox "Please select a record, then try again!", vbExclamation
        Exit Sub
    End If
    ' Save the record, if necessary
    Me.Dirty = False
    ' Get the current FileNum
    lngOldFileNum = Me.FileNum
    ' Copy the record
    RunCommand acCmdSelectRecord
    RunCommand acCmdCopy
    RunCommand acCmdPasteAppend
    ' If you wish, clear some fields/controls
    Me.InspDate = Null
    Me.InspType = Null
    Me.MTO = Null
   Me.Odometer = Null
Me.Hours = Null
Me.CotNum = Null
Me.Mnt1 = Null
Me.Cot1 = null
Me.Mnt2 = null
Me.Cot2 = null
Me.PM1D = null
Me.PM1O = null
Me.PM1H = Null
Me.PM2D = null
Me.PM2O = null
Me.PM2H = null
Me.PM3D = null
Me.PM3O = Null
Me.PM3H = Null
Me.chkAnnexE = Null
Me.txtDateAnE = Null
Me.txtVerAnE = Null
Me.chk204 = Null
Me.chk2016 = null
Me.chk2019 = null
Me.chk2022 = null
Me.chko2 = null
Me.chkfire = null
    ' Get the new FileNum
    lngNewFileNum = Me.FileNum
    ' Copy the records form the subform
    strSQL = "INSERT INTO tblFinding (FileNum, FindID, Finding, Resolved) " & _
        "SELECT " & lngNewFileNum & ", FindID, Finding, Resolved " & _
        "FROM tblFinding WHERE FileNum=" & lngOldFileNum & _
        " AND YesNoField=True"
    CurrentDb.Execute strSQL, dbFailOnError
    ' Update the subform
    Me.sfrmFinding.Requery
End Sub
Thank you again!

User avatar
HansV
Administrator
Posts: 78230
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Creating a duplicate record using VBA

Post by HansV »

If the record in the main form doesn't have a FileNum value, there is nothing to copy...
Best wishes,
Hans

duke_44
2StarLounger
Posts: 115
Joined: 07 Feb 2017, 13:31

Re: Creating a duplicate record using VBA

Post by duke_44 »

That makes sense sorry.

Is it possible to allow my user to use this function by having the record selector in sfrmASR selected? Is it also difficult to have sfrmASR filter/update based on another field in frmASRTool (the field is txtVIN) as an after update function?

Currently I receive a couple of messages/errors/observations
1. The system states that some of my field names do not match. I have gone through, made sure all the required fields copy over fine (which they do as expected). I have also gone through my table and removed any redundant field names.
2. I receive runtime error 3061 - the de-bugger highlights CurrentDb.Execute strSQL, dbFailOnError as the line of concern.
3. Some of the newly created record doesn't save (specifically information in sfrmFinding doesn't remain when going away from and back to this new record)

Your help is greatly appreciated.

User avatar
HansV
Administrator
Posts: 78230
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Creating a duplicate record using VBA

Post by HansV »

I'd have to see (a stripped-down copy of) the database, otherwise it's impossible for me to help you.
Best wishes,
Hans

duke_44
2StarLounger
Posts: 115
Joined: 07 Feb 2017, 13:31

Re: Creating a duplicate record using VBA

Post by duke_44 »

https://www.dropbox.com/s/lb6lwvyalf1oq ... accdb?dl=0" onclick="window.open(this.href);return false;

I didn't want to strip much as not to change the way it works.

Thank you so much!

User avatar
HansV
Administrator
Posts: 78230
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Creating a duplicate record using VBA

Post by HansV »

Thanks. Now that I see the database, it is not clear to me what exactly you want to copy when the user clicks the 'Copy and Create New' button on the main form, since sfrmASR is independent of (not linked to) the main form.
Best wishes,
Hans

duke_44
2StarLounger
Posts: 115
Joined: 07 Feb 2017, 13:31

Re: Creating a duplicate record using VBA

Post by duke_44 »

Sorry for the confusion. If I had may preference I would like my user to be able to filter (or even better it auto filter after an input in lets say VIN field) sfrmASR then using the record selectors copy and save a new record (including unchecked items in sfrmFinding).
I would like the button to copy fields that would not change (the list I updated from your last code should b all

User avatar
HansV
Administrator
Posts: 78230
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Creating a duplicate record using VBA

Post by HansV »

Ah, I think I understand what you want now. I'll work on it.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78230
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Creating a duplicate record using VBA

Post by HansV »

Try this version. I may have included too many fields.

Code: Select all

Private Sub btnCopyRec_Click()
    Dim strSQL As String
    Dim lngOldFileNum As Long
    Dim lngNewFileNum As Long
    ' Check if we have a FileNum
    If IsNull(Me.sfrmASR!FileNum) Then
        MsgBox "Please select a record, then try again!", vbExclamation
        Exit Sub
    End If
    ' Save the record, if necessary
    Me.Dirty = False
    ' Get the current FileNum
    lngOldFileNum = Me.sfrmASR!FileNum
    ' Copy the record
    strSQL = "INSERT INTO tblVehRec (ServID, ServName, CACC, VIN, License, Vehicle, VehType, ModYear, Make, Model, " & _
        "FuelType, GVWR, Length, CMVSS, ESA, ConvMan, ConvProdNum, CertNum, Version, chkLabel, " & _
        "FindLeft, PM3H1, PMPD1, PMPO1, PMPH1, chkVeh1, chkVeh2, chkVeh3, chkVeh4, chkVeh5, chkVeh6, " & _
        "chkVeh7, chkVeh8, chkVeh9, chkFile1, chkFile2, chkFile3, chkFile4, chkFile5, chkFile6, chkFile7, " & _
        "chkFile8, chkFile9, chkAdd1, chkAdd2, chkAnnexE1, txtDateAnE1, txtVerAnE1, chk2041, chk20161, " & _
        "chk20191, chk20221, chko21, chkfire1, chkLabel1, FindLeft1, BaseID, [Note]) " & _
        "SELECT ServID, ServName, CACC, VIN, License, Vehicle, VehType, ModYear, Make, Model, " & _
        "FuelType, GVWR, Length, CMVSS, ESA, ConvMan, ConvProdNum, CertNum, Version, chkLabel, " & _
        "FindLeft, PM3H1, PMPD1, PMPO1, PMPH1, chkVeh1, chkVeh2, chkVeh3, chkVeh4, chkVeh5, chkVeh6, " & _
        "chkVeh7, chkVeh8, chkVeh9, chkFile1, chkFile2, chkFile3, chkFile4, chkFile5, chkFile6, chkFile7, " & _
        "chkFile8, chkFile9, chkAdd1, chkAdd2, chkAnnexE1, txtDateAnE1, txtVerAnE1, chk2041, chk20161, " & _
        "chk20191, chk20221, chko21, chkfire1, chkLabel1, FindLeft1, BaseID, [Note] " & _
        "FROM tblVehRec WHERE FileNum=" & lngOldFileNum
    CurrentDb.Execute strSQL, dbFailOnError
    ' Get the new FileNum
    lngNewFileNum = DMax("FileNum", "tblVehRec")
    ' Copy the records form the subform
    strSQL = "INSERT INTO tblFinding (FileNum, FindID, Finding, Resolved) " & _
        "SELECT " & lngNewFileNum & ", FindID, Finding, Resolved " & _
        "FROM tblFinding WHERE FileNum=" & lngOldFileNum & _
        " AND Resolved=False"
    CurrentDb.Execute strSQL, dbFailOnError
    ' Update the form
    Me.Requery
    ' Go to the new record
    RunCommand acCmdRecordsGoToLast
End Sub
Please test thoroughly.
Best wishes,
Hans

duke_44
2StarLounger
Posts: 115
Joined: 07 Feb 2017, 13:31

Re: Creating a duplicate record using VBA

Post by duke_44 »

Amazing! So far so good, working like a charm but will test more!

There may be circumstances that the results in sfrmFinding won't be required to be copied (eg new inspection). Is it easy enough to create a button to delete the findings? Or better to have a 2 buttons to 1 that doesn't copy the findings and other that copies both?

User avatar
HansV
Administrator
Posts: 78230
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Creating a duplicate record using VBA

Post by HansV »

You can simply duplicate the code, and delete the part

Code: Select all

    ' Get the new FileNum
    lngNewFileNum = DMax("FileNum", "tblVehRec")
    ' Copy the records form the subform
    strSQL = "INSERT INTO tblFinding (FileNum, FindID, Finding, Resolved) " & _
        "SELECT " & lngNewFileNum & ", FindID, Finding, Resolved " & _
        "FROM tblFinding WHERE FileNum=" & lngOldFileNum & _
        " AND Resolved=False"
    CurrentDb.Execute strSQL, dbFailOnError
for the button that doesn't copy the findings records.
Best wishes,
Hans

duke_44
2StarLounger
Posts: 115
Joined: 07 Feb 2017, 13:31

Re: Creating a duplicate record using VBA

Post by duke_44 »

Thank you very much all works nicely!

duke_44
2StarLounger
Posts: 115
Joined: 07 Feb 2017, 13:31

Re: Creating a duplicate record using VBA

Post by duke_44 »

Hello and thank you again!

Can I have the records copied over from tblFinding (only this set of data) formatted so their font is RED? This would help my user understand this was copied over from a previous record.

Thanks again.