How to generate an incremental number that resets? (preferably to the fiscal year)

[Micron]

As my signature says, doesn't work doesn't help. If we're talking about a piece of code, post what you tried. I could only test with a small data sample but it worked. You could also post a zipped copy of a db that we can work with.

[orange]

Did you check out the links I suggested?

[johnrohan]

As my signature says, doesn't work doesn't help. If we're talking about a piece of code, post what you tried. I could only test with a small data sample but it worked. You could also post a zipped copy of a db that we can work with.

I tried kd2017 and June7 codes exactly as they posted them. In June7ths case, I literally copy and pasted the exact same code, then made my table, field, and forms names match to it.

I also copy and pasted your codes, but I think I was off because I don't understand how the fields relate. I assume "sequence" is the field with the incremental number, "GL quick query" is a query? Or maybe it's your table? But I'm not sure what to do with "Year([Transaction Date])".

Did you check out the links I suggested?

I did - one was a little over my head and didn't explain how to reset the sequence. The other one suggests this code: Nz(DMax("NumberField", "TableName", "DateField = " & Year(Date())), 0) + 1 which is similar to what Micron posted, but I'm hung up on how the date field is used there. Also where to put the code? What is the context? Build an expression in the table, in the default value line of the field? Add it to the "before update" event on a form? or what?

Yes, I know just enough about Access to get myself in a hole, but not enough to get myself back out.

I can't send anyone a copy of this database, because it's on a sensitive government system I can't copy anything from, even though the only data in it so far is sample data.

[June7]

How I use my procedure - user clicks a button 'Add New Sample' which calls the NewSample function and opens form to the new record:
strLabNum = NewSample
DoCmd.OpenForm "Sample", acNormal, , "LabNum='" & strLabNum & "'", , , strLabNum & "," & "Login"

There is code behind Sample form that allows user to abort the new record data entry. It retains the record but makes sure fields are set to null, except for the LabNumber field. This existing record will then be retrieved for the next user who logs in a new sample. This way I avoid gaps in sequence and never had an issue with simultaneous users generating the same LabNumber. That is not an impossibility but the new LabNumber and its new record (or the retrieved existing record) get committed to table so quickly it is highly unlikely. We are a small office, only about a dozen users for this db.

Post your attempted code for analysis. Do you have Access at home?

You can't attach zip files to an email and send to your personal email?

[Micron]

I also copy and pasted your codes, but I think I was off because I don't understand how the fields relate. I assume "sequence" is the field with the incremental number, "GL quick query" is a query? Or maybe it's your table? But I'm not sure what to do with "Year([Transaction Date])".

Yes, those are my names and you pretty much have figured them out.
GL quick query is a table (not named by me). Sequence is the field that contains your sequential number (e.g. 2017-005)
[Transaction Date] is the table field that contains the date upon which I presume you need to base the Max sequence number BUT I presume the year portion of the current date has to be the same in that table. Otherwise, you could be looking up the Max sequence number from some date from years ago.

I'm seeing that you've been given several methods and apparently have tried them all without success. This tells me that either you're not grasping the concepts thus can't relate them to what you have, or we're off base because of the lack of sample data or a db of yours to work with. Surely you can mock up something that is close enough to what you need, that you can relate to and doesn't reveal any secrets. A find and replace can turn sensitive data into Donald Duck or Ford or apple pie or whatever. I think the next step is going to have to be you providing a db with enough test data for us to work with. At least, that's my take. I've spent days helping people solve issues in this forum, but they gave me something to work with. We are approaching two dozen posts and don't seem to be getting anywhere.

[kd2017]

I uploaded my database if you want to take a look at it as an example. I am using access 2016...

[johnrohan]

I uploaded my database if you want to take a look at it as an example. I am using access 2016...

Hey thanks!! And it works! I compared our code side by side and I notice you did add a couple things to the first line of the macro that might have made the difference.


Well, I wish I had seen that earlier. I spent 8 hours trying to crack this nut, and drawing from 6 different methods, and managed to reinvent the wheel a different way. I think your solution was more elegant, so I might end up using it, but for everyone's awareness here's how I finally did it:

1. I set up the fiscal year in a text field in my Tasks table, using this as the default value:
=IIf(Month(Date())<10,Year(Date()),Year(Date())+1)

2. I made another field called [identifier] in the same table.

