Access - Creating a non-duplicate sequential numbering system 2

[kumar1098]

I would like to create a form that will auto generate a non-duplicate sequential number for certain types of projects inputted. For example, if one selects the “Special Projects” dropdown box, I need the program to automatically assign the project as SS-1001, SS-1002 and so forth. If someone selects the drop down box with nothing in it, then it will automatically assign the project as 6001, 6002, 6003 and so forth. Any help on this would be greatly appreciated.

 

[xlbo]

You need a set of rules and a table to hold the latest value by type.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[fneily]

Should post in either:
Microsoft: Access Forms
Microsoft: Access Modules (VBA Coding)

"someone selects the drop down box with nothing in it" Maybe a selection could be called NotSpecial, or whatever. Then on the AfterUpdate event of the combobox, you test for that selection. If it's true, then check the Dmax of the highest just numeric project number and add 1. Else concatenate SS- to the next highest alpha project number.
You didn't state if you knew VBA coding. If not, post in the above mentioned forum.

 

[kumar1098]

I do not know VBA coding.

 

[xlbo]

May be possible without VBA (although could be very convoluted) - potential to use macros in Access to fire queries and stuff after update but would be much simpler in VBA...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question