Creating a duplicate record using VBA
-
- 2StarLounger
- Posts: 115
- Joined: 07 Feb 2017, 13:31
Creating a duplicate record using VBA
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.
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.
-
- Administrator
- Posts: 78230
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Creating a duplicate record using VBA
What are the Link Master Fields and Link Child Fields properties of sfrmFinding as a subform on frmASRTool?
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 115
- Joined: 07 Feb 2017, 13:31
Re: Creating a duplicate record using VBA
Both are FileNum
-
- Administrator
- Posts: 78230
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Creating a duplicate record using VBA
Thanks. Is that an AutoNumber field in tblVehRec?
If not, how should we assign a FileNum to the new record?
If not, how should we assign a FileNum to the new record?
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 115
- Joined: 07 Feb 2017, 13:31
Re: Creating a duplicate record using VBA
Yes this is an auto number field
-
- Administrator
- Posts: 78230
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- Administrator
- Posts: 78230
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Creating a duplicate record using VBA
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
Hans
-
- 2StarLounger
- Posts: 115
- Joined: 07 Feb 2017, 13:31
Re: Creating a duplicate record using VBA
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:
Thank you again!
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
-
- Administrator
- Posts: 78230
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Creating a duplicate record using VBA
If the record in the main form doesn't have a FileNum value, there is nothing to copy...
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 115
- Joined: 07 Feb 2017, 13:31
Re: Creating a duplicate record using VBA
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.
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.
-
- Administrator
- Posts: 78230
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Creating a duplicate record using VBA
I'd have to see (a stripped-down copy of) the database, otherwise it's impossible for me to help you.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 115
- Joined: 07 Feb 2017, 13:31
Re: Creating a duplicate record using VBA
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!
I didn't want to strip much as not to change the way it works.
Thank you so much!
-
- Administrator
- Posts: 78230
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Creating a duplicate record using VBA
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
Hans
-
- 2StarLounger
- Posts: 115
- Joined: 07 Feb 2017, 13:31
Re: Creating a duplicate record using VBA
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
I would like the button to copy fields that would not change (the list I updated from your last code should b all
-
- Administrator
- Posts: 78230
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Creating a duplicate record using VBA
Ah, I think I understand what you want now. I'll work on it.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78230
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Creating a duplicate record using VBA
Try this version. I may have included too many fields.
Please test thoroughly.
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
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 115
- Joined: 07 Feb 2017, 13:31
Re: Creating a duplicate record using VBA
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?
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?
-
- Administrator
- Posts: 78230
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Creating a duplicate record using VBA
You can simply duplicate the code, and delete the part
for the button that doesn't copy the findings records.
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
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 115
- Joined: 07 Feb 2017, 13:31
Re: Creating a duplicate record using VBA
Thank you very much all works nicely!
-
- 2StarLounger
- Posts: 115
- Joined: 07 Feb 2017, 13:31
Re: Creating a duplicate record using VBA
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.
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.