3. On the form, I then coded this into my onClick event for my "Add new record" button:
DoCmd.GoToRecord , , acNewRec
If Me.identifier = 0 Or IsNull(Me.Identifier) Then
Me.identifier = Nz(DMax("[identifier]", "Tasks"), 0) + 1
End If

4. Then on my form, I made a textbox where I put this in the control source:
=[FiscalYear] & "-" & Format([identifier],"0000")

At first, it capped out at 10, which was driving me nuts. Then I realized my [identifier] field was a text field. Changed it to a number field instead and now it works past 10. Only downside to this method is that it won't generate this number when a new record is entered in the table, only when the button is pressed on the form. Ideally I would prefer it created the number whenever a new record was generated, no matter how it was created; that just feels "cleaner" to me. But since everyone but me would be using the form, this is a 90% solution I can live with, but I'll experiment with kd2017's method because it may make more sense. If anyone has a suggestion how to improve this method, or possible pitfalls, let me know.

[Micron]

it won't generate this number when a new record is entered in the table

It can if you create the value on the form's BeforeInsert event, but the danger is that if the record creation is cancelled or fails, you can be left with orphaned values in the table. They would not be associated with any record, thus you'd appear to lose your sequencing.

[kd2017]

Hey thanks!! And it works! I compared our code side by side and I notice you did add a couple things to the first line of the macro that might have made the difference.
... I'll experiment with kd2017's method because it may make more sense. If anyone has a suggestion how to improve this method, or possible pitfalls, let me know.

I did add the condition to check if the TaskNumber was null, but that shouldn't have caused any problems on your end. It might have been a nesting issue. I find macros a little confusing to work with in that regard, nesting levels between blocks can be difficult for me to see. I added that condition with the idea that the whole routine would function more like an advanced 'default value', so if the user decides to input their own TaskNumber that the macro wouldn't overwrite it.

I like the Data Macro solution my self because it's not dependent on any form's code. Whether that matters or not, I don't know. But keep in mind that macros are less powerful than vba.

One caveat, because this macro wont run until after the user tries to insert the data into the table it wont display a TaskNumber until after the data input is done. You might change the "Default Value" on the field to say something like "(Auto)" so that it will display that to the user instead of a blank until the macro calculates the TaskNumber. If you did that you would need to tweak the macro's top level conditional to check for this default value so it knows to calculate a TaskNumber.

[ssanfu]

I like the Data Macro solution my self because it's not dependent on any form's code. Whether that matters or not, I don't know. But keep in mind that macros are less powerful than vba.

But be aware that by using a data macro , if you/someone decides to convert to SQL Server/ MySQL/Oracle/etc BE, these dB engines do not support data macros in tables.

[orange]

Steve,
If you are updating to SQL server, Oracle etc, wouldn't you convert an Access data macro to check constraint or whatever(trigger)?
I realize if isn't an automatic conversion, and maybe that's what you're saying --just checking.

[June7]

I went my route so record is immediately committed to table with new identifier to reduce possibility of another user generating the same identifier. I still had to offer user opportunity to abort in case they noticed something wrong with the submittal. Therefore code to search for existing LabNumber in a blank record. I had to insure there would be no gaps in sequence, every number had to be accounted for. I even have Void Number process.

I also needed to immediately show the new identifier to the user so they can write onto the original submittal paperwork.

[kd2017]

But be aware that by using a data macro , if you/someone decides to convert to SQL Server/ MySQL/Oracle/etc BE, these dB engines do not support data macros in tables.

At the risk of getting slightly off track, could you achieve the same thing with a "trigger"? I have never used them before so I am just curious.

I went my route so record is immediately committed to table with new identifier to reduce possibility of another user generating the same identifier. I still had to offer user opportunity to abort in case they noticed something wrong with the submittal. Therefore code to search for existing LabNumber in a blank record. I had to insure there would be no gaps in sequence, every number had to be accounted for. I even have Void Number process.

The macro route would also reduce the possibility of another user generating the same identifier as it isn't generated until the moment the record is inserted. I can see where this would be a problem with OP's solution in post #22.

[ssanfu]

@ orange
@ kd2017

I am still learning about SQLSE and haven't gotten to triggers yet.
My point was that using an Access Data Macro to create an auto-incrementing, auto-resetting (by year) number is not the best decision if there could be a possibility of upsizing the BE. I would design so I didn't have to do a big re-write/modification because of one field.

Using an Access Data Macro for a one off, personal use dB would be ok, but it still seems like a bad idea... (I never use macros - too limiting)

This is my personal opinion..... You may do what you want .... until I become dictator of the world....