How to Write Auto Number Function in VBA

[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

[JoeM]

A few things:

1. The NZ function usually requires a second argument, i.e.
Nz(DMax("Pre_Number", "Transaction Records"),0) + 1

2. Why are you creating an AutoNumber instead of just using Access's AutoNumber field, which will generate them all by themself?

[Missinglinq]

First off, don't muddle the waters by calling this an Autonumber! AutoNumber has a special meaning in Access, and you'll confuse things! You're speaking about an Auto-Incrementing Number!

And the only reason I know of, for this kind of behavior, using an Auto-Incrementing Number hack (your code is valid) is that you're trying to use it on a Form that has the Data Entry Property set to Yes, and you can't do that! When such a Form opens, its Recordset is empty! If you look at the 'Number of Records' thingie at the bottom of the screen, you'll see that it doesn't reflect the actual number of Records in the Table. You cannot view/edit any existing Records, you can only add New Records.

Your options are to forget about the AI Number or to open the Form with the Data Entry Property set to No.

The pertinent question is, why do you have Data Entry set to Yes? If it is because you want your Form to open to a New Record, that can be accomplished in other ways.

Also, placing this kind of code in the Form_Current event, in a Multi-User environment, rather than in the Form_BeforeUpdate event, is a very bad idea, and can lead to multiple Records having the same number.

...The NZ function usually requires a second argument, i.e.
[B]Nz(DMax("Pre_Number", "Transaction Records"),0) + 1

Not really! If the second argument is omitted the Access Gnomes plug in an appropriate value; in the case of Pre_Number being an actual Number, they'll plug in the Zero you added.

Linq ;0)>

[JoeM]

Not really! If the second argument is omitted the Access Gnomes plug in an appropriate value; in the case of Pre_Number being an actual Number, they'll plug in the Zero you added.

Its funny, I had tested it out, and I could get it to work in an Access Query, but not on the Form. Not sure why.

In any event, I don't like to leave anything to chance and let Access' determine how to handle it (if there is a simple way for me to control it).
As we all know, Access doesn't always behave the way we think it should, or the way we would like it to.

Just my preference...

[Missinglinq]

...Access doesn't always behave the way we think it should, or the way we would like it to...

Who among us do?

[hwong]

Thanks Joe!

Thanks Missingling! What you said is completely correct. I have tried my code above in BeforeUpdate Event but it shows nothing =.= possibly because I have locked the Pre_Number textbox to prevent manually amendment?

I have tried other way round by below code. However, say last record is 3, my next number is 5! I think it counts the record I am updating as well.

How can I create incremental number ? It really frustrated me~Access embedded autonumber function is quite poor that the autonumber will be skipped if the record is not saved at last. It makes many "holes" in the table!

Private Sub Form_Current()

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

[JoeM]

Why are "holes" in your numbers problematic?
What exactly are you trying to do with this number?

If you sole purpose is to create a unique identifier, then Autonumber will do that task nicely without any VBA needed. It shouldn't matter if there are "holes" in the numbers.

[Missinglinq]

...I have tried my code above in BeforeUpdate Event but it shows nothing =.= possibly because I have locked the Pre_Number textbox to prevent manually amendment?

No. The Pre_Number simply doesn't show when it's assigned, from the Form_BeforeUpdate event. But when you later return to the Record it will then be there!

Why are "holes" in your numbers problematic?
What exactly are you trying to do with this number?

I have to ask the same question as Joe just did, especially what is the purpose of Pre_Number?

Linq ;0)>

[Missinglinq]

Stop the presses! I just realized that you're using your hack to assign the generated number to the DefaultValue property of Pre_Number! Drop the Default and just assign it to the Value Property of Pre_Number.

Other than that, your auto-incrementing hack looks fine. If you absolutely need for the number to show up immediately, you'll need to keep it in the Form_Current event and follow your code, immediately (next line) with

DoCmd.RunCommand acCmdSaveRecord

This will Save the Record, which insures that the same Pre_Number will not be assigned to another Record being entered by another user.

I haven't tested this, but it should work for you:

Private Sub Form_Current()

If Me.NewRecord = True Then
  Pre_Number.Value = Nz(DMax("Pre_Number", "Transaction Records")) + 1
  DoCmd.RunCommand acCmdSaveRecord 
End If

End Sub

But once again, what are you using Pre_Number for?

Linq ;0)>