why does it have to be for what you describe? You have categories, so ### is going to be driven by a form combo selection? Then this
The second part should be auto filled with the first available number in the range of 1001-9999
means you will need a table of values with a way of denoting which ones have been used already. A few ways to do that. Thus getting the next number can be as simple as a query or DLookup. The biggest hurdle in trying to make these sequential will be dealing with the potential to "grab" one of these numbers and not complete the record thereby losing it, or having 2 or more people doing the same process at the same time. That situation will exist regardless of what approach you take.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.