By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,122 Members | 927 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,122 IT Pros & Developers. It's quick & easy.

Want to add Autonumber with 8 digit format using MS Access or VB

P: 1
Hi ,

I want to add a row of serial number for the existing records in MS access .Whenever I am trying to define a new row with type as "Auto Number" and save the same it automatically gets changed to Text type.Please suggest.Also, the requirement is modified and now the serial number has to be of 8 digits i.e. the first number should be 00000001 and the max length should be of 8 digits as this field is being received by an external system and this system will accept only 8 digits .Is it possible to do in MS access and how can I do the same.

I am allowed to use VB as well,so can anyone please let me know how it can be done through any of these.

Feb 25 '09 #1
Share this Question
Share on Google+
4 Replies

Expert Mod 15k+
P: 31,419
I would suggest forgetting AutoNumbers, and forgetting Numbers. This looks like you need a text field, 8-characters long which holds a numeric value formatted to 8 digits.

When adding a new record, use the DMax() Domain Aggregate function to determine the highest currently used, add 1 to the Val() of that, then reformat it to an eight digit string ("00000000").

Welcome to Bytes!
Feb 25 '09 #2

P: 675
Won't this solution lead to problems in a multi-user environment? Two users find next serial number to use before either saves their new record, then both are using same new value.
I would assume from the original statement that there is already an autonum field in this table. That is why the field gets "changed to text".
. . . this field is being received by an external system and this system will accept only 8 digits .
If the table is copied for this other program, it could be copied with a query which changes the autonum field to a text field of 8 digits. If the table is used directly by this external system, wouldn't it be better to have a 2nd field, text, where the autonum key is assigned to it using a format. Field8Digit = Format(FieldAutoNum,"00000000")
Feb 25 '09 #3

Expert Mod 15k+
P: 31,419
No more than working in a similar way with an AutoNumber field would.

If you want to display the value before it's been written away (as some seem to like trying), then sure. This is not required for AutoNumber fields, so there's no logical reason why it should be done this way with a value determined in the code.
It would be possible to do it that way. It wouldn't be my recommendation though.

It would introduce the requirement to keep the two values matching. Essentially the same issue involved with normalisation, where it's not recommended to duplicate values anywhere.

In my view it would also make the concept more difficult to understand, particularly for those inexperienced with databases. All that said, it's a perfectly good idea to put it on the table for anyone with similar problems to find and choose from.
Feb 25 '09 #4

Expert 2.5K+
P: 3,532
I've used auto-incrementing hacks for a number of years in multi-user environemnts without any problems, as long as the "number" is assigned at the last possible moment before the record is saved, i.e. at the very end of the Form_BeforeUpdate event, after all validation code is run.

And this can be easily be done with the "number" being defined as either Numeric or Text, although, of course, any "number" not being used for math should be defined as Text, using the Val() function as NeoPa suggested.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  3. 'Validation code, if any, goes here
  5. If Me.NewRecord Then
  6.  If RecordsetClone.RecordCount = 0 Then
  7.   Me.SerialNumber = "00000001"
  8.  Else
  9.   Me.SerialNumber = Format(DMax("Val([SerialNumberField])", "AI2") + 1, "00000000")
  10.   End If
  11. End If
  12. End Sub
where SerialNumber is the textbox in the form and SerialNumberField is the name of the field in the table holding the serial number (which may or may not have the same name.)

Linq ;0)>
Feb 25 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.