 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / January 2004Creating a customized counter?
I am creating a file tracking database and would like to use our file numbering system, but have it autonumber. For example, our filing system uses the current year and then a four digit number starting at one such as 2004-0001 So I would like every record to autonumber to create the new and unique file number 2004-0002, 2004-0003 etc. Please help! Peter. Hi Peter, You set the Format property of the AutoNumber equal to \200"4-"0000 Other ideas include: 209830 ACC2000: How to Increment the Numeric Portion of a String http://support.microsoft.com/?id=209830 199679 ACC2000: How to Simulate a Dynamic Counter in a Table or a Query to http://support.microsoft.com/?id=199679 I hope this helps! If you have additional questions on this topic, please respond back to this posting. Regards, Eric Butts Microsoft Access Support "Microsoft Security Announcement: Have you installed the patch for Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises you to review the information at the following link regarding Microsoft Security Bulletin MS03-026 <http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or to visit Windows Update at <http://windowsupdate.microsoft.com/> to install the patch. Running the SCAN program from the Windows Update site will help to insure you are current with all security patches, not just MS03-026." -------------------- | Content-Class: urn:content-classes:message | From: "Peter" <anonymous@discussions.microsoft.com> [quoted text clipped - 27 lines] | | Peter. Thanks Eric! That worked perfectly. Do you have any suggestions on how to reset the Autonumber? >-----Original Message----- >Hi Peter, [quoted text clipped - 60 lines] > >. Hi Eric, you can ignore my last question about re-setting the counter. I deleted the first ID counter and created a second which effectively accomplished what I was looking to do. Thanks again! >-----Original Message----- >Hi Peter, [quoted text clipped - 60 lines] > >. >Hi Peter, > >You set the Format property of the AutoNumber equal to > \200"4-"0000 Note that this will continue to display the number as 2004-xxxx in 2005, 2006 and later years, and that the number will continue to increment rather than starting over at the beginning of the year! Peter should use one of your other suggestions, in order to meet the real requirement. >Other ideas include: >209830 ACC2000: How to Increment the Numeric Portion of a String >http://support.microsoft.com/?id=209830 > >199679 ACC2000: How to Simulate a Dynamic Counter in a Table or a Query to >http://support.microsoft.com/?id=199679 John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public Hi Peter, John's correct in that the number will display as 2004-xxxx in 2005, 2006 and later years, and that the number will continue to increment rather than starting over at the beginning of the year! Look at his suggestion in his previous post if you are looking for it to start over at the beginning of the year. Eric -------------------- | From: John Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> | Newsgroups: microsoft.public.access.tablesdbdesign [quoted text clipped - 3 lines] | Message-ID: <9r0p00lgoopqv9fic21r055g1195j6icq1@4ax.com> | References: <0d1401c3dedd$13a86e90$a601280a@phx.gbl> <M8BbYhu3DHA.1512@cpmsftngxa07.phx.gbl> | X-Newsreader: Forte Agent 1.8/32.548 | MIME-Version: 1.0 [quoted text clipped - 6 lines] | Lines: 24 | Path: cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwix .com!newsfeed.cwix.com!news.maxwell.syr.edu!news.glorb.com!newshosting.com!n x02.iad01.newshosting.com!news-feed01.roc.ny.frontiernet.net!nntp.frontierne t.net!news-west.eli.net!nntp.widaho.net!10.6.1.33 | Xref: cpmsftngxa07.phx.gbl microsoft.public.access.tablesdbdesign:74440 | X-Tomcat-NG: microsoft.public.access.tablesdbdesign [quoted text clipped - 20 lines] | Come for live chats every Tuesday and Thursday | http://go.compuserve.com/msdevapps?loc=us&access=public >I am creating a file tracking database and would like to >use our file numbering system, but have it autonumber. [quoted text clipped - 3 lines] >create the new and unique file number 2004-0002, 2004-0003 >etc. An Autonumber will NOT work for this. And... this kind of "Intelligent Key" is generally frowned upon; storing two pieces of data in one field is unwise, and storing data (a date) in an ID field violates first normal form - fields should be atomic. If you want this number anyway, for compatibility with an existing paper system for instance, I'd suggest using *two* fields, FileYear and FileSeq, both Integer. Set the Default property of FileYear to Year(Date()) so it will automatically pick up the current year; and use a Form to enter data into your table (table datasheets don't have any usable events). Put a textbox on the form named txtFileSeq bound to the FileSeq field. In the Form's BeforeInsert event put code like Private Sub Form_BeforeInsert(Cancel as Integer) Me!txtFileSeq = NZ(DMax("[FileSeq]", "[your-table-name]", "[FileYear] = " & Me!FileYear)) End Sub John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public Hi John, Thanks so much for the recommendations on my problem. I used your code at the bottom and got it to work. I was wondering if the code you provided will also reset the autonumber sequence (i.e. your "FileSeq") to zero once the year automatically changes. In other words, when 2005 comes...will the sequence reset to look like 2005-0001? Or will it continue counting where the autonumber left off, only with the 2005 prefix (ie 2005-1969)? I'm very new at Access and programming, but it looks like it might be the latter...if so, do you have any suggestions on how I might change it to reset with the new year? Sorry, to bug you like this. I've just been tasked with something over my head and could really use your help. Cheers! >-----Original Message----- > [quoted text clipped - 31 lines] >http://go.compuserve.com/msdevapps?loc=us&access=public >. >Thanks so much for the recommendations on my problem. I >used your code at the bottom and got it to work. I was >wondering if the code you provided will also reset the >autonumber sequence (i.e. your "FileSeq") to zero once the >year automatically changes. In other words, when 2005 >comes...will the sequence reset to look like 2005-0001? Yes, it will: let me explain why. Private Sub Form_BeforeInsert(Cancel as Integer) Me!txtFileSeq = NZ(DMax("[FileSeq]", "[your-table-name]", _ "[FileYear]= " & Me!FileYear)) + 1 End Sub The DMax() function takes three arguments: a field to be looked up; a "domain" - a table or query in which to look; and a criterion as the third argument. The criterion will search for all records in your-table-name for which the table's FileYear field is equal to the FileYear control on the form. If it's 2005, that control will (presumably) contain 2005, so it will find all the 2005 records, if any. If there are already some records in 2005, the DMax() function will return the largest value of FileSeq in that group; if there aren't any at all, it will return NULL. The NZ() function will either return the maximum existing FileSeq value, or 0 if DMax() didn't return anything - that's what NZ does, converts Null to Zero. Finally the expression (as I've corrected it here... as written it would NOT work!!!) will add 1 to whatever was returned by NZ; if there were no 2005 records you'll get 0 + 1 and txtFileSeq will be set to 1, otherwise it will add one to the largest existing value and use that. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public Thanks again for taking the time to provide both a solution and explaination. However, it seems that all the feedback i've been receiving indicates I shouldn't be using an autonumber for this purpose in the first place. But I appreciate all the help! Cheers, Peter. >-----Original Message----- > [quoted text clipped - 38 lines] > >. >Thanks again for taking the time to provide both a >solution and explaination. However, it seems that all the >feedback i've been receiving indicates I shouldn't be >using an autonumber for this purpose in the first place. ummmm... exactly. My suggestion does NOT use an Autonumber. It's an alternative to using an Autonumber. Please reread my posts! John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.