sequential numbering <hard one>

[roystreet]

Hello,
I have three fields that are: Standard_Number + FY + Unqiue_Number they make up a file number. These three fields are stored separately in the table as well are concatented on before update into a field called regional_number. My problem is that the user needs to be able to press a button and it will give the next sequential number. The last field must always be 4 digits. I have it set up that it will will place the leading zeros if need be. I have following code:

Private Sub MakeNumber()
DoCmd.GoToRecord , "", acNewRec
If Me.NewRecord Then
Me.Unique_Number = DLookup("Format(Max(CInt([DAA-MasterTable].Unique_Number))+1,'0000')", "DAA-MasterTable")
End If
DoCmd.GoToControl "Employee_Name"
End Sub

The button will run this code and it will insert the next number in the unique_number field. This is great!! But the problem comes into play when the new fiscal year comes upon us and the number needs to begin over again. For instance, currently the numbering would be AL-04-0080. But after 0080 here comes (and then the next FY comes upon us) FY 05 so it should be AL-05-0001. But what happens is the number will show up is AL-05-0081. The code is only looking at the unique_number portion...Is there a way I can get it to look at the whole number??

Thanks,
---roystreet

 

[roystreet]

Great Job!

Hello Pat,
I Greatly appreciate your help!!! I just got the chance to look at it now (13:49 Alaskan Time - If that gives you any idea where I'm at!) Anyway, I have to say That's slick man!! I never use that word, but currently it looks like it's running smooth! The FY is set to a default value, but I went to it and changed once the form was opened and then clicked the button that runs makenumber and it works great! The only thing I see that could be changed is if there are no records for a fiscal year yet, it will not generate a new number.
Example: there are no entries for FY 06, I go in and choose 06 as the FY and then run makenumber - It just leaves the field blank. But as soon as there is one record in there for FY 06 it will now generate the next number perfectly. So, I will have to think about it a little on how to fix that?? Not sure. But this is one of those situations where you just want to say "YES!!!" It works. I've been trying to figure this out and I kept getting close.

---roystreet

I'm going to test it some more....

 

[roystreet]

Pat,
In this case how would it affect it if by using DMax instead of DLookup?? I'm just trying to understand it more.

Thanks,
---roystreet