Question Is there a way to prevent the resetting of auto number indexes on compact and repair (1 Viewer)

[goJimH]

In month-end posting - I basically delete all the records from my transaction file. And in doing so - it seems to be resetting the auto number index - which is causing me major problems.

This forum has been very helpful - and to my reading - the resetting of the index after deleting all the records is normal.

It would be very helpful is this can be altered - or is some other workaround.

P.S. I am working in VB 2010 - using a 2003 MS Access database.

Thank you for any and all assistance. It is greatly appreciated!

Jim H:banghead::banghead:

 

[John Big Booty]

Rather than use the native AutoNumber function you could create your own numbering function using the DMax() function plus one. (search the forum and you should find plenty of discussion and samples on the subject). Then at the end of the month you could store your last transaction number and use that to seed your custom AutoNumber for the coming month.

 

[goJimH]

John Big Booty,

Thanks for your quick and helpful reply. The Monthly Seed for the AutoNumber is likely what I will try.

I appreciate it.

Jim H

 

[nanscombe]

I used to use a dedicated counter table but, to be honest, my counters were a bit awkward because each of our buildings had their own range of numbers. Here is an example of the sort of thing I would use.

A table called tblCounters. Two fields; cName (String) - counter name, cValue (Long) - counter value.

A function nextIdGet().

Public Function nextIdGet(Byval counterName as String)
  Dim strSql as String, rsTemp as Recordset

  Set rsTemp = CurrentDb.OpenRecordset("tblCounters", dbOpenDynaset)

  With rsTemp

    StrSql = "LCase(cName) = '" & LCase(counterName) & "'"
    .FindFirst strSql

    If .NoMatch Then
      .AddNew
        !cName = LCase(counterName)
        !cValue = 1
      .Update

      .FindFirst strSql
    End If

    nextIdGet = !cValue
    .Edit
      !cValue = nextIdGet + 1
    .Update

  End With

  rsTemp.Close
  Set rsTemp = Nothing

End Function

Then in the BeforeUpdate event of the form.

Private Sub Form_BeforeUpdate(Cancel as Integer)
  If Val(Me!ID & "") = 0 Then Me!ID = nextIdGet("theCounterName")
End Sub

 

[goJimH]

Nigel, I thank you greatly. And will keep this in mind as a resource.

As to my problem - under dead line - the thought of having to back track and rework the tables was daunting. As I thought about it - realized I was doing things backwards.

I had two tables - one a monthly file - the other contained all the monthly plus rolling 11 months of history. I was adding to the monthly first - then using that counter assigned value in the history. Because the monthly was set to zero records I had the problem on compact and repair. I reversed it - taking the controlling key from the history file - and all seems to work fine now. The history file is a rolling 12 months so It should not reset like the monthly.

That said - I do appreciate your help and the other response(s). And as I work more with Access - I am sure this site will be of great help.

Jim H