Customized autonumber ID.

[AccessBeetle]

I have to create a RecordID which should be unique and autoincremented. The specifications are something like,

1) The number must be 9 digits long
2)First two digits should represent current YY, which should increment every year
3)Next 7 digits should be like 0000001 and should be autoincremented (like 7 digits long autonumber concatenated to the current year)

so the final number should look like this: 090000001, 090000002, and so on.

What should be my approach to this?
Thanks in advance as always!!

[ChipR]

You may not be able to store preceding zeros if you use a number field, so probably a string. Here's how I think you would do it.

Get the max RecordID with DMax.
Convert the first 2 characters to an integer.
See if they are equal to the current 2 digit year.
If so,
-Convert the rest of the RecordID to an Integer.
-Add 1 (deal with overflow?)
-Create a string, set it equal to the number.
-Append "0" to the front of the string until it is the proper length of 7
-Combine it with the year string.
Else,
-Create a string with the first two digits of the current year and "0000001"
End if.
Done.

[ADezii]

You're probably better off leaving it as Text if you can, but that being said, this simple Function should do the trick. I'll leave it up to you to figure in the possible year change:

Public Function fIncrementAuto() As String
Dim varMax As Variant
 
varMax = DMax("[YourNumber]", "tblTest")
 
fIncrementAuto = Format$(Left(varMax, 2), "00") & Format(Mid$(varMax, 3) + 1, "0000000")
End Function

[AccessBeetle]

All these coding should be placed under Form's On open_click event. Am I right?

[AccessBeetle]

sorry I did not see Adezil's answer.

[missinglinq]

Come on, ADezii, that's the easy part! Doing the year change thing is the fun part! This will do it all.

Note that AI_Number is defined as Text.

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
If Me.NewRecord Then
 
 If RecordsetClone.RecordCount = 0 Then
  Me.AI_Number = Format(Date, "yy") & "0000001"
 End If
 
 If RecordsetClone.RecordCount <> 0 Then
  If Left(DMax("AI_Number", "YourActualTableName"), 2) = Format(Date, "yy") Then
    Me.AI_Number = Format(DMax("val([AI_Number])", "YourActualTableName") + 1, "000000000")
  Else
    Me.AI_Number = Format(Date, "yy") & "0000001"
  End If
 End If
 
End If
End Sub

By placing the code in the Form_BeforeUpdate, the number is assigned at the last possible moment before the record is saved, decreasing the chances, in a multi-user environment, of two records being assigned the same number. Using this method for a number of years, I have never run into this problem.

Linq ;0)>

[ADezii]

We have to leave something for the OP, don't we? (LOL)!

[AccessBeetle]

I am getting an error saying Cannot insert duplicate value in the table. ODBC call failed. When I tried to enter the record, the RecordsID populated with "090000001" second time also. Then I try to move to enter another record and there the error is.
Also, it would be great if you can explain me this part

If Left(DMax("RecordID", "RadioLog_tblRadioCallActivity"), 2) = Format(Date, "yy") Then
    Me.RecordID = Format(DMax("val([RecordID])", "RadioLog_tblRadioCallActivity") + 1, "000000000")
  Else
    Me.RecordID = Format(Date, "yy") & "0000001"
  End If
 

My actual tables are in SQL 2005 and I am linking them in Access 2003. Also, the application contains many forms from where user can enter new data.
Shall I write this code under each and every form? (I think I have to but not sure)

Exact Error:
ODBC Call Failed
Violation of Primary key constraint PK_RadioLog_tbl_RadioCallActivity cannont insert duplicate key in object 'dbo.RadioLog_tblRadioCallActivity. (#2627) and (#3621)

[AccessBeetle]

OK OK.
It is solved. It was the data creating problem. I removed all the data from all the adjoining tables and table itself is also clear.
There is still something wrong. I have customized Login interface. I logged in with User1 and entered the data. It was ok. Then I logged in using User2 and started to add data and it was again starting from 090000001. There was the same error there again. What am I doing wrong?
Thanks for help

