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_C lick


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

Exit_Command16_ Click:
Exit Sub

Err_Command16_C lick:
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_C lick
Dim i as integer, cnt as integer

cnt = Me!COUNTTEXTBOX NAME

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

Me!YOURTEXTBOX = Me!YOURTEXTBOX+ 1

next

Exit_Command16_ Click:
Exit Sub

Err_Command16_C lick:
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,M e!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.RecordsetClon e
'This line can be removed RS.MoveLast

rs.Bookmark=Me. Bookmark
docmd.GoToRecor d ,,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_C lick
Dim i As Integer, cnt As Integer

cnt = Me!Combo17

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

Me!Text22 = Me!Text22 + 1

Next

Exit_Command16_ Click:
Exit Sub

Err_Command16_C lick:
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.