Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-09-2018, 12:33 PM   #1
sparklegrrl
Newly Registered User
 
Join Date: Jul 2003
Location: Texas
Posts: 105
Thanks: 0
Thanked 0 Times in 0 Posts
sparklegrrl
Send a message via ICQ to sparklegrrl Send a message via AIM to sparklegrrl Send a message via Yahoo to sparklegrrl
Custom Primary Key

Good afternoon,

I've searched the forum and I know this has come up but I'm having trouble finding exactly what I need. My apologies if I am searching the wrong thing.

I have a primary key/auto number field that I need to format as the year and number. For example - 18-00000. Each year it needs to reset to 0.

Can anyone help?

Thanks so much!

sparklegrrl is offline   Reply With Quote
Old 11-09-2018, 12:48 PM   #2
MickJav
Newly Registered User
 
Join Date: Nov 2005
Location: Margate
Posts: 554
Thanks: 24
Thanked 5 Times in 5 Posts
MickJav is on a distinguished road
Re: Custom Primary Key

If I'm reading you right you wont be able to use an autonumber the code I posted here could be adapted to do what you need


https://www.access-programmers.co.uk...82&postcount=5


this is the whole thred
https://www.access-programmers.co.uk...82#post1598082
__________________
After 20 years working with access i have no more hair to give.
MickJav is offline   Reply With Quote
Old 11-09-2018, 12:51 PM   #3
sparklegrrl
Newly Registered User
 
Join Date: Jul 2003
Location: Texas
Posts: 105
Thanks: 0
Thanked 0 Times in 0 Posts
sparklegrrl
Send a message via ICQ to sparklegrrl Send a message via AIM to sparklegrrl Send a message via Yahoo to sparklegrrl
Re: Custom Primary Key

Thank you. Not sure how to make all that work. Are you saying create another field? How does it know to reset to 19-0000 on 1/1/19?

sparklegrrl is offline   Reply With Quote
Old 11-09-2018, 12:55 PM   #4
MickJav
Newly Registered User
 
Join Date: Nov 2005
Location: Margate
Posts: 554
Thanks: 24
Thanked 5 Times in 5 Posts
MickJav is on a distinguished road
Re: Custom Primary Key

I might addapt that funcion very bussy at moment with projects but will try and give it a go on sunday.
__________________
After 20 years working with access i have no more hair to give.
MickJav is offline   Reply With Quote
Old 11-09-2018, 01:48 PM   #5
sparklegrrl
Newly Registered User
 
Join Date: Jul 2003
Location: Texas
Posts: 105
Thanks: 0
Thanked 0 Times in 0 Posts
sparklegrrl
Send a message via ICQ to sparklegrrl Send a message via AIM to sparklegrrl Send a message via Yahoo to sparklegrrl
Re: Custom Primary Key

Quote:
Originally Posted by MickJav View Post
I might addapt that funcion very bussy at moment with projects but will try and give it a go on sunday.
Absolutely no worries. I appreciate any and all help! I'm fine with just auto number but the boss wants what the boss wants...*sigh*
sparklegrrl is offline   Reply With Quote
Old 11-09-2018, 02:14 PM   #6
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,609
Thanks: 309
Thanked 404 Times in 389 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Custom Primary Key

Have the autonumber as your primary keys to link any relationships.
Create your 'dummy' key to please your boss and be more visible/familiar to the users, but at the end of the day it is the autonumber that does all the work in the background.

This question has been asked before in one form or another. How/When you decide to 'reset' it, is up to you/
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 11-09-2018, 02:29 PM   #7
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,609
Thanks: 309
Thanked 404 Times in 389 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Custom Primary Key

Here is one such post.
Hopefully you can adapt that?

https://www.access-programmers.co.uk...set+autonumber

Also this should give you some ideas.?

https://access-programmers.co.uk/for...d.php?t=207359

https://access-programmers.co.uk/for...and+autonumber
HTH

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Last edited by Gasman; 11-09-2018 at 02:34 PM.
Gasman is offline   Reply With Quote
Old 11-09-2018, 06:41 PM   #8
GinaWhipp
AWF VIP
 
GinaWhipp's Avatar
 
Join Date: Jun 2011
Location: Ohio, USA
Posts: 5,221
Thanks: 20
Thanked 865 Times in 850 Posts
GinaWhipp has a spectacular aura about GinaWhipp has a spectacular aura about
Re: Custom Primary Key

Hmm, have a look at...
https://regina-whipp.com/blog/?p=704

__________________
Gina Whipp
Microsoft MVP (Access 2010-2015)


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
GinaWhipp 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
Primary Key and Custom Autonumber mond007 Tables 2 07-23-2012 06:14 AM
Creating a Custom Incrementing Primary Key PraizesLiberato Tables 8 07-24-2010 04:49 AM
Primary Key...Autonumber or Custom? DanG Tables 10 07-12-2005 11:11 AM
Custom ID's as Primary Key provoked Modules & VBA 3 06-13-2005 11:32 AM
Dual Primary key acting like single primary key smercer Tables 2 09-30-2004 07:45 AM




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