AutoNumber VBA Question~!

[hwong]

Greeting all!

I have written the below codes for creating auto-number (i.e. pre_number) for data entry of a form. It runs perfectly when I used it at 1st time. However, when I exit and re-open the form, the new record's pre_number field becomes 1!!!

I spent much time to surf internet but still get no solution. Would anyone helps? Thanks!!

Private Sub Form_Current()

If Me.NewRecord = True Then
    Pre_Number.DefaultValue = Nz(DMax("Pre_Number", "Transaction Records")) + 1
End If
End Sub

 

[CBrighton]

Why are you trying to manually create an autonumber when Access has the functionality to do this automatically?

Regardless, try something like this:

Private Sub Form_Current()
 
'Open virtual recordset 
 
dim db as database
dim rst as recordset
 
set db = CurrentDB
set rst = db.OpenRecordset("Transaction Records")
 
'If already at end of file then 0 records, NewRecord = 1
'Else NewRecord = number of records in the table + 1
 
If rst.EOF then
   If Me.NewRecord = True Then
      Pre_Number.DefaultValue = 1
   End If
Else
   If Me.NewRecord = True Then
      rst.movelast
      Pre_Number.DefaultValue = rst.RecordCount + 1
   End If
End If
 
'Close and unset recordsets
 
rst.close
set rst = nothing
 
End Sub

:edit:

However, the issues with code like this is that it does not take into account deleted records, etc. To get it to work as well as the autonumber functionality provided by Access you need extra code to check for duplicates, etc.

:edit2:

JBB's attached database in this thread may help you refine the code you currently have (using DMax), I've not downloaded it but it seems to be the same kind of thing (replicating the function of autonumber): http://www.access-programmers.co.uk/forums/showthread.php?t=233155

 

[hwong]

Thanks 1st. I will try.

Whay I am trying writing autonumber function myself is the function provided by Access is quite poor that an autonumber will be skipped if a record is not saved. Hence audit trail (deleted record? no record?) will be lost .

 

[hwong]

Thanks Cbrighton!

I think record count is a bit "informal" cause it may create duplicate number if a previous record is deleted.

Oh~~this is still an annoying question to me.:banghead:

 

[Galaxiom]

Rather than use the Default property I would suggest you write the value into the textbox on the BeforeInsert Event of the Form. This is only triggered by the insertion of a new record.

Private Sub Form_BeforeInsert()

    Me.Pre_Number = Nz(DMax("Pre_Number", "Transaction Records")) + 1

End Sub

 

[John Big Booty]

I don't understand why you are setting Default Value of the field Pre_Number Why not simply set the value of Pre_Number usingthe following;

Private Sub Form_Current()

If Me.NewRecord = True Then
    Pre_Number = Nz(DMax("Pre_Number", "Transaction Records")) + 1
End If
End Sub

 

[Pat Hartman]

Whay I am trying writing autonumber function myself is the function provided by Access is quite poor that an autonumber will be skipped if a record is not saved. Hence audit trail (deleted record? no record?) will be lost .

Don't you think you're being a little arrogant here? The reason that autonumbers are discarded when the record is not saved is because in a multi-user environment, more than one person at a time could be adding a new record. PersonA starts a new record, PersonB starts a new record before PersonA saves his record. What ID should be assigned to PersonB? The code you are writing will fail in this scenario since both will be assigned the same ID. So is yours really "better"?

 

[Galaxiom]

When using the DMax technique the crucial factor involves when the number is calculated.

If the number is determined when the record is created then it is important to save the record immediately so that any subsequent DMax will see that record. Otherwise another record will get the same number.

However an abandonned record will still result in a gap. To get around this the DMax can be applied as the record is saved.

Even with DMax it is still quite possible for duplicates if two users get the number at the same time. I prefer to keep the next number in a table and lock out other users while it is read and incremented.

 

[Pat Hartman]

I prefer to keep the next number in a table and lock out other users while it is read and incremented.

Any method that includes locking in the assigned number to prevent duplicates runs the risk of creating gaps if the record is not committed. This is exactly what Access does with autonumbers. It assignes the number and commits it immediately. Then if the record is not completed, a gap is created because the record is discarded.

Hwong,
The point is that autonumbers cannot and should not be used for anything except to provide a unique identifier. If you have to have a sequence number that increments by 1 and has no gaps, you need to assign the number yourself and you need to use a loop that increments the sequence number if it duplicates an existing value and try the insert again and again until you succeed. That is the only way to guarantee no gaps. This is not a "better" solution than what Access (and every other relational database) provides. It is simply a solution to a different problem.

 

[Galaxiom]

Any method that includes locking in the assigned number to prevent duplicates runs the risk of creating gaps if the record is not committed. This is exactly what Access does with autonumbers. It assignes the number and commits it immediately. Then if the record is not completed, a gap is created because the record is discarded.

The issue is nothing to do with locking. Any technique that allocates a number and then allows the user to cancel the record will result in gaps.

As I said previously, what matters is when the number is generated relative to when the record is saved. If another number is allocated before the record is saved then it will be prone to duplication.

If you have to have a sequence number that increments by 1 and has no gaps, you need to assign the number yourself and you need to use a loop that increments the sequence number if it duplicates an existing value and try the insert again and again until you succeed. That is the only way to guarantee no gaps.

The part about having to assign the number yourself is correct. However the technique described is certainly not the only way to achieve no gaps.

The technique I described also works reliably. The number needs to be allocated immediately before the record is committed and no other users allowed to get the next number until that process is completed.

This is easily achieved by opening a recordset against the NextNumber table with dbDenyRead + dbDenyWrite Options argument. The recordset is closed after the value is read, incremented and the record saved. In this case a loop repeatedly trys to open the recordset in case another user already had it locked.

In my opinion this is considerably better than what I see as a clumsy solution of capturing the errors resulting from attempting to write a duplicate key and looping until it works as you have described.

During conflicts your technique would result in far more network traffic and processing by the engine.

Moreover I regard error capture (especially errors raised by the database engine or server) as the very last resort to dealing with logical requirements.