Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to create my own Autonumber ?

Posted on 2003-03-24
10
Medium Priority
?
398 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
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

577 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