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,806
Thanks: 10
Thanked 2,114 Times in 2,069 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: 588
Thanks: 0
Thanked 136 Times in 136 Posts
June7 will become famous soon enough
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: 588
Thanks: 0
Thanked 136 Times in 136 Posts
June7 will become famous soon enough
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: 588
Thanks: 0
Thanked 136 Times in 136 Posts
June7 will become famous soon enough
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
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,491
Thanks: 14
Thanked 272 Times in 270 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
Old 07-18-2018, 02:19 AM   #10
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,442
Thanks: 51
Thanked 944 Times in 913 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Custom Numbering

Personally, I would have both in some cases.

Have an autonumber, and use that to relate this table to other tables.

Than have your own specially designed unique reference value, and store this in your table, without it being used as a foreign key anywhere. That way your table links remain simple (numeric), and you can change your internal numbering system anytime you want. It might be easier to make your internal numbering multi-field, rather than single field - eg, year, contract, serial number. This way you don't have the pain (and it is a pain) of constructing multi field links between tables.


as Mark said above.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the top right of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 07-18-2018, 02:54 AM   #11
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,233
Thanks: 72
Thanked 1,401 Times in 1,322 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Custom Numbering

Quote:
Originally Posted by gemma-the-husky View Post
. . . have your own specially designed unique reference value, and store this in your table, without it being used as a foreign key anywhere.That way your table links remain simple (numeric), and you can change your internal numbering system anytime you want.
The convenience of this cannot be overestimated (or do I mean underestimated ?) especially while attempting to merge data from disparate databases.

Quote:
It might be easier to make your internal numbering multi-field, rather than single field - eg, year, contract, serial number. This way you don't have the pain (and it is a pain) of constructing multi field links between tables.
Yes, remembering that anything that purports to be a key in in the eyes of the user aught to be treated as a candidate key. For example, we shouldn't have two formula derived "invoice numbers" identical even if we can tell the records apart in the database. Hence logical models and indexes need to be arranged to ensure only a deterministic unique value is presented for each record regardless of how it is derived.

But of course you wouldn't want something that complex and vulnerable as your primary key to link other tables.

Last edited by Galaxiom; 07-18-2018 at 03:00 AM.
Galaxiom is offline   Reply With Quote
Old 07-18-2018, 05:39 AM   #12
MajP
Newly Registered User
 
Join Date: May 2018
Posts: 436
Thanks: 6
Thanked 105 Times in 103 Posts
MajP will become famous soon enough
Re: Custom Numbering

a simple version without the concurrent user checks.
Code:
Public Function NewProjID(theDate As Date) As String
   Dim strProjNum As String
   Dim Counter As Integer
   Dim MonthID As String
   Dim strYear As String
   MonthID = GetMonth(theDate)
   strYear = CStr(Year(theDate))
   If strProjNum = "" Then
        Counter = DCount("*", "tblProjects", "[Project_ID] like '*" & MonthID & strYear & "'")
   End If
   NewProjID = "PR" & Format(Counter + 1, "00") & MonthID & strYear
End Function

Public Function GetMonth(theDate) As String
    GetMonth = Chr(64 + Month(theDate))
End Function

MajP is online now   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
Gasman (07-18-2018)
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 05:52 AM.


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