Custom Auto Field on form

[tareyj8569]

Hi there,

The goal is to still have my normal primary key that goes up by 1, but I want another field that will look like ABC14-0001 and goes up by one every record. The 14 is the year and I would like it to reset back to ABC15-0001 but in October1st and so on every year.



I would like it to show on the form.

I have been looking for days and tried multiple sites, if any one can help me with this I would really appreciate it. I do not care how it is accomplished as long as it works=P.


Thanks for your time and help.

[ItsMe]

Maybe the on current event of a form. This example uses a textbox control named txtResult and assumes the field you are using has a control named MyID

Dim strPred As String
strPred = "ABC" & Format(Date, "yy") & "-"
Me.txtResult.Value = strPred & Me.MyID.Value

[tareyj8569]

Thanks for responding, thats not 100% what I am looking for. What I am looking for is actually a few things.
1. for it to have ABC
2. 2 digit year 14-
3. to have its own count 0001
4. than in October not January to restart the count
So on October 1st it would look like ABC14-0001.
I am not the best at access but am trying to learn if there is a way i can use what you suggested to get what I am trying to accomplish i am defiantly listening.

[ItsMe]

First thing would be to make sure that, come Oct 1st, Everyone agrees that October 15, 2014 is FY2014 and not FY2015.

I would probably dedicate a table to creating the sequence. You could use an Autonumber field to create the number sequence part. Another approach would be to use DMax to find the greatest and +1 to append. Index the column that is the series and use error trapping for your append process to catch possible duplicates (in case two users retrieve the same DMax).

The trick is resetting the count every FYS. I would run an automated process (once a year) with task scheduler to delete the records within the counter table and do a compact and repair or just compact. Since you need an automated process and probably do not want to delete all records while multiple users are working, might as well use Autonumber and run the maintenance at night 0:30 hrs Oct 1.

With the autonumber part addressed, it is just a matter of adjusting the expression to return 14 on Feb 10, 2015.