Sequential number generator that resets (1 Viewer)

aclockworkgod

New member
Local time
Today, 05:03
Joined
Sep 12, 2019
Messages
5
Hello all! This is my first post and I want to preface this with noting that I am new to coding in VBA. With that said... I need some help.

I am looking to create a sequential number generator/form field that:

1. Has a range (ex. 100-300)
2. Identifies if the number has already been used based on a table
3. Spits out the next record
4. Does not create a duplicate record on the table
5. Resets the range to be used for the next day

I have the format of the record structure I need to be recorded on the table. The only trouble I am having is figuring out a way to get a useful sequential number generator that references the last record to create the new record based on the next available number in the range.

Note: This will be a multi-user data input database, so the potential for many users to be in the database at one time is likely.

It may be asking a lot of Access 2016, but I hope you all will be able to assist. Thanks!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:03
Joined
Feb 19, 2013
Messages
16,553
a common enough question - investigate what is typically called 'dmax+1'
 

Mark_

Longboard on the internet
Local time
Today, 02:03
Joined
Sep 12, 2017
Messages
2,111
Just to make sure, having an autonumber primary key doesn't meet your needs?

Is this for something like the order number on a sales order? Does it need to be visible prior to saving a completed transaction?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:03
Joined
May 7, 2009
Messages
19,169
you need a separate table to save the sequential number generated. (tblSeqn)

fields:
year (long) "current year"
Seqn (long)

to get the next sequence (based on year):

=nz(dmax("seqn", "tblSeqn", "[year]=" & year(date)) , 0) + 1
 

aclockworkgod

New member
Local time
Today, 05:03
Joined
Sep 12, 2019
Messages
5
Just to make sure, having an autonumber primary key doesn't meet your needs?

Is this for something like the order number on a sales order? Does it need to be visible prior to saving a completed transaction?

Mark,

It would be similar to the question posed in this thread: 'https://access-programmers.co.uk/forums/showthread.php?t=105462

I'm not sure if this can be accomplished using an auto-number. There's another more specific example thread that I'll need to find again.
 

Mark_

Longboard on the internet
Local time
Today, 02:03
Joined
Sep 12, 2017
Messages
2,111
Mark,

It would be similar to the question posed in this thread: 'https://access-programmers.co.uk/forums/showthread.php?t=105462

I'm not sure if this can be accomplished using an auto-number. There's another more specific example thread that I'll need to find again.

As outlined in that thread, having a sequential number (other than an autonumber) is seldom needed and requires that you commit the record as soon as it is generated to avoid duplicate issues.

In that thread the user simply wants a number that makes people feel good to deal with. Are you in the same situation? Will that threads technique not work for you?
 

aclockworkgod

New member
Local time
Today, 05:03
Joined
Sep 12, 2019
Messages
5
I think I may have found what I was looking for here: tek-tips.com/viewthread.cfm?qid=837296

Do we think it is a feasible way for a multi-user db to operate and have the range be reset daily?
 

June7

AWF VIP
Local time
Today, 01:03
Joined
Mar 9, 2014
Messages
5,423
Very similar to example code in post 6 link.
 

aclockworkgod

New member
Local time
Today, 05:03
Joined
Sep 12, 2019
Messages
5
Very similar to example code in post 6 link.

Thanks June! and thanks all!
I will be reviewing the code I have implemented throughout the day and into tomorrow to see if it's what we'll need. I appreciate everyone's help on this and will look further into post 6 if I run into any trouble. Thanks again for the quick responses!
 

missinglinq

AWF VIP
Local time
Today, 05:03
Joined
Jun 20, 2003
Messages
6,423
...requires that you commit the record as soon as it is generated to avoid duplicate issues...

Actually, in a multi-user environment, the number is usually committed at the last possible moment (i.e in the Form_BeforeUpdate event) to avoid duplication.

Committing the Record as soon as it is created is only needed if it is mandatory that the number be visible to the user before the entire Record is completed.

Using this latter method can also present a problem if there is validation that needs to be done involving two or more Controls...such as making sure that an 'end date' is later than a 'begin date,' or that the total of two Controls falls within a certain range, etc.

Linq ;0)>
 

Mark_

Longboard on the internet
Local time
Today, 02:03
Joined
Sep 12, 2017
Messages
2,111
Actually, in a multi-user environment, the number is usually committed at the last possible moment (i.e in the Form_BeforeUpdate event) to avoid duplication.

Committing the Record as soon as it is created is only needed if it is mandatory that the number be visible to the user before the entire Record is completed.

Using this latter method can also present a problem if there is validation that needs to be done involving two or more Controls...such as making sure that an 'end date' is later than a 'begin date,' or that the total of two Controls falls within a certain range, etc.

Linq ;0)>
I am guessing you mean "Generated and committed"?

You are right, if they don't actually need the number until after they've saved it, waiting to the last possible moment works best as it avoids the whole "What if they don't want to save" issue. Too often, people want to see these kinds of numbers while they are working with the record. Doesn't really help them any, but they like it for some strange reason...
 

June7

AWF VIP
Local time
Today, 01:03
Joined
Mar 9, 2014
Messages
5,423
I faced this same quandary. User needed to immediately write generated ID on a submittal document. Tried waiting to end of data input but couldn't get record to display long enough after ID generated. So gave up and implemented process that immediately generated and committed and also incorporated allowance for cancel option. It's shown in my example code in linked thread.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:03
Joined
Feb 19, 2013
Messages
16,553
Tried waiting to end of data input but couldn't get record to display long enough after ID generated
In these situations I have a message box pop up with the newly generated (and committed) code. If user clicks the OK button before they have written it down, there is a fallback in that users have access to their action log
 

June7

AWF VIP
Local time
Today, 01:03
Joined
Mar 9, 2014
Messages
5,423
Another prompt users have to respond to and as you said they might accidentally click OK (or just hit Enter key) and then they have to track down the record they just entered. My solution might be more complicated code but I think process is friendlier for my users.
 

Users who are viewing this thread

Top Bottom