Autonumber Text

[Bill]

I asked this before then got busy and never got back to it. Now I can't find
the response. So...

I need to create a field which will be an autonumber field however I want it
formated like yy-nnn. Example would be the current two digit year 08 - the
next available number. Right now we are at something like 653. So ideally I
would want to seed the autonumber portion or have it automatically add 1 to
the last number. The finaly result needs to be a combined text/number field
as it is passed to other areas and is my main reference number. The end
result needs to look like 08-653 and the next new entry must be 08-654, etc.

Thanks,

Bill

 

[Jeff Boyce]

Bill

You'll need to "roll your own". The Access Autonumber data type is just
that ... Access assigns a number.

It sounds like you want to have a customized field holding both the year of
the record and a sequence number. That's not a good idea when designing
fields, trying to stuff more than one fact into one field.

Instead, consider having a date/time field and a sequence number field. You
can use a query to return the concatenation of the "yy" of your date field
and the sequence number field.

I'll take a wild guess that NEXT year you'll want "09-001" (and so on)...
search on-line (and at mvps.org/access) for "Custom Autonumber" to get ideas
on how to build a procedure that checks the date and the largest sequence
number (for the year) and adds one.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Pete D.]

I made this off of a MS article which with some minor changes should work
for you. I always have a table to set site specific requrements such as
photo directories or admin tools such as force logoff where I also store key
next number. Please realize this is my way of dealing with gov
orginizations that want much information within the key field even though it
isn't good relational programing. Takes years to un-teach what was taught
for many years so folks could keep stuff in 80 column punch card.

Option Compare Database
Option Explicit
'Counter modified for usage in two locations like a motorpool and sub
motorpool
'If sub motorpool number starts at 5001 daily
'If master motorpool number starts at 1 daily
'You must modify this counter program at variable ResetCounter by changing
commented code below.
Function Next_Custom_Counter() As String
On Error GoTo Next_Custom_Counter_Err
Dim MyDB As Database
Dim MyTable As Recordset
Dim NextCounter As Long
Dim NewDayCheck As Long
Dim NextDate As String
Dim ResetCounter As Long 'Used to set counter to number for location,
master or sub location
'ResetCounter = 5001 'Uncomment line Sub location or comment line if
master
ResetCounter = 1 'Uncomment line if master location or comment line if
sub location

'=================================================================
'Open table and get the current value of "Next Available Number,"
'=================================================================
NextDate = Format(Date, "yyyymmdd")
Set MyDB = CurrentDb
Set MyTable = MyDB.OpenRecordset("T_Installation_Variables")
MyTable.Edit
If ResetCounter < 5001 Then
NextCounter = MyTable("NumNextRecord") 'Master location
Else
NextCounter = MyTable("NumNextRecordSub1") 'Sub location
End If
NewDayCheck = MyTable("LastDateSelect")
'=================================================================
'The next lines diagnose and increments or resets the counter field
'according to evaluation results, and saves the value back into
'the table
'=================================================================
If NewDayCheck < NextDate Then
NextCounter = ResetCounter
If ResetCounter < 5001 Then
MyTable("NumNextRecord") = NextCounter 'Master location
Else
MyTable("NumNextRecordSub1") = NextCounter 'Sub location
End If
MyTable("LastDateSelect") = NextDate
ElseIf NewDayCheck > NextDate Then
Error 17
MsgBox ("System Date is earlier than last record date! Check system
date and update or call for help")
GoTo Next_Custom_Counter_Exit
Else
If ResetCounter < 5001 Then
MyTable("NumNextRecord") = NextCounter + 1 'Master location
Else
MyTable("NumNextRecordSub1") = NextCounter + 1 'Sub Location
End If
End If
MyTable.Update
MyTable.Close
Set MyTable = Nothing
'================================================================
'Message below for testing purpose, un-comment to see results
'================================================================
'MsgBox "Next available counter value is " & Str$(NextCounter)
Next_Custom_Counter = NextDate & Format(NextCounter, "0000")
GoTo Next_Custom_Counter_Exit
Next_Custom_Counter_Err:
MsgBox "Error " & Err & ": " & Error$
If Err = 94 Then
MsgBox "Error " & Err & ": " & Error$ & " You must enter an starting
number and date in the T_Installation_Variables Table"
ElseIf Err <> 0 Then Resume Next
End If
GoTo Next_Custom_Counter_Exit
Next_Custom_Counter_Exit:
Exit Function
End Function

 

[John W. Vinson]

I asked this before then got busy and never got back to it. Now I can't find
the response. So...

I need to create a field which will be an autonumber field however I want it
formated like yy-nnn. Example would be the current two digit year 08 - the
next available number. Right now we are at something like 653. So ideally I
would want to seed the autonumber portion or have it automatically add 1 to
the last number. The finaly result needs to be a combined text/number field
as it is passed to other areas and is my main reference number. The end
result needs to look like 08-653 and the next new entry must be 08-654, etc.

Thanks,

Bill

An Autonumber will NOT serve your need here; you'll need to maintain the
numbering sequence yourself.

You can use a text field for this pupose with a bit of code. Assign values to
the field by using VBA code on the Form that you use to enter data (yes, you
must use a form, tables have no usable events). One simple way would be to use
the Form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim IDYr As String, IDNum As Integer, vMax As Variant
IDYr = Format(Date, "yy") ' get 08, or current year
' Get largest existing ID for current year
vMax = DMax("ID", "yourtable", "ID LIKE '" & IDYr & "*'")
If IsNull(vMax) Then
Me!txtID = IDYr & "-001"
Else
IDNum = Val(Mid(vMax, 4) ' extract digit portion
If IDNum >= 999 Then
Cancel = True
MsgBox "Turn off the computer and go home until next year, out of IDs"
Else
IDNum = IDNum + 1
Me!txtID = IDYr & "-" & Format(IDNum, "000")
End If
End If
End Sub

 

[Bill]

John,

I think this is what I am wanting to do. When I'm using your code it is
erroring at

IDNum = Val(Mid(vMax, 4) ' extract digit portion

I've named my table IDNum and I'm not sure how the rest of that line
calls/reads??

Thanks,

Bill

 

[John W. Vinson]

John,

I think this is what I am wanting to do. When I'm using your code it is
erroring at

IDNum = Val(Mid(vMax, 4) ' extract digit portion

I've named my table IDNum and I'm not sure how the rest of that line
calls/reads??

I'm missing a second close parenthesis after the 4.

IDNum should NOT be your table name. IDNum is a VBA variable inside this
routine. The code assumes that there is a textbox on the form named txtID,
bound to the ID field (which could be called IDNum or whatever you like) - but
you can't set a TABLE to a value, only a FIELD.

 

[Bill]

Wow am I showing my "newbie" side or what?!! LOL. That works perfectly!!
Thank you. This is exactly what I needed.