Link to home
Start Free TrialLog in
Avatar of kdeutsch
kdeutschFlag for United States of America

asked on

Autogenerate number in specific format

I originally got a question aswered her that gave me the below code for autogenerating a number in Xcel that in a specific format that I could then pull into Access.  BUt now my question is can I do this same thing in Access using a text field.  That when they add a new job number it autogenerates the new number in the format.  To do this it would also have to look at the previous filed to generate off of that number so as to ensure they are sequential.  Then when the fiscal year comes around it would also have to reset itself so that it starts over agian.  Here is example of format.
07-001  First 2 numbers are year which turns over every OCt 1st, rest is just a sequential number
07-002

Thanks

Sub AccessNr()
Dim A As String
    Nr = Cells(1, 2).Value
    Nr = Nr + 1
    If Nr >= 1000 Then Nr = 1
    Cells(1, 2).Value = Nr
    If Month(Now()) = 10 Or Month(Now()) = 11 Or Month(Now()) = 12 Then
        If Cells(1, 4).Value = 0 Then
            Cells(1, 4).Value = 1
            Cells(1, 2).Value = 1
        End If
        A = Format(year(Now()) - 1999, "00") + "-" + Format(Cells(1, 2).Value, "000")
    Else
        A = Format(year(Now()) - 2000, "00") + "-" + Format(Cells(1, 2).Value, "000")
        Cells(1, 4).Value = 0
    End If
    Cells(1, 3).Value = A
End Sub
back to top
Avatar of sigs66
sigs66

The simipliest one I can think of is this
= dmax("FieldName", "tableName" ) + 1

The dmax gets the largest number used and 1 is the increment.

Just make this the default value property and maybe lock the field and remove the tab stop. The only problem with this is if there is more than one user. It would be possible for them to get the same number

Cheers
Steve
If your table is called tblTest and your field is MyNum, use this in the form used to edit your table:

Private Sub Form_BeforeInsert(Cancel As Integer)

    Dim strPrefix As String
    Dim strNum

    strPrefix = Format(DateAdd("m", 3, Date), "yy")
    strNum = Nz(DMax("MyNum", "tblTest", "MyNum Like '" & strPrefix & "-*'"))
    strNum = Format(Val(Mid(strNum, 4)) + 1, "0000")
    Me.MyNum = strPrefix & "-" & strNum

End Sub

Cheers!
(°v°)
Avatar of clarkscott
Unfortunately, Steve is correct is stating that if multiple users are generating records - it's impossible to get this accurate.

The 'Autonumber ' process, whether Access or SQL (or Oracle...) is based on the explicit rule that there will be no duplicates.  Therefore, if a user starts to create a record, the Autonumber is generated.  Since multiple users may be creating records at the same time, their 'autonumber' is also generated.  Now, let's say the first user decides not to create the record.  It's already too late to change the subsequent user's generated autonumber.  The 'autonumbers' will never be sequential with no 'holes'.   Also - what happens to the order of these numbers if a record is deleted?

You must change your way of thinking and using auto-generated numbers.
If you want a sequential count of records, you will have to create a report that generates a 'running sum' field and populates dynamically.

Any attempt to try to absolutely control this will be tons of code and lots of frustration

Scott C

To get around the possibility of creating 2 records with the same number, you could just save the record as soon as you create the number. The odds of 2 people both making a record in the same second isn't very likely.

Have the person enter all the data for the record on the form. Then click save, which would run the code to generate the number and save the record at the same time. If you have large numbers of people creating large numbers of records all you would need to do is wait a second or two and check to make sure you didn't create a dupe, and if you did just give the record the available next number, and redisplay the form for the user.
Hi All

Thinking about this last night. I Came up with 2 solutions... firstly chances are this number is generated  in a form... so you could put it in the code before the before insert property

'----------------------------------------------------------------------------------------------
Private Sub Form_BeforeInsert(Cancel As Integer)
On Error GoTo err_code

me.idtextbox = dmax("FieldName", "tableName" ) + 1

exit_code:
    Exit Sub

err_code:
    MsgBox "Error: " & Err.Number & ", " & Err.Description, , "Auto Number Error"
    Resume exit_code
End Sub
 '----------------------------------------------------------------------------------------------
This code goes into the form used to generate the new number

because this generates the code the instant before the record is inserted it reduces to almost 0 the chance of a duplicate, and I think if someone tried to insert a record at the same time one of them would reject and they would just hit the enter key again and iinsert it again...  but i haven't tested that.

Cheers  
sigs66,

The chat you are having with clarkscott and eddiej about gaps and counter clashes is interesting, but you are missing one tiny detail. kdeutsch doesn't want a numeric autonumber, but one following this format: "07-002" using the fiscal year as prefix.

Your code would set Null as first ID (because DMax() would return Null) and raise an error if a record was manually created (because "07-002" + 1 is a type mismatch).

(°v°)
Opps that what I get for not paying attention

give me 10 minutes
this is a nasty expression...

=cstr(format(now, "yy")) & "-" & cstr(nz(dmax("clng(right([field],3))", "table",  "left([field],2) = cstr(format(now, 'yy'))"),0)+1)

Split up into two parts

1/ the "cstr(format(now, "yy"))" is the two digit year - I am assuming that you want to us the current year not the max year in the system - this gets around any issues with pre year 2000 data

2/ the "cstr(nz(dmax("clng(right([field],3))", "table",  "left([field],2) = cstr(format(now, 'yy'))"),0)+1)" gets the maximum value for this year.  If there is no maximum value it returns 0 and then adds 1. Field and Table will need to be replaced appropriate names from your database.

the end result is you end up with a figure like "07-800"

'----------------------------------------------------------------------------------------------
Private Sub Form_BeforeInsert(Cancel As Integer)
On Error GoTo err_code

me.idtextbox = cstr(format(now, "yy")) & "-" & cstr(nz(dmax("clng(right([field],3))", "table",  "left([field],2) = cstr(format(now, 'yy'))"),0)+1)

exit_code:
    Exit Sub

err_code:
    MsgBox "Error: " & Err.Number & ", " & Err.Description, , "Auto Number Error"
    Resume exit_code
End Sub
 '----------------------------------------------------------------------------------------------
or you could make the expression the default value in the table
Yeah! Almost there. You now only need to take into account that October 07 = fiscal year 08, and change CStr() to Format() for those nasty leading zeros. <*grin*>

And no, a one-liner as Default Value is not possible. Try it! DMax() and Nz() are not really JetEngine functions.

(°v°)
ASKER CERTIFIED SOLUTION
Avatar of sigs66
sigs66

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kdeutsch

ASKER

WOW, you guys are awesome, If I understand this correct I would put this code in my form that opens in a text box.  Now would that text box be seperate from the one that was created to store the actual number.   An is this looking up the last number to generate the new number or how is that working.
Thanks
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for the help, You both did a great job.  I used portions of what you each did but solved a different way.  I created a table that calls a function that changes the year and adds the sequential number to it.