By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,369 Members | 950 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,369 IT Pros & Developers. It's quick & easy.

Help with duplicate record, with changed field

P: 14
Working in Access 2003.
I have managed to duplicate a record using a button on a form with code:
Private Sub Command16_Click()
On Error GoTo Err_Command16_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Exit_Command16_Click:
Exit Sub

Err_Command16_Click:
MsgBox Err.Description
Resume Exit_Command16_Click

End Sub

But this just creates a duplicate of the fields on the form, which is fine, but i would also like to +1 to one of the fields on the new record.

Is this possible? If so how would i do it.



Also.....
If this is possible,
would it also be possible to have a drop down menu in which to select say (n)1-9 and that is how many duplicates it will make with field +(n).
so the first duplicate will be the same but a field will have +1, and the next +2, etc.
for the selected number of fields
Mar 16 '07 #1
Share this Question
Share on Google+
12 Replies


P: 60
Let's Assume that there is a table tblStuff with fields txtName, txtAddress, and numID. Let's also assume that you wanted to increment numID and you are copying the last record made.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command16_Click()
  2.     On Error GoTo errCommand16Click
  3.  
  4.     Dim strName as String
  5.     Dim strAddress as String
  6.     Dim intIDNo as Integer
  7.     Dim rstStuff as ADODB.Recordset
  8.  
  9.     With rstStuff
  10.         'Open tblStuff to read and write
  11.         .Open "tblStuff", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
  12.         'Moves to the last record, and then copies its values to temporary variables
  13.         .MoveLast
  14.         strName = rstStuff![txtAddress]
  15.         strAddress = rstStuff![txtAddress]
  16.         intIDNo = rstStuff![numID] + 1 'Adds one to the ID number
  17.         'Add a new record to tblStuff and give it the values pulled from the last record in the table
  18.         .AddNew
  19.         ![txtName] = strName
  20.         ![txtAddress] = strAddress
  21.         ![numID] = intIDNo
  22.         .Update
  23.         .Close
  24.     End With
  25.  
  26.     Set rstStuff = Nothing
  27.  
  28.     Exit Sub
  29.  
  30. errCommand16Click:
  31.     MsgBox Err.Description
  32.  
  33. End Sub
  34.  
In order to make several copies, grab the value from the sub-menu and store it in a variable (such as i) and then use For i = 1 to X just before the With rstStuff and End For just after Set rstStuff = Nothing
Mar 16 '07 #2

Denburt
Expert 100+
P: 1,356
After the new duplicate record is created then simply refer to the control in question and add 1

Me!YOURTEXTBOX = Me!YOURTEXTBOX+1



To break it down it would simply look like so:


Private Sub Command16_Click()
On Error GoTo Err_Command16_Click
Dim i as integer, cnt as integer

cnt = Me!COUNTTEXTBOXNAME

For i = 0 to cnt
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Me!YOURTEXTBOX = Me!YOURTEXTBOX+1

next

Exit_Command16_Click:
Exit Sub

Err_Command16_Click:
MsgBox Err.Description
Resume Exit_Command16_Click

End Sub
Mar 16 '07 #3

Denburt
Expert 100+
P: 1,356
As a side note I didnt check to see what you were doing with the menubar commands those commands can be troublesome at times (not function correctly) I would suggest doing this in code it would be much more reliable..


Check out the following article:
http://support.microsoft.com/kb/210236
Mar 16 '07 #4

P: 14
thanks i will give that a try, will that only work for the last record? or will it work for whatever record is currently selected in the form?
Mar 16 '07 #5

P: 14
what i have already duplicates just the name, date index, and target 1, target 2, target 3 fields, and the autonumber already increases as it should, but i want to +1 to the date index field.
Mar 16 '07 #6

Denburt
Expert 100+
P: 1,356
If your adding one day to a date it would look more like:

Me!YOURTEXTBOX = DateAdd("d",1,Me!YOURTEXTBOX)


if you want to copy a particular record not just the last one it just needs a few adjustments.

Remove this:

If Not F.NewRecord Then Exit Function


Adjust the following lines to read:


Set RS = F.RecordsetClone
'This line can be removed RS.MoveLast

rs.Bookmark=Me.Bookmark
docmd.GoToRecord ,,acNewRec

I think that should work for you let me know.
Good Luck
Mar 16 '07 #7

P: 14
its a date index, that refers to a date in another table. just need to +1.
Mar 16 '07 #8

P: 14
one last thing, i think. I've managed to get the form to do what i want using:

Private Sub Command16_Click()
On Error GoTo Err_Command16_Click
Dim i As Integer, cnt As Integer

cnt = Me!Combo17

For i = 0 To cnt
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Me!Text22 = Me!Text22 + 1

Next

Exit_Command16_Click:
Exit Sub

Err_Command16_Click:
MsgBox Err.Description
Resume Exit_Command16_Click

End Sub



Now when the records have been added it automatically takes me to the last record added. Is it possible to add the duplicates and stay with the current record on the form?
Mar 16 '07 #9

Denburt
Expert 100+
P: 1,356
At the beggining of the code mark your location theen at the end return to your original location.

Dim varBk As Integer
varBk = Me.Bookmark






Me.Bookmark = varBk

That should work.
Mar 16 '07 #10

P: 14
ive tried that, but i get a "type mismatch" error now
Mar 16 '07 #11

P: 14
Ok, well i got it all sorted now. using this code:
Private Sub Command16_Click()
On Error GoTo Err_Command16_Click
Dim i As Integer, cnt As Integer, varBk As String

varBk = Me.Bookmark
cnt = Me!Combo17

For i = 0 To cnt - 1
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Me!Text22 = Me!Text22 + 1



Next

Exit_Command16_Click:
Me.Bookmark = varBk
Exit Sub

Err_Command16_Click:
MsgBox Err.Description


Resume Exit_Command16_Click


End Sub




But......

Is it also possible to stop this solution making a duplicate duplicate.

What i mean is; this solution creates a duplicate of fields x, y, z and w+1.

So this is not creating exact duplicates, as w value has changed for each record.

Now if you hit this button, or run this, twice, there will be REAL duplicates, as there will be 2 copies of x ,y, z and w+1.

So..

Is it possible to add code that will replace the old duplicate.
Also because z is a variable and i may want this changed on the duplicates, if they already exist i can't just block the new duplicate being sent.
Mar 17 '07 #12

Denburt
Expert 100+
P: 1,356
You can use something like the following to prevent duplication of data or change it up to suite your needs.

rs= me.recordsetclone
if rs.findfirst (PutWhereConditionHere) Then
rs.close
set rs = nothing
exit sub
end if
Mar 19 '07 #13

Post your reply

Sign in to post your reply or Sign up for a free account.