Copying the Current Record into a new Record (with designated new primary key)

[Instructor Support]

I've read a few things about how to copy records and mostly what I see is that copying doesn't work cause of the primary key being unable to be duplicated. However, I found this thread;
https://www.accessforums.net/access/...ord-15269.html

Where the individual managed to get a copy working using this example;
http://www.allenbrowne.com/ser-57.html

I'm getting the same error she got at the end where it says "Item not found in this collection". Please note that I am trying to copy the current record in my form.

So I think the important bit about my database is that my primary key is not automatic, it's a user designated number. The number is basically a series of numbers representing the date in a field called cboStartDate (for example: 240823 where 24 is the Japanese year, 08 is the month, and 23 is the day) and then two digit iterations up for how many records start on that date (example: 24082301, 24082302, 24082303.) Optimally, I would like to be specifying the number via a message box at the time of copy if automating that part isn't easy. Is this workable?

Here is the code I am using;

Private Sub CopyJob_Click()
'On Error GoTo Err_Handler
    'Purpose:   Duplicate the current form record
   
    'Save any edits first
    If Me.Dirty Then
        Me.Dirty = False
    End If
    
    'Make sure there is a record to duplicate.
    If Me.NewRecord Then
        MsgBox "Select the record to duplicate."
    Else
        'Duplicate the main record: add to form's clone.
        With Me.RecordsetClone
            .AddNew
 'This is my record's primary key. This part doesn't snag.
                !JobOrderID = Me.JobOrderID.Value + 1
'This is an alphanumeric field on my form and where the code stops working.
                !Combo351 = Me.Combo351
                !SubName = Me.SubName
                !JobOrderDate = Date
                !cboStartDate = Me.cboStartDate
                !Combo312 = Me.Combo312
                !Check509 = Me.Check509
                !Check529 = Me.Check529
                !cboEndDate = Me.cboEndDate
                !InstructorName = Me.InstructorName
                !Eva1Due = Me.Eva1Due
                !Eva2Due = Me.Eva2Due
                !Eva3Due = Me.Eva3Due
                !EvaluationNotes = Me.EvaluationNotes
                !IntrRate = Me.IntrRate
                !MonStart1 = Me.MonStart1
                !MonFinish1 = Me.MonFinish1
                !TueStart1 = Me.TueStart1
                !TueFinish1 = Me.TueFinish1
                !WedStart1 = Me.WedStart1
                !WedFinish1 = Me.WedFinish1
                !ThuStart1 = Me.ThuStart1
                !ThuFinish1 = Me.ThuFinish1
                !FriStart1 = Me.FriStart1
                !FriFinish1 = Me.FriFinish1
                !SatStart1 = Me.SatStart1
                !SatFinish1 = Me.SatFinish1
                !SunStart1 = Me.SunStart1
                !SunFinish1 = Me.SunFinish1
                'etc for other fields.
            .Update
            
            'Save the primary key value, to use as the foreign key for the related records.
            .Bookmark = .LastModified

            'Display the new duplicate.
            Me.Bookmark = .LastModified
        End With
    End If

Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
    Resume Exit_Handler
       
End Sub

I do not have any subforms on this form, so I removed the append query. I hope that didn't mess something up.

I'm grateful for any help!

[pbaldy]

In this line:


!Combo351 = Me.Combo351

the first part would have to be the name of the field in the data. What you have is presumably the name of a control on the form (which is okay for the second part). Thus I'd expect something like:


!FieldName = Me.Combo351

[Instructor Support]

That was it! Thank you for helping me to identify my silly mistake!

It works perfectly now.

[pbaldy]

Happy to help!

[Instructor Support]

Is it inappropriate to ask a question about the 2nd half of my goal?

With regard to the Order ID, I have created a user prompt to specify the first 6 digits, but I'd like to be able to ask the database to assign the last two digits. So if I put in 240822 then the database would look to see if 24082201 exists. If not- create. If it already exists, increment up to 24082202 and repeat. Alternately find the max of the 2 digit serial with those 6 digits matching and add one to make it a one step instead of a loop. I dunno how to do either, or which approach would be best.

I can start a new question thread if it is inappropriate to continue this one. Please advise.

~~~~~~~~~~
Oops, I figured it out.

I found a VBA command called dmax and I'm just telling it to use the criteria of the left 6 digits of my table field as matching the user provided number and then adding + 1 to the result. It seems to be working exactly as I need it to.

[pbaldy]

Sorry, I missed the follow-up question. You used the method I would have recommended.