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.

 

[brharrii]

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

To get the ID number to increment by +1 from the highest ID number, just use an AutoNumber field type.

 

[brharrii]

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

as far as duplicating the record and leaving certain fields blank, this can be done with VBA, but I'm not sure how to help much beyond that. I did a google search and it appears that there are some good tutorials on it out there. I'd post a link to one of them, but I'm not positive if we are permitted to post links to other forums in this forum.

Good luck!

 

[msp4422]

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

To get the ID number to increment by +1 from the highest ID number, just use an AutoNumber field type.

Sorry I forgot to mention. The ID field is not the primary key and can contain same values. I need to get the Current maximum value in the database + 1.

Thanks for replying

 

[msp4422]

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

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

 

[brharrii]

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

Nice Work!