Autonumber in Access 2007

[gminezes]

Hi Guys,

I want to use the Autonumber function in my table in a specific format. The format is SP10000. And I want Access to automatically number each entry by one with each new entry eg. SP10001...SP10002...SP10003.

Please let me know how I can do this as when I use the autonumber function it only gives me simple numbers such as 1...2...3...4 etc.

Please help
Thanks

[BitBlt]

Autonumber doesn't really work that way. There are a number of different ways you can "trick" it, depending on how you want to use the number.

Simplest of all (of course) is don't worry about formatting your AutoNumber column when you store it. Just use a function to format it when you have to display it or expose it.

One example is, if you use a QueryDef, just use a combination of literal and Format statement to format your number like so:

Expr1: "SP" & Format([myKey],"000000")

Second example is, if you expose your table in a form, you can use a similar technique in the Format attribute of the text box or label you use to display your key.

However, if you REALLY have to store the formatted number, you could create the table with a second column to hold the formatted version. Then, whenever you insert a row, re-read the row you just inserted and update the second column. A slightly simpler way (depending on if you are exposing your record with a Form) is to put some code into the AfterInsert event and populate your second column there.

Hope these suggestions help. Good luck!