Customized ID Numbers

[Mohammed Abdul]

Gentlemen,
I am new to the access application development, with the help of my friend I have written code to generate custom ID numbers.
what is does:
1.creates numbers upon creating new record.
2.gets the year from the date(field) of event and combines with this auto number and stored in Field name "OCCID" in "EventTbl" table.
Question(HELP):
If the previous year event is recorded in current year example current year 2011 and event date year is 2010, the custom number stored in field "OCCID" is not getting 2010XXXX last number but stores 2011XXXX.
example: the last number in 2010 is 20100096 and for the year 2011 it is 20110007. If new record is created for the year 2010, the next new number is 20110008 instead of 20100097.
The code is mentioned below.

  
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me!OCCID) Then
    Dim dbs As Database
    Set dbs = CurrentDb
    Dim rs As DAO.Recordset

    Set rs = dbs.OpenRecordset("select max(CInt(Mid([OCCID], 5))) from EventTbl where Year([Date]) =" & Year(Me.[Date]))
    If IsNull(rs(0)) = True Then
        Me.OCCID = Year(Me.[Date]) & "" & Format(1, "0000")
    Else
        Me.OCCID = Year(Me.[Date]) & "" & Format(rs(0) + 1, "0000")
    End If
   
   'Me.OCCID = Nz(DMax("[OCCID]", "EventTbl", "Year([OccDate])=" & Year(Me.[OccDate])), 0) + 1

End If

Thanks in advance for who ever is putting the efforts to response or reply.
Regards,
Bari
   
   
End Sub

[jimatqsi]

"Date" is a keyword (function name) in Access. Date() retrieves the current system date.

Therefore, "Date" is a very poor choice for the name of a textbox or any other object on a form. Change the name of your text box (if that's what it is) from Date to txtDate and then change the references in your code from Year(Me.[Date]) to Year(Me.[txtDate]) and all will be well.

This illustrates a good practice that should always be followed in Access or any other language. Use identifying prefixes or suffixes (I prefer Date_txt, but txtDate seems to be more common) on each object you place on a form or report. _cbo for combo boxes, _chk for checkboxes, _list for listboxes etc... Here is one list of many you could use as a guide .. http://en.wikipedia.org/wiki/Leszyns...ing_convention

Jim

[ADezii]

You would be looking at something similar to:

Private Sub Detail_DblClick(Cancel As Integer)
If IsNull(Me!OCCID) Then
  Dim dbs As Database
  Dim rs As DAO.Recordset
  
  Set dbs = CurrentDb
  Set rs = dbs.OpenRecordset("SELECT MAX(CInt(Mid([OCCID], 5))) FROM EventTbl WHERE Year([Date]) =" & Year(Me.[txtDate]))
  
  Me.OCCID = IIf(IsNull(rs(0)), Year(Me.[txtDate]) & "" & _
             Format(1, "0000"), Year(Me.[txtDate]) & "" & Format(rs(0) + 1, "0000"))
             
  rs.Close
  Set rs = Nothing
End If
End Sub

[Mohammed Abdul]

Thanks Jim and Adezii for your efforts and suggestion, I will just change it and update you all.

Regards,

Bari

[Mohammed Abdul]

Yapee!!! IT IS WORKING....
I changed the Date Field to DATE_OCC, but there was as Error so
the only thing I changed is Year[DATE_OCC] in the code in Line 7.
Thanks once again......

If IsNull(Me!OCCID) Then
  Dim dbs As Database
  Dim rs As DAO.Recordset
 
  Set dbs = CurrentDb
  Set rs = dbs.OpenRecordset("SELECT MAX(CInt(Mid([OCCID], 5))) FROM EventTbl WHERE Year([Date_OCC]) =" & Year(Me.[Date_OCC]))
 
  Me.OCCID = IIf(IsNull(rs(0)), Year(Me.[Date_OCC]) & "" & _
             Format(1, "0000"), Year(Me.[Date_OCC]) & "" & Format(rs(0) + 1, "0000"))
 
  rs.Close
  Set rs = Nothing
End If
End Sub

[NeoPa]

@Mohammed
Although you can refer to the first field in your recordset as rs(0), it would make more sense, and make your code clearer, if you used the named format instead. EG. If your field is named [ID] then you could refer to it instead as rs("ID").

Congratulations anyway on getting a working solution :-)

PS. You may now want to consider Marking a Post as 'Best Answer' now you have the solution.