Record control number that can be refreshed annually?

[progressiverookie]

How do I create a record control number that I can start over at 00001 every January 1st? Example number would be: 200400001 for the first record entered in 2004. In 2005 the number would be 200500001, 200500002, 200500003, and so on? Please explain for a rookie! Thanks in advance.

 

[AJN2004]

I think the best bet would be to create a VBA module that runs each time the database is opened. Something like this:

Create a new module in the Visual Basic editor and copy the global variables into it.
------
Global GlobalDat, GlobalRecNr
------
On your start form double click the "on open" statement in the properties dialog under the action tab, switch to the visual basic editor. Enter the following code:
-----------
Private Sub Form_Open(Cancel As Integer)
Dim Dba As DAO.Database, Rst As DAO.Recordset
Dim Dat

Set Dba = CurrentDb

Set Rst = Dba.OpenRecordset("TABLENAME")

Dat=year(Date)

Rst.MoveLast

If left(Rst("FIELDNAME"),4) < Dat then
GlobalDat = Dat
GlobalRecNr = "00001"
Else
GlobalDat = Left(Rst("FIELDNAME"),4)
GlobalRecNr = Right(Rst("FIELDNAME"),5)

End If

End Sub
…….

You will need to modify or write code to increment the record number each time a new record is created:

Taking for granted that you have added a button, using the buttons assistant, to create the new record. In the development view of the form, select the add new record button, go to the properties dialog action tab and click on the "…" to the right of the line, this brings you into the VB editor. After the " DoCmd.GoToRecord , , acNewRec" statement add the following:

If GlobalRecNr <>"00001" Then
GlobalRecNr = GlobalRecNr + 1
End If

Me.FIELDNAME = GlobalDat & GlobalRecNr


I hope this works for you

 

[progressiverookie]

AJN2004,

Thanks for the reply.

I have already created a command button that calls the following function on click. What is does is searches the table for the highest number and adds one.

***START CODE***
Public Function NewCustID() As Long

On Error GoTo NextID_Err

Dim lngNextID As Long

'Find highest Employee ID in the tblPracticeEmployeeData table and add 1
lngNextID = DMax("[Control Number]", "Case Files") + 1

'Assign function the value of the Next ID
NewCustID = lngNextID

'Exit function now after successful incrementing or after error message
Exit_NewCustID:
Exit Function

'If an error occurred, display a message, then go to Exit statement
NextID_Err:
MsgBox "Error " & err & ": " & Error$

Resume Exit_NewCustID

End Function
***END CODE***

Keep in mind, my "RCN" is actually two fields on the form. The year, and the increment portion, 000001.

I tested your code by changing the system clock to January 1, 2005, and it did not work. I incremented the last number in 2004, just like usual.

Thanks for your help.

Jeremy

 

[hymn]

if its for a single user you could try the code below

Private Sub Form_Current()

t_year = Format(Now, "yyyy")

End Sub

Private Sub t_year_Exit(Cancel As Integer)
'AAAA = TABLE NAME
'frmGGGG = Form Name
't_Year = FIELD NAME OF YEAR
't_No = FIELD NAME NUMBER FIELD
't_YearNo = FIELD NAME OF YEAR AND NUMBER
'EEEE = FIELD NAME

'to set sequential number
Set Db = CurrentDb()
Set rds = Db.OpenRecordset("SELECT distinctrow Max(tbl_AAAA.t_No) AS MaxOft_No FROM tbl_AAAA where ((tbl_AAAA.t_year='" & Forms![frmGGGG]![t_year] & "'));", DB_OPEN_DYNASET)
Me![t_No] = IIf(IsNull(rds![MaxOft_No]), 1, rds![MaxOft_No] + 1)
Me![t_No].Requery
Me!t_YearNo = [t_year] & [t_No]


EEEE.SetFocus
End Sub

Hope this helps
Hymn