Duplicate current record & Increment a non Primary ID column to CurrMax+1 value

[msp4422]

Hi



I would like to know if we have a simple method to duplicate the current record in a form and Increment a ID value to Current Maximum Value + 1 , and to clear few boxes so that user can enter new data in this form.

I used Duplicate button but it copies entire data and I am unable to get a way to Update ID value to Max+1 and few date fields to NULL automatically.

Could anyone suggest on this??

Many Thanks.

[msp4422]

Private Sub Command106_Click()
On Error GoTo Err_Command106_Click


DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste


Me.[OII Receipt Date] = Null
Me.[Initial Execution Date] = Null


.
.
.
.


Thanks everyone for their time
Have a cheerful day