increase value of last record by 1 automatically in new record

[thebuzzman]

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.

[ADezii]

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:
   

   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:
   

   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:
   

   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.

[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.

[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.

    Dim stHighest As Integer
    Dim stHighOpen As Integer
    Dim stHighArch As Integer
 
'   Check to see if this is the first order of the year
 
    If DCount("*", "SC_OPEN", "Left([FILE_NO],4)=Format(Date(),'yyyy')") = 0 _
        And DCount("*", "SC_ARCH", "Left([FILE_NO],4)=Format(Date(),'yyyy')") = 0 Then
 
        Me.FILE_NO = Year(DATE) & "-0001"   '   Reset file number to 1
        DoCmd.RunCommand acCmdSaveRecord
 
    Else
 
'   Find the highest file number for the current year from both tables
        stHighOpen = Nz(DMax("Right([FILE_NO], 4)", "SC_OPEN", "Left([FILE_NO], 4) = Format(DATE(), 'yyyy')"))
 
        stHighArch = Nz(DMax("Right([FILE_NO], 4)", "SC_ARCH", "Left([FILE_NO], 4) = Format(DATE(), 'yyyy')"))
 
'   Pick the one that is highest
            If stHighOpen < stHighArch Then
                stHighest = stHighArch
 
            Else
                stHighest = stHighOpen
 
            End If
 
        '   Create new file number
        If stHighest > 0 And stHighest < 9 Then
            Me.FILE_NO = Year(DATE) & "-000" & stHighest + 1
        End If
 
        If stHighest > 8 And stHighest < 99 Then
            Me.FILE_NO = Year(DATE) & "-00" & stHighest + 1
        End If
 
        If stHighest > 98 And stHighest < 999 Then
            Me.FILE_NO = Year(DATE) & "-0" & stHighest + 1
        End If
 
        If stHighest > 998 Then
            Me.FILE_NO = Year(DATE) & "-" & stHighest + 1
        End If
 
DoCmd.RunCommand acCmdSaveRecord
 
    End If
 
Confirm_File_No_Creation:
    DoCmd.SetWarnings True
    MsgBox ("The File Number " & [FILE_NO] & " Has Been Created For The Order")
 

This could be improved I'm sure. But I was pleased with it, considering I was brand new at Access