autonumber

eric.l.bohon

Board Regular
Joined
May 8, 2006
Messages
149
good morning to all,

what i am trying to do is pretty simple but can not figure it out. I have field that when new info is input in it will automatically but a number in. the number looks like this (09-0001). whit the 09 being the last two digits of the year and the 0001 being the first document. now with that being said, if i put one new record in it puts the 09-0001 and when i input another record in it will automatically put the next number in as 09-0002.

any help.
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Hi Eric,

This needs to be done on a form and you will need some VBA code. In the example below I've assumed that you have a table called tblTx, and the primary key is Tx_ID (a text field).

Code:
Option Compare Database
Option Explicit
Private Sub Form_Current()
    If Me.NewRecord Then
        Me.Tx_ID.SetFocus
        Me.Tx_ID = GetNewID()
    End If
End Sub
Function GetNewID()
    Dim sYear As String
    Dim lngRec As Long
    Dim sNumber As String
    Dim sID
    
    sYear = Format(Year(Now()) - 2000, "00")
    lngRec = DCount("*", "tblTx", "Left([Tx_ID],2)='" & sYear & "'") + 1
    sNumber = Format(lngRec, "0000")
    GetNewID = sYear & "-" & sNumber
End Function

You'll need to create code for the form's Current event. If you haven't done that before, see here. In the code above, change the table and ID field names to suit.
Make sure that the Current event contains the code above (modified with your names), and that you paste the function below the event code.
Note: If there are gaps in the names you will need to warp the names in square brackets or you will get an error.

Denis
 

eric.l.bohon

Board Regular
Joined
May 8, 2006
Messages
149
i have a question in regards to this. the code works but have ran into a problem. since it is already have way through the year and i am trying to take all the info that was save for the last 6 months into the database so that my office my use the database i am running into a problem. i have imported all the control numbers (from 09-0001 to 09-0810) and when i go in to add a new record it comes up with a error when i try to save it. what it is trying to do is instead of giving me the next number (09-0811) instead it is try to give me the number 09-0001 which is already in the system.

any help
eric
 

Watch MrExcel Video

Forum statistics

Threads
1,127,359
Messages
5,624,238
Members
416,018
Latest member
mirceaon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top