Continuous Count

[WG_Clark]

Hi! I am a novice Access user.....creating a database (MSAccess2007) for a property management group



I have created a table "ResidentID"

Primary key is ResidentID - rather than autonumber, I chose text format (I want to create my own sequential identification)

I am looking to make the text format for ResidentID the year and then sequential number. ex. "2013-0001". next record would be "2013-0002"
Not sure where or how to tell access to do this (does this fall under validation rule?)

Also, I will be creating a form for this table. I would like the Property manager to be able to enter new residents and have the new record automatically populate the next sequential "ResidentID" number.

Any help would be most greatly appreciated!

[Missinglinq]

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
   If RecordsetClone.RecordCount = 0 Then
     Me. ResidentID = Format(Date, "YYYY") & "-0001"
  End If
If RecordsetClone.RecordCount <> 0 Then
  If Left(DMax("ResidentID ", "YourActualTableName"), 4) = Format(Date, "YYYY") Then
   Me. ResidentID = Format(Date, "YYYY") & "-" & Format(DMax("val(Right([ResidentID],4))", "YourActualTableName", "Left([ResidentID],4) = '" & Format(Date, "YYYY") & "'") + 1, "0000")
  Else
   Me. ResidentID = Format(Date, "YYYY") & "-0001"
  End If
  End If
End If
End Sub

Assuming that ResidentID is the actual name you're using, you only have to replace YourActualTableName with...that's right...Your Actual Table Name!

This kind of hack simply has to be in the Form_BeforeUpdate event, if this is going to be used in a multi-user environment; otherwise two users could each start a Record and be assigned the same ID number. Placing it in the Form_BeforeUpdate event insures that the assignment takes place in the last nanosecond before the Record is saved. I've had this kind of code running, in this event, in apps for well over a decade and I've never had a duplicate number assigned. The only drawback, with this, is the same thing; the number is assigned as the Record is saved, usually when moving to another Record, closing the Form or closing Access, itself, meaning that you won't see it, on a Single View Form, until you return to the Record.

If this is going to be a stand-alone app, i.e. one user using it on a single computer, you can move the code, in toto, to the Form_Current event. If you do that you just have to make one modification:

Directly under the Sub Header, before any other code, enter the line

If Me.NewRecord Then

Then immediately before the last line of the Sub, i.e.

End Sub

enter

End If

So the last two lines should be

End If
End Sub

Linq ;0)>