How to resequence an Autonumber

[jsnunez]

Hi all

I have a table with an ID, that is an autonumber, that starts at 1967, I would like it to start from 1.

How do I resequence an autonumber field ?

thanks
jsn

 

[ZmrAbdulla]

Just clear the table and compact database
for more ref: on AutoNumber

[ul]
[li] Use of Autonumber faq702-5106 [/li]
[li] How can I auto-prefix my autonumber field? faq181-1725 [/li]
[li] Create a Autonumber with a Suffix faq181-1179[/li]
[li] How to autonumber date values in a control. faq181-1129 [/li]
[li] What can I use besides AutoNumber faq181-1023 [/li]
[li] Make an intelligent autonumber field? faq181-159 [/li]
[li] can I make a dynamic autonumber? faq181-158 [/li]
[/ul]

Reset Autonumber Field thread702-1050735


________________________________________
Zameer Abdulla
Visit Me
Minds are like parachutes. They only function when they are open. -Sir James Dewar (1877-1925)

 

[jsnunez]

Hi

You said: "If you need a meaningful sequencer (like to assign user numbers), you have to create it and manipulate it yourself."

How do I create this "meaningful sequence"?

Can you give me a specific example of for example an Invoice table wherre the InvoiceNumber increases by steps of 1, and the field is not declared as autoformat, so I assing the invoice numbers somewhere in the form.

thanks
jsn

 

[formerTexan]

Hi,

A quick and easy method of number incrementing is to look up the currently used largest invoice number, add 1 and then pass it for the new invoice.

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim lngMaxInvNum As Long
Dim lngNewInvNum As Long
Set db = CurrentDb
strSQL = "SELECT Max(tblYourInvTable.InvNum) AS MaxOfInvNum FROM tblYourInvTable;"
Set rs = db.OpenRecordset(strSQL)
lngMaxInvNum = rs!MaxofInvNum
lngNewInvNum = lngMaxInvNum + 1
Me.txtInvNum = lngNewInvNum
End Sub

However this number will not guarantee invoice number uniqueness.

If you have an invoice number sequence such as:

47
48
49

And 49 is deleted; the preceeding function will recreate number 49 the next time an invoice is added. It would become quite possible to then print out two different invoices with the same invoice number. If this is going to be a problem, come back and we can get into gory details on bulletproofing number sequencing.

Cheers,
Bill

 

[jsnunez]

Hi

The "Before Insert" event fires when you start typing in one of the fields in the form, at this moment the Invoice Number field is pupulated in the form with the new value.

Questions:
- What happens if two or more people are doing data entry at the same time?
- Are there any possibility of reserving the same invoice number, so when they save the record they will be a duplicate invoice number?
- What do you suggest to avoid "to print out two different invoices with the same invoice number" ?
- how efficient and effective is the Identity field in Ms SQL?
- is it better to use the code in the "before insert" event or in the trigger?

Thanks
jsn




Thanks

 

[earthandfire]

If you don't want to use an autonumber, create a table with a field - NextNumber. When you need to allocate a new invoice number, open the table EXCLUSIVELY pick up the number, increment it and write it back and then close the table. This guarantees uniqueness and works perfectly in a multi-user environment.

Hope this helps.

 

[earthandfire]

As an example:

Public Function GetNewNumber(TableName As String, FieldName As String, ACount As Long) As Long
Dim rs As Recordset
Dim n As Long

  On Error GoTo TableInUse
  Set rs = CurrentDb.OpenRecordset(TableName, , dbPessimistic)
  rs.Edit
  n = rs.Fields(FieldName)
  rs.Fields(FieldName) = n + ACount
  rs.Update
  rs.Close
  GetNewNumber = n + 1
  Exit Function

TableInUse:
  Resume
  
End Function

This example actually stores the LAST USED number in the table. The function allows more than one number to be allocated, it returns the first of the allocated numbers.

If the last number was 123, and you request 10 numbers, the function will return 124, but update the table to 133 (the last allocated number) - so that you can use 124 to 133 inclusive.

Hope this helps.

 

[jsnunez]

Hi

I wrote this module, and it worjs in access mdb and access adp:

Public Function getNewInvoice() As Long
Dim strQuery As String, rsResult As ADODB.Recordset


strQuery = "SELECT Max(invoice.invoicenum) AS MaxOfInvoiceNum FROM invoice;"
Set rsResult = CurrentProject.Connection.Execute(strQuery)
getNewInvoice = rsResult("MaxOfinvoiceNum") + 1


End Function

it works!!!
jsn

 

[lespaul]

jsn,

the only problem with your solution is that if more than one person tries to create a new invoice at the same time, they may both end up with the same number. That's why earthandfire emphasized the exclusivity of that solution.

leslie

 

[jsnunez]

That is true Less.

I have also added the solution with specific table that will store the last invoice number used.

I am going to test both scenarios.

Thanks for your help.
jsn

 

[formerTexan]

Hi,

Good advice above. Particularly giving the user exclusive rights to the table. I will just elaborate on why I suggest keeping a seperate table (aside from just conflicting users) and perhaps some of the others can go further in offering ways to bulletproof the exercise.

Using the invoice table to retrieve the MAX number means that a user can create a new invoice, print it out and send it off. If this invoice is then deleted, then the next invoice created will duplicate the number. A seperate table maintaining a single number should avoid this.

Cheers,
Bill