Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-23-2019, 12:40 AM   #1
ReginaF
Newly Registered User
 
Join Date: Sep 2019
Posts: 12
Thanks: 7
Thanked 0 Times in 0 Posts
ReginaF is on a distinguished road
conditional autonumber?

Hello,

In my workplace I have to work with Access and it is quite new to me, so would like to ask for your help.

I would like to add a field to my table where I could have autonumbering. The tricky part is, that I really need that the autonumbering would start again from one each year, and would really like to store it in my table, as later it will be needed for a calculated field. Now I know, that I could enter the numbers manually, however we would like to reduce the possibility of creating a place for errors. I have played with the idea of creating a VBA function for it, however I can not figure out how can I apply any costum function to create a calculated field in a table (if it is possible at all).

Thank you in advance

ReginaF is offline   Reply With Quote
Old 09-23-2019, 01:03 AM   #2
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,568
Thanks: 68
Thanked 2,744 Times in 2,629 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: conditional autonumber?

use DMax()+1 to generate the autonumber.
since it resets every year, you also need a year field (number) to your table.
dmax function is not available in macro so you need to build a function to generate.
you also need a form for your table so you can call the function.

add code to your form's BeforeInsert() event:
Code:
private sub form_beforeinsert(cancel as integer)
me.autonumberField = nz( dmax("autonumberField", "yourTableName", "yearField=" & Year(date())), 0) + 1
end sub
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
ReginaF (09-24-2019)
Old 09-23-2019, 01:06 AM   #3
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,432
Thanks: 165
Thanked 1,736 Times in 1,706 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: conditional autonumber?

Most of the time the need for this "yearly reset" is historic and not needed if you have any date field that you can reference in your top level record data.

Have a search on here for "resetting yearly numbering" and you will find a plethora of examples and some of the issues surrounding implementing it.

__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

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.
Minty is online now   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
ReginaF (09-24-2019)
Old 09-23-2019, 06:53 AM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,710
Thanks: 57
Thanked 1,259 Times in 1,240 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: conditional autonumber?

Hi. Are you sure you need to store this number? Is it not possible to find this number just using a query?
__________________
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 09-24-2019, 10:12 AM   #5
ReginaF
Newly Registered User
 
Join Date: Sep 2019
Posts: 12
Thanks: 7
Thanked 0 Times in 0 Posts
ReginaF is on a distinguished road
Re: conditional autonumber?

Thank you!

I am so happy that it is working, that I can not thank you enough!
ReginaF is offline   Reply With Quote
Old 09-24-2019, 10:43 AM   #6
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,710
Thanks: 57
Thanked 1,259 Times in 1,240 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: conditional autonumber?

Hi. Glad to hear you got it sorted out. Good luck with your project.
__________________
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 09-27-2019, 09:05 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,032
Thanks: 20
Thanked 381 Times in 374 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: conditional autonumber?

ReginaF,

A suggestion, use an actual autonumber in your table AND have your custom number for display. You would use the real autonumber (never seen by users) to link together child records and such.


Mark_ 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 ellenr Modules & VBA 5 10-25-2013 10:46 AM
Autonumber stanisa Tables 1 06-02-2006 05:08 AM
Autonumber mkelly General 4 02-24-2004 12:52 PM
Autonumber not from 1? Suzy Tables 3 01-29-2004 02:00 AM
Autonumber dragos Forms 1 05-22-2003 12:41 PM




All times are GMT -8. The time now is 04:14 AM.


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