Resetting the counter on AutoNumber

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
Thanks Greg!

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

Thanks for the feedback!

Todd

Greg said:
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

TT said:
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
Click to expand...
Click to expand...
 
Back
Top