Custom Primary Key

[Guest]

I have a client who would like to have serial numbers assigned to records as
follows:

05-0001
05-0002

where the "05" indicates the year, and the "0001" indicates the record
number (incrementing by 1) within a given year. In other words, next year,
the first serial number would be:

06-0001

etc.

The dash could be optional, as I could always format the field to
incorporate that. Does anyone have an idea as to how I can accomplish this?
I think coding something in the data entry form makes the most sense, but I'm
not quite sure how.

Thanks,

Keith

 

[Mike Painter]

I have a client who would like to have serial numbers assigned to
records as follows:

05-0001
05-0002

where the "05" indicates the year, and the "0001" indicates the record
number (incrementing by 1) within a given year. In other words, next
year, the first serial number would be:

06-0001

etc.

The dash could be optional, as I could always format the field to
incorporate that. Does anyone have an idea as to how I can
accomplish this? I think coding something in the data entry form
makes the most sense, but I'm not quite sure how.

Thanks,

Keith

Two fields would be easier.
Format a date field for the year and default to Date().
You will have to maintain the number field in a table. There are a number of
ways to do it.
You can get the number with dlookup and store the next value back with
Insert Into.
If you are in a single user environment that will be enough, if not buy the
Access developers handbook and usae their code. Make sure you use as
autonumber as the real key to the table.
Build your forms, etc on queries sorted by year and number.

99% of the time this request is of no real value in the computer world.

 

[Guest]

Several months ago I received help on a similar question from Allen Browne,
with additional explanations and assistance from Tim Ferguson. I pass along
a variant of Allen's code that worked for me:

If Me.NewRecord Then
Dim strWhere As String
Dim varResult As Variant

strWhere = "ID_Number Like """ & Format(Date, "yy") & "*"""
varResult = DMax("ID_Number", "tblYourTable", strWhere)

If IsNull(varResult) Then
Me.ID_Number = Format(Date, "yy") & "-0001"
Else
Me.ID_Number = Left(varResult, 3) & _
Format(Val(Right(varResult, 4)) + 1, "0000")
End If
End If

This code could be placed in the form's Current event if the database will
be used by only one person at at time. If there could be multiple users the
code would be better in the form's Before Update event. If there are to be
many users and a likelihood that two records could be entered simultaneously
then you may need some sort of error trapping.
This code assumes that the field in which this numbering occurs is
ID_Number, and that the table name is tblYourTable. Watch out for line
wrapping.

 

[Guest]

Use the Year() function to get the year. You will probably have to subtract
2000 from it depending on how you have your system set to handle dates.

strKeyYr = Format(Year(Date)-2000,"00-")

Then use the DMAX() function to find the current high number.

strHighKey = DMAX("[PrimaryKeyFieldName]","YourTableName", _
& "Left([PrimaryKeyFieldName],3) = '" & strKeyYr & "'")

If IsNull(strHighKey) Then 'This is the first entry for the year
strHighKey = strKeyYr & "0001"
Else
strHighKey = strKeyYr & Format(Right(strHighKey,4) + 1,"0000")
End If

 

[Guest]

On a similar question I received a reply from Allen Browne, with additional
explanations and assistance from Tim Ferguson. The following code is based
on their assistance:

If Me.NewRecord Then
Dim strWhere As String
Dim varResult As Variant

strWhere = "ID_Number Like """ & Format(Date, "yy") & "*"""
varResult = DMax("ID_Number", "tblNumbering", strWhere)

If IsNull(varResult) Then
Me.ID_Number = Format(Date, "yy") & "-0001"
Else
Me.ID_Number = Left(varResult, 3) & _
Format(Val(Right(varResult, 4)) + 1, "0000")
End If
End If

This code could go into the form's Current event if it is a single user
database. If several people could be entering records at the same time it
could go into the form's Before Update event. If simultaneous data entry is
a possibility you may want to add some kind of error trapping.
The code assumes that the field where the number will be stored is
ID_Number, and the table is tblYourTable. ID_Number is a text field in this
example.

 

[Guest]

My two replies should be about the same. I posted the first one, but several
hours later it had not showed up, so I posted again. Today they are both
there. Hmmm.

 

[Guest]

Thanks to all. I will try your suggestions in the next few days.

Keith

 

[Guest]

After reading this thread, following the advice of others, and poking around
the KB, I came up with the code below your question. At the risk of showing
glaring inefficiences to the experts, I'm sharing it as a gesture of
appreciation for the help I've found here.

There's probably a better way, but it works for me. Hope it is helpful for
you.

I have a client who would like to have serial numbers assigned to records as
follows:

05-0001
05-0002

where the "05" indicates the year, and the "0001" indicates the record
number (incrementing by 1) within a given year. In other words, next year,
the first serial number would be:

06-0001

etc.

The dash could be optional, as I could always format the field to
incorporate that. Does anyone have an idea as to how I can accomplish this?
I think coding something in the data entry form makes the most sense, but I'm
not quite sure how.

Thanks,

Keith

Public Function NextCustomCounter(TableName As String) As String

On Error GoTo NextCustomCounterErr

Dim rs As DAO.Recordset
Dim LastCounter As String
Dim NextCounter As String
Dim intCount As Integer

Set rs = CurrentDb.OpenRecordset("tblCustomCounters", 2)

'Open the DAO recordset.
With rs
.FindFirst "TableName = '" & TableName & "'"

If Not .nomatch Then 'When the table is found,

If Not IsNull(!LastCounter) Then 'And a counter entry
exists
'Check to see if it's a new year
If Left(!LastCounter, 2) = Format(Date, "yy") Then
'Use the next sequental counter
LastCounter = !LastCounter
Else
'If this is the first transaction of the new year, reset
the counter
LastCounter = Format(Date, "yy") & "-0"
End If
Else
LastCounter = Format(Date, "yy") & "-0"
End If 'Counter exists
'Update the record with the next entry
.Edit
Else
'Add the new table & counter to the CustomCounters table
LastCounter = Format(Date, "yy") & "-0"
.AddNew
!TableName = TableName
End If

'Increments by one the value of the numeric portion of the counter
'Determines the length of the string by subtracting 3 (YY-)
intCount = Val(Right(LastCounter, (Len(LastCounter) - 3))) + 1

'Formats the updated value of the counter
NextCounter = Format(Date, "yy") & "-" & CStr(intCount)

'Store the counter
!LastCounter = NextCounter
.Update

End With 'recordset

'Clear the recordset
Set rs = Nothing

'Sets for return to the calling code
NextCustomCounter = NextCounter

Exit Function

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'The following error routine should be replaced with a custom
'error routine.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
NextCustomCounterErr:
MsgBox "Error " & Err & ": " & Error$
'If Err <> 0 Then Resume
'End
End Function