Using MS Access/Assigning Sequential Numbering with Project Code
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"),)+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!
You don't need an event for that, just put the DMAX in the Default Value property for that field in Form Design View.
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
Microsoft Access MVP
Computer Learning Zone