0

I have just dived into access for the first time. Pretty easy So far. I have created a form for my employees to use to create to job tickets. What I am trying to do now, is to number the tickets in the format 17-0001, 17-0002 etc automatically. This number is the unique identifier for all jobs, and is what will be primarily used in the future for queries. I found I could do this with autonumber prefix but I see thats not a good idea. In addition to the sequencing, I also need the numbering to start over each year. SO jan 1 2 018 first job would be 18-0001. I beleive i need to use dmax to accomplish this, but I am not sure how to implement it.

Any help is appreciated!

Erik A
  • 31,639
  • 12
  • 42
  • 67
user540174
  • 1
  • 1
    All I can say is: this is a bad idea. Having a string as a record identifier will slow down all queries linking on it, needing to calculate a `DMax` when inserting a query will also slow that down by a lot depending on how many queries you have, it will complicate any future query (you can use a default, but any custom insert query will need to calculate that default for any record), and it will introduce possible concurrency issues on insert (if two users insert a record at the same time). Why do you want to do this? – Erik A Nov 23 '17 at 21:05
  • "the way its always been" is the best answer. but also, we currently export the data in that format for use in accounting software. what if instead of using a string as an identifier, you run a script that finds the next number and inserts it as text into the table. this could also prevent duplicates. i would have no idea how to code this, but it may get me going down the right path. we are talking about 1000 entries per year. – user540174 Nov 23 '17 at 23:04
  • What access version? You might be able to add table triggers for this job. – Krish Nov 23 '17 at 23:06
  • access 2016. thanks for the replies btw – user540174 Nov 23 '17 at 23:08

0 Answers0