By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,829 Members | 673 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,829 IT Pros & Developers. It's quick & easy.

increase value of last record by 1 automatically in new record

P: 4
I started a database for entering ambulance run reports last month. So far, everything is going great with it. As expected, I am always trying to improve upon it as I learn more things.

What I need help on now is auto generating the incident number. Starting the new year, the first incident number will be 20080001. I want to make it where when we create a new record, the default value will see what the last record's incident number is and add 1 to it. We create a new record by clicking a button on the Main Switchboard.

Something to consider...when 2009 starts, i want to be able to make the incident number 20090001.

I appreciate any help in this matter.

Mike Busby



PS. I asked a question on this site a few weeks back: HERE

Within a day or two, the question was answered.
I wanted to again thank everyone that helps out around here. It is much appreciated.
Jan 6 '08 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,623
I started a database for entering ambulance run reports last month. So far, everything is going great with it. As expected, I am always trying to improve upon it as I learn more things.

What I need help on now is auto generating the incident number. Starting the new year, the first incident number will be 20080001. I want to make it where when we create a new record, the default value will see what the last record's incident number is and add 1 to it. We create a new record by clicking a button on the Main Switchboard.

Something to consider...when 2009 starts, i want to be able to make the incident number 20090001.

I appreciate any help in this matter.

Mike Busby



PS. I asked a question on this site a few weeks back: HERE

Within a day or two, the question was answered.
I wanted to again thank everyone that helps out around here. It is much appreciated.
ASSUMPTIONS:
  1. Table Name: tblIncidents.
  2. Field Name containing Incident Number: [Incident#].
  3. You like to keep things neat and tidy in 1 line.
  4. In the Field on your Form bound to [Incident#], place this Expression in the Data Tab, Default Value Option:
    Expand|Select|Wrap|Line Numbers
    1. Left$(DLast("[Incident#]", "tblIncidents"),4) & Format$(Val(Right$(DLast("[Incident#]", "tblIncidents"),4) + 1),"0000")
  5. Now, whenever you add a New Incident, the Incident Number will automatically be incremented by 1, and formatted correctly, as in:
    Expand|Select|Wrap|Line Numbers
    1. 20080027 ==> New Incident Number will be 20080028
  6. To keep things simple, add the 1st Record for 2009 manually, then the Default Value Expression will increment it properly, as in:
    Expand|Select|Wrap|Line Numbers
    1. 20090001 (manually entered) ==> New Incident Number will be 20090002
  7. You can also Reset the Counter for a new Year but then neat and tidy, on 1 line, goes out the door. It all depends on what you want.
P.S. - Make sure that the [Incident#] Field is TEXT and not NUMERIC.
Jan 6 '08 #2

tdw
100+
P: 206
tdw
I started a database for entering ambulance run reports last month. So far, everything is going great with it. As expected, I am always trying to improve upon it as I learn more things.

What I need help on now is auto generating the incident number. Starting the new year, the first incident number will be 20080001. I want to make it where when we create a new record, the default value will see what the last record's incident number is and add 1 to it. We create a new record by clicking a button on the Main Switchboard.

Something to consider...when 2009 starts, i want to be able to make the incident number 20090001.

I appreciate any help in this matter.

Mike Busby
You are basically asking exactly the same thing that I had to get help with last year. We use exactly the same format for our file numbers, but with a dash: 2008-0001.
The person who originally created our database used some spaghetti programming using an autonumber field to then create the new file number. The problem was that system doesn't leave any room for error, no room for manually correcting file numbers. In order to reset the file numbers back to 0001 at the beginning of the year we had to go into the coding and change the mathematical formulas that were using the autonumber field.... it was a mess. Finally this forum helped me straighten it all out.
I will look to see if the thread for that is still on this forum and I'll link it if it is. If not I'll try to help by posting examples of my code.
It's not as neat as the one liner below, but it works great and does automatically reset at the beginning of the year.
Feb 6 '08 #3

tdw
100+
P: 206
tdw
Here is the thread

http://www.thescripts.com/forum/thre...le+number.html

Below is what I ended up with. Keep in mind that I am actually using two tables (SC_OPEN and SC_ARCH), and looking for the highest file number between the two. You are probably using one table, so you'd have to filter out the extra stuff.

Expand|Select|Wrap|Line Numbers
  1.     Dim stHighest As Integer
  2.     Dim stHighOpen As Integer
  3.     Dim stHighArch As Integer
  4.  
  5. '   Check to see if this is the first order of the year
  6.  
  7.     If DCount("*", "SC_OPEN", "Left([FILE_NO],4)=Format(Date(),'yyyy')") = 0 _
  8.         And DCount("*", "SC_ARCH", "Left([FILE_NO],4)=Format(Date(),'yyyy')") = 0 Then
  9.  
  10.         Me.FILE_NO = Year(DATE) & "-0001"   '   Reset file number to 1
  11.         DoCmd.RunCommand acCmdSaveRecord
  12.  
  13.     Else
  14.  
  15. '   Find the highest file number for the current year from both tables
  16.         stHighOpen = Nz(DMax("Right([FILE_NO], 4)", "SC_OPEN", "Left([FILE_NO], 4) = Format(DATE(), 'yyyy')"))
  17.  
  18.         stHighArch = Nz(DMax("Right([FILE_NO], 4)", "SC_ARCH", "Left([FILE_NO], 4) = Format(DATE(), 'yyyy')"))
  19.  
  20. '   Pick the one that is highest
  21.             If stHighOpen < stHighArch Then
  22.                 stHighest = stHighArch
  23.  
  24.             Else
  25.                 stHighest = stHighOpen
  26.  
  27.             End If
  28.  
  29.         '   Create new file number
  30.         If stHighest > 0 And stHighest < 9 Then
  31.             Me.FILE_NO = Year(DATE) & "-000" & stHighest + 1
  32.         End If
  33.  
  34.         If stHighest > 8 And stHighest < 99 Then
  35.             Me.FILE_NO = Year(DATE) & "-00" & stHighest + 1
  36.         End If
  37.  
  38.         If stHighest > 98 And stHighest < 999 Then
  39.             Me.FILE_NO = Year(DATE) & "-0" & stHighest + 1
  40.         End If
  41.  
  42.         If stHighest > 998 Then
  43.             Me.FILE_NO = Year(DATE) & "-" & stHighest + 1
  44.         End If
  45.  
  46. DoCmd.RunCommand acCmdSaveRecord
  47.  
  48.     End If
  49.  
  50. Confirm_File_No_Creation:
  51.     DoCmd.SetWarnings True
  52.     MsgBox ("The File Number " & [FILE_NO] & " Has Been Created For The Order")
  53.  
This could be improved I'm sure. But I was pleased with it, considering I was brand new at Access
Feb 6 '08 #4

Post your reply

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