Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-12-2019, 09:19 AM   #1
aclockworkgod
Newly Registered User
 
Join Date: Sep 2019
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
aclockworkgod is on a distinguished road
Sequential number generator that resets

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!

aclockworkgod is offline   Reply With Quote
Old 09-12-2019, 09:28 AM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,233
Thanks: 40
Thanked 3,643 Times in 3,513 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Sequential number generator that resets

a common enough question - investigate what is typically called 'dmax+1'
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
Tera (09-14-2019)
Old 09-12-2019, 09:31 AM   #3
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,024
Thanks: 20
Thanked 381 Times in 374 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Sequential number generator that resets

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_ is offline   Reply With Quote
Old 09-12-2019, 09:38 AM   #4
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,396
Thanks: 68
Thanked 2,701 Times in 2,586 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Sequential number generator that resets

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
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-12-2019, 09:45 AM   #5
aclockworkgod
Newly Registered User
 
Join Date: Sep 2019
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
aclockworkgod is on a distinguished road
Re: Sequential number generator that resets

Quote:
Originally Posted by Mark_ View Post
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.
aclockworkgod is offline   Reply With Quote
Old 09-12-2019, 09:46 AM   #6
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,270
Thanks: 0
Thanked 532 Times in 528 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Sequential number generator that resets

Review https://www.accessforums.net/showthread.php?t=23329
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
aclockworkgod (09-12-2019)
Old 09-12-2019, 10:22 AM   #7
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,024
Thanks: 20
Thanked 381 Times in 374 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Sequential number generator that resets

Quote:
Originally Posted by aclockworkgod View Post
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?

Mark_ is offline   Reply With Quote
Old 09-12-2019, 10:39 AM   #8
aclockworkgod
Newly Registered User
 
Join Date: Sep 2019
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
aclockworkgod is on a distinguished road
Re: Sequential number generator that resets

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?
aclockworkgod is offline   Reply With Quote
Old 09-12-2019, 10:49 AM   #9
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,270
Thanks: 0
Thanked 532 Times in 528 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Sequential number generator that resets

Very similar to example code in post 6 link.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 09-12-2019, 11:12 AM   #10
aclockworkgod
Newly Registered User
 
Join Date: Sep 2019
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
aclockworkgod is on a distinguished road
Re: Sequential number generator that resets

Quote:
Originally Posted by June7 View Post
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!
aclockworkgod is offline   Reply With Quote
Old 09-12-2019, 04:26 PM   #11
missinglinq
AWF VIP
 
missinglinq's Avatar
 
Join Date: Jun 2003
Location: Richmond (Virginia that is!)
Posts: 6,298
Thanks: 11
Thanked 716 Times in 666 Posts
missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light
Re: Sequential number generator that resets

Quote:
Originally Posted by Mark_ View Post

...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)>
__________________
The Devil's in the Details!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


(All code solutions tested in Access 2003/2007, before posting, unless otherwise noted.)
missinglinq is offline   Reply With Quote
Old 09-14-2019, 07:27 AM   #12
apr pillai
Newly Registered User
 
apr pillai's Avatar
 
Join Date: Jan 2005
Location: India
Posts: 677
Thanks: 1
Thanked 107 Times in 95 Posts
apr pillai is on a distinguished road
Send a message via AIM to apr pillai Send a message via Yahoo to apr pillai Send a message via Skype™ to apr pillai
Re: Sequential number generator that resets

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
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
(Learn MS-Access Tips and Tricks)

All responses are based on Access2003/2007
apr pillai is offline   Reply With Quote
Old 09-14-2019, 09:13 PM   #13
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,024
Thanks: 20
Thanked 381 Times in 374 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Sequential number generator that resets

Quote:
Originally Posted by missinglinq View Post
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...
Mark_ is offline   Reply With Quote
Old 09-14-2019, 09:19 PM   #14
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,270
Thanks: 0
Thanked 532 Times in 528 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Sequential number generator that resets

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.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 09-15-2019, 12:49 AM   #15
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,233
Thanks: 40
Thanked 3,643 Times in 3,513 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Sequential number generator that resets

Quote:
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

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
The_Doc_Man (09-15-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Random number generator Lifeseeker Modules & VBA 10 09-23-2011 08:55 AM
Page number resets with report footer vmon Reports 0 09-11-2006 06:43 AM
Random Number Generator velcrowe Sample Databases 5 06-01-2006 10:43 AM
[SOLVED] random number generator anitra Forms 1 02-13-2006 03:35 PM
[SOLVED] Number that resets every year squatrow Forms 2 07-13-2003 03:03 PM




All times are GMT -8. The time now is 06:52 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World