How to reset AutoIncrement field from Access Database

Question

I am trying to reset the Autoincrement field of a particular field. The reason because it gets a count that auto populates in a case number. But when it hits mindnight 12/31/2013 11:59:00 PM I want to autoincrement to start again from zero.

This is some code I have. This code is on Form1 and I get the value of the ID2 from Form2 to keep the case number consistant only when they hit at Field1 by using the Double Click

Private Sub Field1_DblClick(Cancel As Integer)
    Docmd.OpenForm "Form2",,,,acFormAdd,acHidden
    Me.Field1 = Format(Date, "YY") & "-" & Format(Forms!Form2!ID2, "0000")
    Me.Field2.SetFocus
End Sub

Maybe someone has a different approach. For counting upwards. Now my problem I have is that when I reach "12/31/13 11:59:00 PM" I want that autoincrement to reset itself back to 1 to start a brand new case number. with the format provided. Right know it works fine the format they see is YY-0001 in order words 13-0001 case number example. I have the following code that I think It should work for reseting the autoincrement. probably someone has a better approach then this. My code.

Private Sub Field1_DblClick(Cancel As Integer)
    If Me.Text5 > "12:/31/2013 11:59:00 PM" Then
        DoCmd. DeleteObject acTable, "Table2"
        DoCmd.RunSQL "CREATE TABLE Table2 ([ID2] AUTOINCREMENT, [Field1] text (255))"
    end if

The problem I have how do I manage from changing what happens if they dont press the number how do I force it to change. I have the text5 on a timer timer intervals every 1000 milliseconds which is one second the code that runs for the timer is

Private Sub Form_Timer()
    Me.Text5 = Now()
End Sub

I have two problems what happends if they never need to press how do I validate so it can delete the table and create a new table once it works the autoincrement will refresh the only problem is that how do i force it that it will do it on its own.

I tried and it deletes the table and adds a new one and it gives me a brand new record I kind of taught outside the box' — NOE2270667, Aug 19 '13 at 16:39
But I want a better suggestion from someone if they have a better code. — NOE2270667, Aug 19 '13 at 16:39

score 4 · Accepted Answer · answered Aug 19 '13 at 20:19

4

Try this:

CurrentDb.Execute "ALTER TABLE YourTableNameHere ALTER COLUMN FieldNameHere COUNTER(1,1)"

answered Aug 19 '13 at 20:19

Johnny Bones

8,786
7
52
117

Hey Johny but I want the column to reset when the new year comes. How could I do that. – NOE2270667 Aug 20 '13 at 15:45
You'd have to run it manually when the new year comes. Or put in the Open Form event to check the date, and if it's a new year then run the code. – Johnny Bones Aug 20 '13 at 16:51
For those who come here from Google there is good discussion at - http://stackoverflow.com/questions/4416619 – Adarsh Madrecha Feb 24 '16 at 18:16

score 1 · Answer 2 · edited Feb 24 '16 at 19:28

1

You can also do this:

Remove the Primary Key from the field
Delete the field
Make a new field exactly as the one you deleted
It's done

More details: https://www.youtube.com/watch?v=1kni5kFzlPk Regards.

edited Feb 24 '16 at 19:28

Johnny Bones

8,786
7
52
117

answered Nov 19 '15 at 10:12

FerPessoa

63
1
9

How to reset AutoIncrement field from Access Database

2 Answers2

Linked