Using MS Access/Sequential Part Number Generator
Our engineering team would like to create a new method (to us) of part number generation that is simple and scalable. We have 10 users at the moment.
I'd like to create a simple form that can be located on each user's computer that will generate the next number in a series and record a few details (Part Number, User Name, Date, Sales Order) in a central database on a server.
I have used Access in the past and think I can handle creating this, but wanted your opinion on the complexity first, before I get in over my head and spend a lot of time on it. I also had a few questions;
1. Will Access need to be installed on each computer
2. Can multiple people have the form open at the same time
3. What should I watch out for so that multiple people can't accidentally generate the same number
4. Is there a way to copy the generated number to the clipboard, for easing pasting into a document field.
5. How many hours would you think are required for a beginner user to create this?
1. No. You can use the freely distributable Access runtime for the machine that just need to use the app.
2. Yes. You would split the database into a back end (tables) and a front end (everything else). The back end would go on a network shared drive and the front end on each individual PC.
3. see answer to #5
4. Yes, there is VBA code that would allow the user to copy the value to the Windows clipboard.
5. Please see my blog article (blog address below) on Sequential numbering. It details how to do Sequential numbering for a variety of scenarios. If you follow the instructions it shouldn't take long to build.
Whether there is a chance of generating a duplicate depends on the level of activity. The article shows how to inhibit duplicates, but they are possible if two people are seeking to generate a new number at the exact same time.
Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA