Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-11-2018, 10:17 AM   #1
Jpowell
Newly Registered User
 
Join Date: Jul 2018
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Jpowell is on a distinguished road
Custom Numbering

I am trying to find away to Create an auto number for our project numbering system. It is formated as PR01A2018. Pr Stays the same, The number changes as new projects the letter is the month (A = January, B = February, Ect) and the year as the date entered. Is this possible or am I wasting my time?

Jpowell is offline   Reply With Quote
Old 07-11-2018, 10:31 AM   #2
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,649
Thanks: 10
Thanked 2,074 Times in 2,029 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Custom Numbering

Quote:
Is this possible or am I wasting my time?
Yes, any algorithm you can logically define is possible.

Yes, you are wasting your time.

What do you hope to accomplish with this custom numbering? What benefit does it provide over an autonumber?
plog is offline   Reply With Quote
Old 07-11-2018, 10:41 AM   #3
Jpowell
Newly Registered User
 
Join Date: Jul 2018
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Jpowell is on a distinguished road
Re: Custom Numbering

We have a current format now on our numbering system and I am in charge of now putting these into a database as they come in and would like to make it auto number with the current format we use.

Jpowell is offline   Reply With Quote
Old 07-11-2018, 10:53 AM   #4
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 330
Thanks: 0
Thanked 76 Times in 76 Posts
June7 is on a distinguished road
Re: Custom Numbering

I do this. Laboratory samples are assigned a sequential number and the series starts over each year. Numbers must be accounted for - no gaps.

Generating custom unique identifier is a common topic. One example http://www.accessforums.net/showthread.php?t=23329
June7 is offline   Reply With Quote
Old 07-11-2018, 11:10 AM   #5
Jpowell
Newly Registered User
 
Join Date: Jul 2018
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Jpowell is on a distinguished road
Re: Custom Numbering

Do you use a Month as a letter format in your table? Thanks, I browsed some of these before I posted. I just wanted to know if my format is possible.
Jpowell is offline   Reply With Quote
Old 07-11-2018, 12:19 PM   #6
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 330
Thanks: 0
Thanked 76 Times in 76 Posts
June7 is on a distinguished road
Re: Custom Numbering

Certainly your ID format is possible.

No, I do not use a month identifier as part of the ID.

Whether or not you use this custom ID as the linking primary/foreign key is a different question. Many here would suggest you should use autonumber as the (hidden) link and use the custom ID for users to reference.
June7 is offline   Reply With Quote
Old 07-11-2018, 12:44 PM   #7
Jpowell
Newly Registered User
 
Join Date: Jul 2018
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Jpowell is on a distinguished road
Re: Custom Numbering

This is what I would like to do but I'm not sure how to go about this with the user reference key I'm trying to create. Unless it's manually typed.

Jpowell is offline   Reply With Quote
Old 07-11-2018, 01:12 PM   #8
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 330
Thanks: 0
Thanked 76 Times in 76 Posts
June7 is on a distinguished road
Re: Custom Numbering

I provided example code for a function that creates unique identifier. Modify as you see fit.

The real trick is figuring out what event to call the function from. There is risk that multiple users could generate the same number in which case will get error for whoever is not first to save record if you have the field set to not allow duplicates, as should be.

Other code calls the function to create the identifier and immediately commits record - because users have to see the SampleID right away - then opens that record for them to complete other data. I have never had the same number generated by multiple users. My code also allows for user to abort record creation and since the SampleID must be accounted for, code 'blanks' the record except for the SampleID field and the saved record is pulled up for next user to enter a new sample.

Alternatively, you can have users do data input and create the new SampleID after all input and commit record at that time.

The more 'user friendly' the more code.

Last edited by June7; 07-11-2018 at 01:18 PM.
June7 is offline   Reply With Quote
Old 07-11-2018, 01:26 PM   #9
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Posts: 1,216
Thanks: 13
Thanked 230 Times in 228 Posts
Mark_ will become famous soon enough
Re: Custom Numbering

@ OP,

To be fairly clear regarding the advice you are being given...

To link records or to track a specific record you will want to use an auto number primary key that cannot be edited. This avoids many issues related to users doing strange things.

If you want a short hand "Number" that end users can reference you will want to save that as a different field that is NOT used to link records or to uniquely identify a given record.

What you are looking or is "Sequential numbers in a given month". This is normally accomplished by having two fields in your record, AddDate (The date you create the record, you probably have one anyhow) and "Sequence". You then use DMax() to find the highest sequence number for a given month, often by having a query that lists all records by yyyymm and sequence.

You then put together the pieces you need to display your reference. In your case it becomes "PR" & FORMAT(YourSequenceNumber,"00") & SomeFunctionYouWriteToReturnLetterBasedOnMonthNumb er(Month(AddDate)) & FORMAT(YEAR(AddDate),"yyyy")

Mark_ is offline   Reply With Quote
Reply

Tags
auto numbering

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Permanent custom numbering Keeperen General 3 09-17-2012 12:59 AM
Numbering dastr Queries 8 05-22-2012 05:04 AM
Custom date format for custom combo box value list merry_fay Modules & VBA 10 03-28-2011 06:18 AM
Can I have a custom icon on my custom right click contaxt menu darbid Modules & VBA 1 08-12-2010 10:01 AM
Question Custom auto numbering records josephbupe General 6 05-29-2010 08:40 PM




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


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

Sponsored Links

How to advertise

Media Kit


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