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

Autonumber: Company- & Year & Number

P: 5
Dear all,

I just canít find the solution to this.

I want to create a Field that will work as an autonumber.
The field should be automatically generate, each time a new entry is put into the system. It should look like this

Company Initials "TRN-" the current Year "08" slash "/" and added to this an ongoing number

TRN-08/001
TRN-08/002
......
TRN-09/001
What is the difference if I want the counter to start with 25

TRN-08/025


Hope someone can help me

THX in advance
Jan 11 '08 #1
Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,357
What have you tried so far?
Jan 11 '08 #2

Minion
Expert 100+
P: 108
Sounds like you need an event that will run for the On Current event of the form. With that in mind I'll put together a little example of what I mean. For the example the field that would hold the autonumber will be called txtIndex and be bound the the index field which I take is the first column.

The event would be something like:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. Dim indexStr as String
  3. Dim dbs as DAO.Database
  4. Dim rst as DAO.Recordset
  5.  
  6.    Set dbs = CurrentDB
  7.    set rst = dbs.OpenRecordset("<<table name here>>")
  8.  
  9.    If rst.BOF Then
  10.       rst.Close
  11.       Exit Sub
  12.    Else
  13.       rst.MovePrevious
  14.       indexStr = "TRN-" & Format(Now,"yy") & "/" & Format((Right(rst.Fields(0),3) + 1), "000")
  15.       txtIndex = indexStr
  16.    End If
  17.  
  18. 'Just some clean up. Not even sure it's needed.
  19.    rst.Close
  20.    set rst = Nothing
  21.    set dbs = Nothing
  22. End Sub
  23.  
Hopefully this will work as I planned. What it does is looks at the previous index and increment it by 1. As it stand this only does 3 digits, so more programing would be needed if you wish to allow the final 3 numbers to be more than 999.

Hope this helps.

- Minion -
Jan 11 '08 #3

Post your reply

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