Customized Autonumbering? 1

[wlfpackr]

Just wondering how you can create customized auto-numbering (or a sequence) in Access. We label all our PC's with our company initials and then a four digit number, i.e. KA0654 next PC entered would be KA0655. I'm trying to get the Access database to auto-number in this format and I am not having any luck.


=================
There are 10 kinds of people in this world, those that understand binary and those that do not.

 

[Eupher]

See faq700-184. Also, if you search for autonumber in the Access Tables and Relationships forum you will find quite a few threads on this topic.

Ken S.

 

[TheAceMan1]

How are ya wlfpackr . . . . .

Try this . . .

The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.

Next, in a [blue]module[/blue] in the [blue]modules window[/blue], copy/paste the following function ([blue]you![/blue] substitute proper names in [purple]purple[/purple] . . . be careful to maintain spacing):

[blue]Public Function NextCompanyID() As String
   Dim db As DAO.Database, rst As DAO.Recordset
   Dim SQL As String, strNext As String

   Set db = CurrentDb()
   SQL = "SELECT Max(Val(Right([[purple][b]AutoNumName[/b][/purple]],4))) AS CoNum " & _
         "FROM [[purple][b]TableName[/b][/purple]];"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   strNext = CStr(rst!CoNum + 1)
   NextCompanyID = "KA" & String(4 - Len(strNext), "0") & strNext
   
   Set rst = Nothing
   Set db = Nothing
   
End Function[/blue]

Call the function from just about anywhere you like . . .

[blue]   Me!TextBoxName = NextCompanyID()
or
   VBA_Variable = NextCompanyID()[/blue]

See Ya! . . . . . .

 

[RoyVidar]

I'll side with Eupher - especially if you are going to use this in a multiuser setup, then the comments and methods in MichaelRed's faq (referenced by Eupher), might be relevant.

I'm not perticluarly fond of using a concatenated primary key either - this primary key consists of a company identifier and a number. I'd rather have separate field for them, to make it easier to track if/when this system is going to track computers and other inventorey owned by other companies, too.

Add another table for company, use the two letter code as primary key, and link it to this table. Then use either an ordinary Autonumber, or a number according to MichaelRed's faq as the real identifier/primary key, and concatenate with the company code on the fly whenever it's needed for display purposes.

Probably the easiest, would be to just display a number with the initail company code. To achieve that, you could simply use ordinary Autonumber, and use the following in the format property of it:

[tt]"KA0000"[/tt]

This will format your autonumber just like you wan't it, but store only the number. Should more than one company become an issue, then at a later time, add "the company code", and concatenate for display purposes.

Roy-Vidar