Form needs to be auto-populated with next sequential number

[stanley721]

I have a form that I use to enter new data. The data is sequentially numbered with the year as the preceding value like 13-001. Each time I go to enter new data, I want the tracking number (13-001) to be the next sequential number, but don't want to use autonumber.


Then I want that new data to be placed in the table. The tracking number needs to reset at the beginning of every year. I am using Access 2010. I've read many post and forums on the net and none seem to be getting me the results I need. I've attached a screen capture of my form and table.

[RuralGuy]

I'll start by saying your TaskNumber is a string rather than a number. It can always be displayed with leading zeros using the Format() function. See if this link helps you a bit: http://www.baldyweb.com/CustomAutonumber.htm

[stanley721]

But where does that get input. At the Form level. The thread you referred me to said you can't accomplish what I want on the table level so do yoput it in the form? I'm almost there. I am just missing a few pieces to make the light bulb turn on.

[RuralGuy]

Yes, with code at the Form level. You could use the Current Event and check for Me.NewRecord.

[Missinglinq]

In a multi-user environment, using the Form_Current event to do this could lead to duplicate numbers; one user starts to enter a New Record, has the ID assigned, and before they complete the Record another user starts entering a New Record, gets assigned the same number. Better, I think, to use the Form_BeforeUpdate event. It fires at the last possible moment before a Record is saved, and in over a decade of doing this with multiple users I've never had a duplication problem.

I've used this hack to accomplish this very thing:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then
   
  If RecordsetClone.RecordCount = 0 Then

     Me.TrackingNumber = Format(Date, "YY") & "-001"

  End If

If RecordsetClone.RecordCount <> 0 Then
 
  If DCount("*", "YourActualTableName", "Left([TrackingNumber],2) = '" & Format(Date, "YY") & "'") > 0 Then
                                                                            
    Me.TrackingNumber = Format(Date, "YY") & "-" & Format(DMax("Right([TrackingNumber],3)", "YourActualTableName", "Left([TrackingNumber],2) = '" & Format(Date, "YY") & "'") + 1, "000")
   
   Else
   
   Me.TrackingNumber = Format(Date, "YY") & "-001"
  
  End If
  
  End If

End If

End Sub

You'll need to replace TrackingNumber and YourActualTableName with the actual names for your Field and underlying Table.

Linq ;0)>

[NTC]

As you are finding out - this isn't as simple as one might think.

Keep in mind that what one stores and what one displays can be different.

the 13 is easy because it is the 2 right digits of the year - and will auto update as the years go by presuming it is based on the computer's date.

the 001 is text as RG notes. but you can store integer 1. and then count the digits and stuff none, one or two 0s at the point of display. integer is the easier field type to find the max in order to auto increment +1. of course that max has to be a year specific max.....

and it a multi user situation you'll want to force the write to the table asap to minimize the probability of multiple users generating the same value..

[stanley721]

This is heading in the right direction. But now what I really like for it to do is to display that next sequential number in the field on the form as the person is typing in the data in the other fields. As it is not, the peson inputs their data and exits, not knowing what the assigned number is. I need that because the user then writes that assigned number at the top of the hard copy record. We are basically entering a log of documents that gets filed, and the hard copies have the sequential number written on them for tracking purposes.
Can this be done?

[Missinglinq]

Is this database going to be used by a single user, or are multiple users going to be using it at the same time? You'd need to save the Record immediately, if this is going to be used in a Multi-user environment, to prevent duplicate ID assignments. But this can present problems if you have Fields that are required, as they will not yet be populated. But it may work for you.

You would place the code I gave in the Form_Current event, and add this line just prior to the End Sub line:

If Me.Dirty Then Me.Dirty = False

Linq ;0)>

[stanley721]

Sorry, it is doing exactly what I need, and I tested it to make sure duplicates can't be used and they couldn't. A co-worker and I tried multiple times to enter test dat at the same time and it would never assign us duplicate values.

[Missinglinq]

Glad you got it working!

Linq ;0)>

[stanley721]

Alright, not so fast. It isn't working correctly. I tested it on my home computer where I could change the date, and as I changed years the tracking number did not reset. It continued with the next sequential number, but it did change the year. So it would be 12-036 then 13-037 then 14-038. Ugh, just when I thought I was making progress.

[Missinglinq]

Don't really know what to tell you. I tested the code in the exact same manner and it performed as expected, changing to the new year when I advanced my system's clock/calendar to 2014 and resetting the ID number to 1.

Now the system was set up to generate a new ID number assuming that the Record being created was supposed to automatically be given an ID based on the current year. I notice that you give an example of 12-036 and I suppose going backwards in time could have created a glitch.

Linq ;0)>

[Missinglinq]

Once again, don't really know what to tell you. Just finished doing more testing, actually going back in time then forward, then backwards, resetting the system clock each time, and the code works exactly as expected.

If you post your actual code I'll be happy to look at it when I get a chance.

Linq ;0)>

[stanley721]

It could be my table. I've tried so many different things I may have several variations combined. I tried to keep each database separate when I would change things. I will send screenshots.

[Missinglinq]

You have TrackingNumber defined, in your Table, as an Autonumber! It has to be defined as Text, in order to have the hypen between the year and the rest of the Tracking Number.

Also, in the line

Me.TrackingNumber = Format(Date, "YY") & "-" & Format(DMax("Right([TrackingNumber],3)", "YourActualTableName", "Left([TrackingNumber],2) = '" & Format(Date, "YY") & "'") + 1, "000")

you're still using

YourActualTableName

as the name of your underlying Table/Query, instead of the actual name!

Linq ;0)>