Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-10-2019, 05:36 AM   #1
Cgaro62
Newly Registered User
 
Join Date: Jul 2019
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Cgaro62 is on a distinguished road
Autonumber question

Hi Isladogs,


I hope so too! I am working on creating a custom auto number in for my form but so far, no luck.

The form is 2019 CAPAR Log, the field is "CAR Number" in which I am trying to create a formula that when a new record is entered it will provide me with an auto-number that begins with "2019-" followed by a 3-digit number. For example, 2019-001, 2019-002, 2019-003 and so on.



Do you think you could help? I'm a beginner when it comes to creating databases.

Cgaro62 is offline   Reply With Quote
Old 07-10-2019, 05:52 AM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,833
Thanks: 107
Thanked 2,661 Times in 2,434 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: C-Ana

Hi
In the table design, go to the properties section for that field and enter "2019-"000
The autonumbering will be displayed as 2019-001,2019-002 etc but will actually be saved as 1,2 etc

For info I have moved this to the Tables section as it is a technical question
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 07-10-2019, 06:37 AM   #3
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,769
Thanks: 55
Thanked 1,022 Times in 988 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: Autonumber question

I imagine you want to start the prefix from scratch each year, so you need a slghtly different solution to have 2019-nnn, then next year 2020-nnn, etc.

Rather than have one field, have 2 fields. Store the year in one field.
Make the other your sequence number.


You need to get the highest sequence number for the active year with a dmax

nextcarnumber = dmax("carnumber","tblcars","yearnumber = " & yearrequired),0)+1

__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 07-10-2019, 06:44 AM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,662
Thanks: 38
Thanked 889 Times in 872 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Autonumber question

Hi. You might find something useful from here. Good luck!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 07-10-2019, 07:26 AM   #5
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,948
Thanks: 19
Thanked 370 Times in 363 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Autonumber question

One warning about "2019-###", what happens in 2021 when you have 2174 records for the year? Is this a possibility?
Mark_ is offline   Reply With Quote
Old 08-02-2019, 07:31 AM   #6
statsman
Newly Registered User
 
statsman's Avatar
 
Join Date: Aug 2004
Location: Toronto ON
Posts: 2,074
Thanks: 4
Thanked 3 Times in 3 Posts
statsman will become famous soon enough
Re: Autonumber question

I downloaded this off another forum some time ago. The paper is yellow its so old but it works great.
I don't recall whose code it is but it works like a charm for year and sequence. If you recognize it please claim it.

(quote)

Create two fields in your table:
TheYear - long integer
TheSequence – long integer

Then enter this code in your form's AfterInsert

Me.TheYear = CLng(DatePart("YYYY", Date()))

Me.TheSequence = Nz(DMax("[TheSequence]", "YourTableName", "[TheYear] = "&Me.TheYear),0) +1

That’s all the code you need. When a new year starts the DMax function returns a Null, which the Nz function changes to a 0. Then it just adds 1 to get the next value.

Why two fields? You will spend a lot of time writing code to continuously tear them apart. It’s much easier to store and group them when they're stored separately.

It’s also easier to format them in a query.

(end quote)

What happens is when you make your first keystroke on the form the year and sequence are entered automatically. So if your last entry was
TheYear - 2006 TheSequence – 14
the next will be
TheYear - 2006 TheSequence – 15
assuming its still 2006. If this is the first entry in 2007 you will get:
TheYear – 2007 TheSequence – 1

Be sure to set TheYear and TheSequence text boxes on your forms to Enabled-NO Locked-YES. This will prevent people from overriding the pre-set numbers.

I use this function constantly to create Invoice Numbers.

__________________
Dart players always have a point.
statsman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
AutoNumber VBA Question~! hwong Forms 9 09-19-2012 09:40 PM
Autonumber question Edgarr8 Queries 4 09-23-2009 04:26 PM
Autonumber question again sorry G1ZmO Tables 7 03-25-2008 02:52 AM
Autonumber question Holmes Tables 4 01-08-2007 02:19 AM
autonumber question jeremypaule Tables 2 10-17-2006 11:09 AM




All times are GMT -8. The time now is 02:24 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