Correspondence Reference Number Generation

[Ildanach]

Hey folks,

You will all have to forgive my newbie status and possibly silly questions but I was hoping somebody out there can help me with a database that I need to create for work.

I need to create a form that will generate reference numbers for incoming and outgoing corresponndence for the projects that my department works on.

When the form is opened I want the form to check what the last reference number used was and generate the next one in sequence.

The format for the reference numbers will be;
PROJECT/Month-Year/0100 eg: ACC/0409/0123

When the form is used I want it to update the month-year part automatically and use a drop down menu for the project part. The four digit number at the end will as I said increment with each piece of correspondence entered per each project.

Any tips and ideas/suggestions would be massively appreciated. I am a complete noob when it comes to access and am using 2003 and 2007 versions.

Thanks in advance!
Dave

 

[Uncle Gizmo]

There's quite are a bit of information within this forum about what you are trying to do. It's a very common problem, which has been broached and solved many times within here.

There are several traps you can fall into, the first one is using that number you describe as the linking number between that record and records related to it in other tables. Avoid this at all costs, have an ID column within your table which uses the auto number feature to give you a unique number for each record in your correspondents table. Always use this hidden number to link the records to other tables where necessary.

You could build this number in one field, however that's a very limiting solution, bear in mind that really this number is for the viewers reference, with that in mind consider having separate fields for for project, month-year, and the reference number. Now all you do is bring these together in one text box on the form and the user sees the complete number, however the numbers components are stored in separate fields, this makes it much easier to manipulate these separate components.