473,326 Members | 2,102 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Autonumber (almost) - sequential including text

4
Greetings all!

I have reviewed a number of the solutions for generating a strict autonumber field (without using autonumber) that increments from the maximum value (using DMax).

What I wish to accomplish is a bit more complex (and honestly may not be worth my time or effort - it's approaching that point already).

I am converting incident reporting from a manual form that is entered into Excel to then analyze the data to an Access 2010 database. We currently number the incidents as II-2014-001, where the 2014 is the year of the incident, and the -001 is sequential within that year.

The sticking point I am running into is that while I can make the 2014 autofill by using Year(), and then sequentially update the -001 with the DMax, I have no idea how to make the sequential value start over with the start of a new year.

As years are sequential, I'm pretty sure that my answer will be related to DMax of 2014-001 being able to reset at 2015-001, but it chokes at having the II at the front (since it then needs to be a text field).

Should I just dump the II (and tell my boss it isn't worth it - he will accept that answer), use two fields to display the II- separately, or is there an easier solution?
Sep 30 '14 #1
6 1492
zmbd
5,501 Expert Mod 4TB
See if this thread helps:
Create Invoice Number using year, month, day and a number that increments with one

Look at the criteria for DMAX
Sep 30 '14 #2
NeoPa
32,556 Expert Mod 16PB
No need for dumping the idea. It's relatively straightforward.

When you use numbers within a string you have to set selection criteria to match what it is you're interested in. When you have a result returned you extract the relevant part of the string and convert it to a number. Remember to use Nz() too, to handle the first record of a new year.

The code would be similar to :
Expand|Select|Wrap|Line Numbers
  1. Dim strLike As String, strWhere As String, strID As String
  2.  
  3. strLike = Replace("II-%Y-*", "%Y", Year(Date()))
  4. strWhere = Replace("([IncidentID] Like '%L')", "%L", strLike)
  5. strID = Nz(DMax("[IncidentID]", "[tblIncident]", strWhere), "000")
  6. strID = Replace(strLike, "*", Format(Val(Right(strID, 3)) + 1, "000"))
Oct 6 '14 #3
Nurgle
4
Thank you both!

zmbd, I was going to say I did review that post prior to making my own, but it was actually a very similar one that didn't work. I do believe that solution should work.

NeoPa - thank you as well. I believe that code should also work, giving me two separate possible solutions! ;-)

I apologize for taking so long to respond - I am a complete newbie to VBA programming and I am still trying to figure out where exactly to put those nifty bits of code. I know how to read it; my biggest challenge in understanding VBA hasn't been the language itself, but has been in figuring out where to stuff any particular piece of code. So many options! So between my normal work, I've been reading VBA manuals trying to learn what goes where.

You guys are great, by the way. I've done enough work as a moderator in other forums to know that newbies who don't search are the bane of power users' patience, which is why I'll struggle with the were of putting it for a bit longer before asking that question. That, and learning it on my own will hopefully help me discover why it goes where it goes, which will be more useful in the end. :-)
Oct 30 '14 #4
NeoPa
32,556 Expert Mod 16PB
@Nurgle.

Great attitude - and you even know where apostrophes go in plural possessives!!!

I'm just off travelling for a week (MS Global MVP Summit in Seattle.) so I can't help for a bit, but feel free to PM me a link to your new question if you post it. Explain your problem clearly and I'll do what I can to help clarify what goes where - assuming someone hasn't already done a great job of that before I get to it.
Oct 31 '14 #5
Nurgle
4
I got it to work! I believe it is mostly based on zmbd's link, although I absolutely needed to read both answers in order to figure it out. How on earth I'm to "choose as best answer" in this case, I do not know.

I was initially trying to put the code into the table itself, when I realized that the ONLY person who would be silly enough to be dorking around in the raw table would be me, therefore the answer needed to go onto the form.

I chose to simply format the number to include the "II-" at the front instead of stored as part of the identifier, so it displays as II-2014-035 but is stored as 2014035. I placed the following code into the "On Got Focus" event for the II_Number control:

Expand|Select|Wrap|Line Numbers
  1. Private Sub II_Number_GotFocus()
  2.  
  3. Dim lngInternalIncidentNum As Long
  4.  
  5. lngInternalIncidentNum = 25
  6.  
  7. If Nz(Me.II_Number, 25) = 25 Then
  8.     lngInternalIncidentNum = DMax("[II_Number]", "tblInternalIncidents", _
  9.         "Year([II_Date])= " & Year(Me.II_Date)) + 1
  10.     Me.II_Number.Value = lngInternalIncidentNum
  11. End If
  12.  
  13. End Sub
  14.  
With that solved, I have now wandered off into other problems (such as figuring out how to set up a macro code to tab out of a subform while using a form within a navigation form; I've figured out how to get out of the form but not how to verify the field is null prior to exiting!) that would need new threads if and when I admit defeat.

:-)

Thank you both for your patience, and sorry for being such a slow updater! I wanted to make sure I actually had it solved first, which meant having others try and break it too. ;-)
Nov 26 '14 #6
NeoPa
32,556 Expert Mod 16PB
No worries :-)

Such a short thread doesn't need a Best Answer as much as some longer ones do. Also, we both have so many in our stats already that a couple here or there won't make a big difference. It does you credit that you considered the issue though :-)
Nov 28 '14 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Albert Tu | last post by:
Dear there, We have an x-ray CT system. The acquisition computer acquires x-ray projections and outputs multiple data files in binary format (2-byte unsigned integer) such as projection0.raw,...
4
by: Roderik Emmerink | last post by:
Hi, The following page gives the desired result in firefox: http://www.vrijgezellenfeest.nl/serve_object.php?id=175 When viewing the same page in internet explorer there is no visible text in...
17
by: Alan Carter | last post by:
http://www.mvps.org/access/tencommandments.htm Thou shalt not use Autonumber if the field is meant to have meaning for thy users. Why? Alan
1
by: michelle | last post by:
Hi -- I'm creating a form in ASP.NET 2.0 (and I'm brand new to .NET). And if the person is new, then I want a blank form to appear and the person will fill all that info in. This works! ...
1
by: jason.teen | last post by:
Hi, Curerntly I have designed my table in MS Access with a column of "RuleID" and is set to (autonumber) tblRule: RuleID | Name ------------------------------- 1 ...
2
by: Doug Schwartz | last post by:
Does anyone know of a simple way to include text from one HTML file in another, besides frames? I've tried using an object, but can't quite figure out the right parameters. doug -- When a...
8
by: Imicola | last post by:
Hi, I want to use primary key IDs in my tables which are autonumbers, with a text prefix. I.e. for table tblVillage, the ID's will by V1, V2, V3... and for table tblHumanCases, the ID's will be...
4
by: DetrieZ | last post by:
This is only removing the text characters.. I need it to remove all the text and everything after it Public Function ExtractNumeric(TextString As String) As String Dim x As Long Dim sDigit...
2
by: Nick Cooper | last post by:
I need to send tasking information out to taxi drivers away from base. I can summarize the details in an Access report and attach it to an email using SendObject, but these are difficult to read on...
6
by: SaSystem1989 | last post by:
hi all, is it possible to Search data in database? with a single sql script or some tools will help me? for example ,I have a HR database ,and I want to search 'CEO', $2354.00 or '12/4/1996' in all...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.