You are here:

Using MS Access/Assigning Sequential Numbering with Project Code

Advertisement


Question
Hello Richard. I am a beginner and have been using your awesome tutorials to learn Access. I am trying to build a multi-user spreadsheet for our office to use as a project log. For each project, I want it to create a project number sequentially and automatically in the format WP15-001 where the 001 would be 002 for the next project and so on. I know I should not use auto number, so I'm trying the DMax function. I've created a macro on "After Insert" and have it to "Edit Record" and "Set Field". Right now I have Nz(DMax("[ProjectNumberInt]","ProjectListT"),[0])+1, but it keeps returning a 0 null value and not adding. I was going to then have another ProjectNumber data set to then display the full WP15-001 value. Please help!

Answer
You don't need an event for that, just put the DMAX in the Default Value property for that field in Form Design View.

=DMax("ProjectNumberInt","ProjectListT")+1

As long as you seed the table with at least one record, you won't have to worry about using NZ.

Don't put your 0 inside square brackets. That refers to a FIELD called 0. Since you don't have any spaces in your field names or table names, you don't have to worry about using brackets.

Hope this helps.

Be sure to visit my web site and watch my FREE 3-hour-long Microsoft Access Video Tutorial

Cordially,
Richard Rost
Microsoft Access MVP
Computer Learning Zone  

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Richard Rost

Expertise

I am happy to answer any kinds of questions about Microsoft Access - from basic table design to advanced VBA programming. Also, please feel free to check the Access Tips & Tricks section of my web site, and free Access 101 tutorial.

Experience

I am the president of AccessLearningZone.com and 599CD.com. I specialize in Microsoft Access Tutorials. I have been teaching Access in the classroom since 1994, and online since 2002.

Education/Credentials
I am a self-taught Access expert. I have been building databases for clients since the early 90s. You can see a sample of my Access Tutorials on my web site at 599CD.com

©2016 About.com. All rights reserved.