Sequential number generator that resets

[aclockworkgod]

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]

a common enough question - investigate what is typically called 'dmax+1'

 

[Mark_]

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]

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]

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.

 

[June7]

Review https://www.accessforums.net/showthread.php?t=23329

 

[Mark_]

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]

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]

Very similar to example code in post 6 link.

 

[aclockworkgod]

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]

...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)>

 

[apr pillai]

I am not sure whether the following link will help you to plan your own approach for a solution:

Autonumber with Date and Sequence Number

 

[Mark_]

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]

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]

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]

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.