?
Solved

How to create my own Autonumber ?

Posted on 2003-03-24
10
Medium Priority
?
391 Views
Last Modified: 2006-11-17
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
0
Comment
Question by:geertbro
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 6

Expert Comment

by:DrTech
ID: 8194141
What do you mean by "more than 1 of the same autonumber"?
0
 
LVL 1

Accepted Solution

by:
IanWorcester earned 140 total points
ID: 8194150
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
 
LVL 1

Author Comment

by:geertbro
ID: 8194154
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Expert Comment

by:DrTech
ID: 8194162
Just make the to field a combined primary key. The key then consists of two fields, that must be unique together.
0
 
LVL 1

Expert Comment

by:IanWorcester
ID: 8194368
Sorry, I named ClientNo, Client ID in the code example.
0
 
LVL 18

Expert Comment

by:1William
ID: 8194626
I agree with DR Tech, use a compound key.
0
 
LVL 4

Expert Comment

by:ki_ki
ID: 8197290
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
 
LVL 6

Expert Comment

by:DrTech
ID: 8200702
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
 
LVL 1

Author Comment

by:geertbro
ID: 8209904
Thanx, IanWorcester.
I'm working on it myself
0
 
LVL 1

Expert Comment

by:IanWorcester
ID: 8210003
Any Time.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question