Auto incrementing number for multible records.

[uknow]

Hi,
Do you have any idea how to create the number which will auto increase for different records.Each record will start with 1 and so on.TQ for advice.

For instance:
Record A will start the number 1
Record B also will start the number at 1 as well...and so on.

 

[SgtJarrow]

Credt for the below goes to the origianl poster...I don't remember who it was....I am sorry, code poster, I wish I could give you the credit myself.

*********** Someone's Code ******************

Create a new table called ID & give it 2 fields Prefix & LastID.
Use this code to create your Unique ID's

Function GetNextIDWithPrefix(prefix As String) As Variant

Dim sTable As String 'table storing ID's
Dim db As DAO.Database 'this database
Dim rst As DAO.Recordset 'recordet with
Dim intRetry As Integer 'number of retries

On Error GoTo ErrorGetNextID
sTable = "AbsenceID"
'set the table with the IDs in
Set db = CurrentDb()
'set reference to this database
Set rst = db.OpenRecordset("SELECT * FROM " & sTable & " WHERE prefix='" & prefix & "';", dbOpenDynaset) 'Open table with the counter
'open a query which looks for a record that matches 'prefix'
If rst.EOF And rst.BOF Then
'if there are no records found then this is a new prefix
rst.AddNew
'add a new record
rst!prefix = prefix
'store the prefix
rst!lastid = 1
'set the counter going
rst.Update
'save the record
rst.MoveFirst
'move to the 1st record
Else
'the prefix already exists
rst.MoveFirst
'move to the record
rst.Edit
'increase the counter
rst!lastid = rst!lastid + 1
rst.Update
'save the record
End If

GetNextIDWithPrefix = prefix & Format(rst!lastid, "0000&quot
'return the unique ID


rst.Close 'tidy everything up
Set rst = Nothing
db.Close
Set db = Nothing

ExitGetNextID:
Exit Function

ErrorGetNextID: 'If someone is editing this record trap the error
If Err = 3188 Then
intRetry = intRetry + 1
If intRetry < 100 Then
Resume 'try 100 times to get in
Else 'Time out retries
MsgBox Error$, 48, &quot;Another user editing this number&quot;
Resume ExitGetNextID
End If
Else 'Handle other errors
MsgBox Str$(Err) & &quot; &quot; & Error$, 48, &quot;Problem Generating Number&quot;
Resume ExitGetNextID
End If
End Function


Call it with GetNextIDWithPrefix(&quot;CPM2Medic1&quot to get
CPM2Medic10001
CPM2Medic10002
CPM2Medic10003
etc


There is no limit to the number of prefixes you can store so this is very flexible.

Enjoy

************** End Code ****************

****************************
Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com

 

[uknow]

Thanks.
but i still not clear how to do it.may be I need to explain more details.I have few table consist of customer, project and Item_code.Each customer could have few project and each project has one Item_code.Each Item_Code might have more then one testing number.The question is how do I create the auto number for each testing No of a Item_code ?

Illustration:

Item_code : (same for one record eg. ItemPAD
Qmin : (testing no range between 1 to 5)
Ot: ( same as Qmin)
autonumber : (Start at Number 1) -This is the no I do know how to generate.

Qmin and Qt will have many testing No

TQ for your great advice.