Auto Number with Letter

[Guest]

I have a table with a list of Merchants and their data. I have an Auto
Number set up in an ID Field. I want to use this number in an Internal ID
Number with an identifying letter(s) in front. For example, The Auto Number
assinged is 1235, we want the Interal ID Number to be SD1235. I have a
seperate field for the Internal ID Number. How can I automatically populate
the Internal ID Number field with the combined Letter and Auto Number
(SD1235) when my users enter a new Merchant? Thanks!

 

[Roger Carlson]

First of all, if your Internal ID Number needs to be sequential without
gaps, you cannot use an autonumber field. The autonumber datatype will
develop gaps that cannot be recovered if a record is 1) deleted or 2)
cancelled before it is saved.

The usual advice is to create your own number in code. Here some examples:

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Semaphore.mdb'
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Increment Alpha Field.mdb'
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Thanks for your response, however the numbers don't need to be sequential
without gaps, and we have already been using the Autonumbers in the Internal
ID Number. I will definitely use your advise in the future, however, we are
now in the middle of this project...is there anyway I can automatically
populate
the Internal ID Number field with the combined Letter and Auto Number
(SD1235) when my users enter a new Merchant? I've been doing this manually,
but we now have a very large import. Thanks!

 

[Guest]

I would not combine them, but use a method to display them together. The
trick is, how do you select the letters with which to prepend the ID?
If you are using a bound form, I suggest creating a control for the ID and
one for the InternalIDPrefix and making them invisible. Then for the
combined field, create an unbound control to display the combination of the
two. You will also need code create the combination for display and code to
load the prefix value into it's bound control.
This is really more work than doing what Roger suggested, but if you are so
far down the line in the project that changing fields would impact other
objects, then you will be forced into this cumbersome design.

 

[Roger Carlson]

In that case, I wouldn't even store the number in it's own field. Store
your alpha value in one field and leave the autonumber as is. Anytime you
need this number (in a query, form, or report), just concatenate the two
fields together.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L