Custom autonumber with year and month

[Laura1]

Hi all,

I need to create an "autonumber" field in the following format:
FYYMMXX
Where F is a constant, YY is the year, MM is the month, and XX is an incremented number. So for example, the first record in August of 2013 would be "F130801".

I've been checking other threads but haven't been able to follow them to a resolution. I think there is a way to do this within my form... Any help is much appreciated...

 

[billmeye]

I'm assuming the user is entering a date into a bound control so you could put this code into the after update event for that data control and have it fire only if it is a new record (or you could do so if the autonumber is null etc.).

Dim MonthCount As String
If IsDate(Me.DateEntered) And Me.NewRecord Then
    MonthCount = Format(DCount("*", "tblyourTable", "Year([DateEntered])=" & Year(Me.DateEntered) & " AND Month([DateEntered])=" & Month(Me.DateEntered)) + 1, "00")
    Me.CodedAutonumber = "F" & Format(Me.DateEntered, "YY") & Format(Me.DateEntered, "MM") & MonthCount
End If

 

[Laura1]

Yes, a date is being entered, it's defaulted to today's date.
Ok, your code makes sense to me mostly, I have coded in other programs before, but I'm afraid my usage in Access has been mostly with the wizards so far. Where would I enter this code? Would it be in the expression builder of the default value for this field within the form?

 

[billmeye]

You would use the code in an [Event Procedure] which would be the choice Code Builder. If you are having today's date default then it will not fire since there is no data entry. You can place the code in the forms Before Insert event and it will fire immediately upon entering a new record. Make this slight change to replace all Me.DateEntered with Date(). Also, since it will only fire upon entering a new record you don't need to test for that.

Dim MonthCount As String
    MonthCount = Format(DCount("*", "tblyourTable", "Year([DateEntered])=" & Year(Date()) & " AND Month([DateEntered])=" & Month(Date()) + 1, "00")
    Me.CodedAutonumber = "F" & Format(Date(), "YY") & Format(Date(), "MM") & MonthCount