Help with duplicate record, with changed field

[capdownlondon]

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

[fauxanadu]

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.

Private Sub Command16_Click()
    On Error GoTo errCommand16Click
 
    Dim strName as String
    Dim strAddress as String
    Dim intIDNo as Integer
    Dim rstStuff as ADODB.Recordset
 
    With rstStuff
        'Open tblStuff to read and write
        .Open "tblStuff", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
        'Moves to the last record, and then copies its values to temporary variables
        .MoveLast
        strName = rstStuff![txtAddress]
        strAddress = rstStuff![txtAddress]
        intIDNo = rstStuff![numID] + 1 'Adds one to the ID number
        'Add a new record to tblStuff and give it the values pulled from the last record in the table
        .AddNew
        ![txtName] = strName
        ![txtAddress] = strAddress
        ![numID] = intIDNo
        .Update
        .Close
    End With
 
    Set rstStuff = Nothing
 
    Exit Sub
 
errCommand16Click:
    MsgBox Err.Description
 
End Sub
 

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

[Denburt]

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

[Denburt]

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

[capdownlondon]

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?

[capdownlondon]

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.

[Denburt]

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

[capdownlondon]

its a date index, that refers to a date in another table. just need to +1.

[capdownlondon]

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?

[Denburt]

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.

[capdownlondon]

ive tried that, but i get a "type mismatch" error now

[capdownlondon]

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.

[Denburt]

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