How to reset AutoIncrement field from Access Database -

i trying reset autoincrement field of particular field. reason because gets count auto populates in case number. when hits mindnight 12/31/2013 11:59:00 pm want autoincrement start again zero.

this code have. code on form1 , value of id2 form2 keep case number consistant when hit @ field1 using double click

private sub field1_dblclick(cancel integer)     docmd.openform "form2",,,,acformadd,achidden     me.field1 = format(date, "yy") & "-" & format(forms!form2!id2, "0000")     me.field2.setfocus end sub 

maybe has different approach. counting upwards. problem have when reach "12/31/13 11:59:00 pm" want autoincrement reset 1 start brand new case number. format provided. right know works fine format see yy-0001 in order words 13-0001 case number example. have following code think should work reseting autoincrement. has better approach this. code.

private sub field1_dblclick(cancel integer)     if me.text5 > "12:/31/2013 11:59:00 pm"         docmd. deleteobject actable, "table2"         docmd.runsql "create table table2 ([id2] autoincrement, [field1] text (255))"     end if 

the problem have how manage changing happens if dont press number how force change. have text5 on timer timer intervals every 1000 milliseconds 1 second code runs timer is

private sub form_timer()     me.text5 = now() end sub 

i have 2 problems happends if never need press how validate can delete table , create new table once works autoincrement refresh problem how force it on own.

try this:

currentdb.execute "alter table yourtablenamehere alter column fieldnamehere counter(1,1)"