How to create my own Autonumber ?

Access XP

I want a unique clientnumber for each client.
I also have departments.
The format I need is as follows:

Department-code = 2 numbers
autonumber = 6 numbers

eg.
12 000016
07 000125
10 000125
15 000125
...

So the whole code must be unique, but it is possible to have more than 1 of the same autonumber.
Is this possible? I was looking at http://www.experts-exchange.com/Databases/MS_Access/Q_11726758.html but it doesn't seem as easy for me.

Geert
LVL 1
geertbroAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DrTechCommented:
What do you mean by "more than 1 of the same autonumber"?
0
IanWorcesterCommented:
Have 3 fields, DeptNo (Numeric) ClientNo (Numberic) and DeptClientNo (Text), You can make the primery key the DeptNo and ClientNo together and you can concatanate these into a text field for display purposes.

Use this code to retreive the next number for the department:

Function GetNextID(ByVal lngDeptID As Long) As String
   
    On Error GoTo GetNextID_Err
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim lngNextNo As Long
    Dim strNextID As String
   
    Set db = CurrentDb
    strSQL = "SELECT DeptID, ClientID FROM Table1  WHERE DeptID = " & CStr(lngDeptID) & " ORDER BY ClientID DESC;"

    Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
   
    If rst.RecordCount > 0 Then
        rst.MoveFirst
        lngNextNo = rst("ClientID") + 1
    Else
        lngNextNo = 1
    End If
   
    ' Pad with a leading zero if necessary
    strNextID = Right("0" & CStr(lngDeptID), 2)
    strNextID = strNextID & Right("000000" & CStr(lngNextNo), 6)
   
    GetNextID = strNextID
   
    rst.Close
    db.Close
   
    Set rst = Nothing
    Set db = Nothing

GetNextID_Exit:
    Exit Function
GetNextID_Err:
    Beep
    MsgBox Error, vbExclamation
    Resume GetNextID_Exit
End Function


Hope this helps.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
geertbroAuthor Commented:
The unique number can be
07 000125
10 000125
15 000125

so it is possible to have more than 1 of
000125

Together with the department-code it will be unique
07+000125 = 07000125
10+000125 = 10000125
15+000125 = 15000125
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DrTechCommented:
Just make the to field a combined primary key. The key then consists of two fields, that must be unique together.
0
IanWorcesterCommented:
Sorry, I named ClientNo, Client ID in the code example.
0
1WilliamCommented:
I agree with DR Tech, use a compound key.
0
ki_kiCommented:
Hello,
I don't know if i can ask a question here (?).
How do you create a primary key that's a combined key (from 2 fields) in ACCESS? Thanx.
0
DrTechCommented:
Open the table in design view. Select the to fields. Click the key button in the toolbar.

And another time: Don't ask questions in other peoples theads. Create your own, and add your own points.
0
geertbroAuthor Commented:
Thanx, IanWorcester.
I'm working on it myself
0
IanWorcesterCommented:
Any Time.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.