Getting a non-automatic ID to become AUTOMATIC !

[THE STUDENT]

As some may know im in the process of development of a database and one of the functionality that I would like to achieve is a custom ID to become automatic on my 'ADD-CUSTOMER' form (e.g. CID0001 - CID0002) so on an so forth. From my research all I can see in access 2010 is a autonumber which cannot be altered (or at least i dont know how).
Can anyone please advise on how to achieve this? or if so direct me to a u tube video that might be able to help with such or any other way?

Regards


THE STUDENT

[euanl]

First create a query that counts the values in the ID field (only the ID field should be included in this query), call this qryID_Count. This will tell the database what the next reference should be numerically.

Now in the 'after update' function of one of the other fields in your form enter the following event procedure:

Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim vnotfound As Boolean
Dim nr As Integer 'next ref from query
Dim nractual As String 'next ref in text format
Dim part1 As String 'part 1 of virtual PIP (or walk in void ref)
Dim part2 As String 'part 2 of virtual PIP (or walk in void ref)



'now allocate the next ref

Set db = CurrentDb
Set qdf = db.QueryDefs("qryID_Count")

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next

Set rst = qdf.OpenRecordset

If Not (rst.BOF And rst.EOF) Then
vnotfound = True
Else
vnotfound = False
End If

If vnotfound = True Then
rst.Close

'if the list is populated then tell the database what actions you want it to perform,

'create a Dlookup from the count query that will give you the numeric value for the next reference
nr = DLookup("CountofID", "qryID_Count") + 1 ' the +1 is important to stop duplicates occurring


'if <10 the number should have 3 leading zeros added, if >10 and <100, 2 and so on.
If nr < 10 Then
nractual = Trim("CID000" & nr)
ElseIf nr > 10 And nr < 100 Then
nractual = Trim("CID00" & nr)
ElseIf nr > 100 And nr < 1000 Then
nractual = Trim("CID0" & nr)
Else: nractual = Trim("CID" & nr)
End If



Me.ID.Value = nractual

Refresh
Else:


nractual = "CID0001"
Me.ID.Value = nractual
Refresh
End If

'always remember to close the bits you have referenced
Set rst = Nothing
Set qdf = Nothing
Set db = Nothing
Set prm = Nothing

[RuralGuy]

This link may also be helpful: http://www.baldyweb.com/CustomAutonumber.htm

[euanl]

Here is a simplified version of my coding without having to create a query. Obviously change the table name and the field name if you need to.

'declare your variables

Dim nr As Integer 'next ref
Dim nractual As String 'next ref in text format
Dim part1 As String 'part 1 of ID
Dim part2 As String 'part 2 of ID

'Count the amount of existing records in the table
nr = DCount("ID", "tblTester") + 1


'if <10 the number should have 3 leading zeros added
If nr < 10 Then
nractual = Trim("CID000" & nr)
ElseIf nr > 10 And nr < 100 Then
nractual = Trim("CID00" & nr)
ElseIf nr > 100 And nr < 1000 Then
nractual = Trim("CID0" & nr)
Else: nractual = Trim("CID" & nr)
End If

'Take the value created and dump into the ID field
Me.ID.Value = nractual

Refresh