[missinglinq]

Not really sure! It works fine on this end, but, of course, I'm not using a SQL 2005 back end, and have no real experience with it. Hopefully someone else here doesan d will be able to help..

The bit of code you asked about is checking to see if the year portion of the last record entered is the same as the current year. IF it is, it continues incrementing the number with the same year prefix. If it isn't it assigns the new year prefix and starts the "count" over again at 0000001.

Linq ;0)>

[AccessBeetle]

MissingLinq,
Thanks for explaining. I don't think the problem is on SQL side as it does updates the value but it could not compare it with the last entered RecordID and that is why whenever there is another user trying to enter data it starts all over again..So that this error (cannot insert duplicate primary key) pops up.
any inputs on that?
There are two forms which allows data entry for the same table (one has Data Entry property set to Yes and one has Data Entry property set to No and the second one also filters data based on District). Do you think that I have to write this code under both Forms' BeforeUpdate() event?
Is there any way we can attach such kind of logic to the table's RecordID field?
Thanks

[missinglinq]

I don't think the problem is on SQL side as it does updates the value but it could not compare it with the last entered RecordID

How do you know this? And why wouldn't it be able to compare it?

I think you need to post your entire Form_BeforeUpdate sub here for us to look at.

Linq ;0>

[AccessBeetle]

If Me.NewRecord Then
 
 If RecordsetClone.RecordCount = 0 Then
  Me.RecordID = Format(Date, "yy") & "0000001"
 End If
 
 If RecordsetClone.RecordCount <> 0 Then
  If Left(DMax("RecordID", "RadioLog_tblRadioCallActivity"), 2) = Format(Date, "yy") Then
    Me.RecordID = Format(DMax("val([RecordID])", "RadioLog_tblRadioCallActivity") + 1, "000000000")
  Else
    Me.RecordID = Format(Date, "yy") & "0000001"
  End If
 End If
 
End If

Here is my entire sub. I am saying that because, when I logged in with UserNo1 and enter data it all works perfactly fine (suppose I entered two set of data with RecordID 09-0000001 and 09-0000002). Then I log out and log in with UserNo2. It enters first records with RecordID like "0000001" instead of incrementing it with one like "09-0000003". Then when I hit enter it gives me that error saying Cannot insert duplicate value. There is something wrong and it might be a little thing but right now it seems like huge hurdle.
Thanks for looking into it.

[MrDeej]

dim rst as new adodb.recordset
with rst
.open "SELECT * from [table]",currentproject.connection,adopenforwardonly,adlo ckoptimistic
.addnew
![customized autonumber id] = right(year,2) & format(!autonumber;"00000000")
![other field] = [other data]
.update
.close

end with
set rst = nothing

[AccessBeetle]

Thank You MeDeej for your input.
Missinglinq and every person who runs into this problem,
I figured out what was causing the problem. (Yehhhh!!) It was the form's Data Entry property. As I have said mupltiple times before that I have two forms who are bound to the same table. One has Data entry property set to yes and other one has to No. When user logs in and go to SingleLine data entry (Data entry = yes), the form assumes that there are no previous records in the table so it fails to evaluate this line of code

If RecordsetClone.RecordCount = 0 Then 
  Me.RecordID = Format(Date, "yy") & "0000001" 
 End If 
 

Although, the last records entered was "09-0000002", it goes thorugh this loop and starts it again with "09-0000001". As this the primary key of the table it gives me error I have mentioned (Violation of Primary key, cannot insert duplicate value). When I changed Data Entry property = No, it fetched the last record and incremented it using the rest of the code.
Thanks MissingLinq, ChipR and Adezi for your precious inputs.

[missinglinq]

Glad you got it figured out and interesting tidbit of knowledge to know!

Linq ;0)>