Resetting the counter on AutoNumber

[Guest]

Hi there,

I was just wondering if it was possible to reset the counter on a control
with an AutoNumber?

The reason I ask is I use a formula to combine the date and AutoNumber in a
record to create a unique field in my table. The reason I do this is so that
I can have a running sum in my form.

=DSum("[Amount]","tblDetails","[Unique Field] <=" & Nz([Unique Field],0) &
"And [AccountID] =" &
Nz([Forms]![frmDetails]![frmDetailsSubform].Form!Combo4,0))

I was just currious if it was possible to reset the counter every Jan 1st
back to 1 so that my numbers don't get astronomically large.

The unique field would look something like this for 2 transactions that
occured on Oct 20th: (formula = date * 100000 + AutoNumber)

2004102000011
2004102000012

This is fine until I hit 100,000 records then I'll start to over write the
date. I could multiply it by a huge number but I thought I would see if
there was a way to reset the counter first before I went that route.

Hopefully this makes sense to you!!

Thanks!
Todd

 

[Greg]

The only way is to create a new table with the same fields, then copy the
data from the old table to the new table. Delete the old table then rename
the new table to the old name.

This can be all done through code using tabledefs and querydefs.

HTH's
Greg

 

[Guest]

Thanks Greg!

I had a feeling that was the answer, but just wasn't sure.

Thanks for the feedback!

Todd

The only way is to create a new table with the same fields, then copy the
data from the old table to the new table. Delete the old table then rename
the new table to the old name.

This can be all done through code using tabledefs and querydefs.

HTH's
Greg

Hi there,

I was just wondering if it was possible to reset the counter on a control
with an AutoNumber?

The reason I ask is I use a formula to combine the date and AutoNumber in a
record to create a unique field in my table. The reason I do this is so that
I can have a running sum in my form.

=DSum("[Amount]","tblDetails","[Unique Field] <=" & Nz([Unique Field],0) &
"And [AccountID] =" &
Nz([Forms]![frmDetails]![frmDetailsSubform].Form!Combo4,0))

I was just currious if it was possible to reset the counter every Jan 1st
back to 1 so that my numbers don't get astronomically large.

The unique field would look something like this for 2 transactions that
occured on Oct 20th: (formula = date * 100000 + AutoNumber)

2004102000011
2004102000012

This is fine until I hit 100,000 records then I'll start to over write the
date. I could multiply it by a huge number but I thought I would see if
there was a way to reset the counter first before I went that route.

Hopefully this makes sense to you!!

Thanks!
Todd