Auto Numbers

[davey3389]

Hi Guys,

I know I'm being an idiot here but my brain has gone dead on me.

I got job numbers that I need to auto populated with the format for the first number being "NRN12001" and the next one following "NRN12002" so really need to know what I need to change in the design view of the table

Cheers Everyone

Ben

 

[Isskint]

You could make it an AutoNumber field and set the Format for that field to "NRN12"000.

 

[davey3389]

and when i view forms it will show the NRN bit aswel as the number

Cheers

 

[John Big Booty]

If you follow Isskint's advise you do need to be aware that there situations in which the Autonumber function will not provide sequential number, it can even provide negative numbers.

If you want a guaranteed sequential number have a look at the Dmax() function plus 1.

The following Code in the Form's On Current Event Should do the trick;

    If Me.YourCounterField = 0 Or IsNull(Me.YourCounterField) Then
        Me.YourCounterField = Nz(DMax("YourCounterField", "YourTableName"), [B][COLOR="Red"]X[/COLOR][/B]) + 1
    End If

Note; Replace X with your seed number (the number you wish your series to start at).

 

[boblarson]

I second JBB's remarks. If you need a number that has meaning, do NOT use an Autonumber. An Autonumber should only be used if you do not care if it is positive, negative, is not sequential, etc. Normally an autonumber is really best used as a surrogate key and let the system handle that. The common saying is that "autonumbers are not for human consumption."

 

[Isskint]

Yeah i would have to agree with both JBB and Bob (1 they are quite correct and 2 they know a LOT more than i do). Your format can stay the same (it is only displaying the number that way not storing it that way).

Incidentally, is the 12 in the prefix (NRN12) for the year 2012? This would restrict (from a display point) the number of records to 999.