MS Access as a numbering system

[everett_psycho]

I'm trying to make a document numbering system in access where i need the next unique number in a sequence relying on other criteria. In simple i'm trying to get this:

Unique reference number
1234-00-DB-100-000001

The first numbers all mean something and will be different for all numbers and the 000001 needs to be sequential but if i change any of the parts before it the last number needs to allocate the next available one in that sequence. Is there an easy way to make it do this without tinkering with VB scripts too much? I tried looking up max in a query but the ncan't amend the blank one.

If i've been unclear in my jibberish i can answer any questions if it helps you help me

 

[Singh400]

I would pull together seperate fields. By that I mean, have a seperate field for 1234, 00, DB , 100, 000001 (define this one as an autonumber).

Then on the form side, pull them all together using a technique I can't quite remember. Might be text command or something.

 

[everett_psycho]

i managed that much but the autonumber is overall, i need it to restart from 1 on each different number type which is where I'm struggling.

to concatenate them is [field 1] & "-" & [Field 2] & "-"..... ect. but i needed the last number to do it with. At the moment i'm trusting the guy using the system to not mess it up and start making up numbers, i think that will last until he gets to 2

 

[Clownfish]

I think I understand. So you will have a table with these IDs in. On a form you could get them to enter the 1st part. On update recalc the form. On the form have a field that does a dmax calculation on a query. The query filters the table based on what the user has entered. Within the query use RIGHT and convert the end bit to a value.
Now the field on the form should return the highest value. So add 1 to it and the convert it back to a string. It is probably easier to do this as a separate field. Then have another field to concatenate the 1st part to this newly calculated 2nd part and use this field as the source for the ID in the table. I hope this makes sense.