Auto Number

[cthai]

Hello -

I have a database in Access 2007 with an Auto Number - "FY2011-01" each time we add a record the number at the end of FY2011-## change.. however now it's FY2012 I would like to start with FY2012-01 instead of FY2012-305 is there a way I can set the record to start at 01?

 

[dhookom]

There is probably a way to do this. The solution would depend on how "each time we add a record the number at the end of FY2011-## change" which none of use know. There must be some code or something that does the math.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya cthai . . .

Using the Access Table Interface, an autonumber field is typically a [blue]LongInteger![/blue] What you show ([blue]FY2011-01[/blue]) is a field set as [blue]Text![/blue] ... so for starters, the field you prescribe is not an [blue]AutoNumber![/blue]

The question is ... how are you assigning the values for this field? All you need to do is to [blue]detect the year assignment change[/blue] and make the approrpriate adjstments. [blue]Your posting of this assignment code would be a speedie resolve to resolution ...[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

cthai . . .

The following should do. I had to makeup my own table and field names since they were'nt supplied by you. I used [blue]IDent[/blue] for the table name and [blue]NewID[/blue] for the fieldname. You'll have to substitute all proper names in [purple]purple[/purple]. Also ... since you show your using leading zeros, the code is setup to administer three sifnificant digits.

Now, in a module in the modules window, copy/paste the following code. Name the module [blue]modNewID[/blue]:

[blue]Public Function NextID(Yr As Long) As String
   Dim yrExist As String, idx As Long
   
   yrExist = Nz(DLookup("[[purple][b]NewID[/b][/purple]]", _
                        "[purple][b]tblIdent[/b][/purple]", _
                        "Val(Mid([[purple][b]NewID[/b][/purple]],3,4))=" & Yr), "")
   
   If yrExist <> "" Then
      idx = DMax("Val(Right([[purple][b]NewID[/b][/purple]], Len([[purple][b]NewID[/b][/purple]]) - InStr([[purple][b]NewID[/b][/purple]], '-')))", _
                 "[purple][b]tblIdent[/b][/purple]", _
                 "Val(Mid([[purple][b]NewID[/b][/purple]],3,4))=" & Yr) + 1
      NextID = "FY" & CStr(Yr) & "-" & Format(idx, "000")
   Else
      NextID = "FY" & CStr(Yr) & "-" & "001"
   End If

End Function[/blue]

To use the function you have to supply the year. This allows you to go back and make additions proper ... for any year. For the [blue]current year[/blue] you would use something like:

[blue]   Me.TextboxName = NextID(Year(Date))[/blue]

For [blue]2009[/blue] it would be:

[blue]   Me.TextboxName = NextID(2009)[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]