• New Patreon Tier and Early Access Content available. If you would like to support AVForums, we now have a new Patreon Tier which gives you access to selected news, reviews and articles before they are available to the public. Read more.

MS Access as a numbering system

everett_psycho

Distinguished Member
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

Distinguished Member
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

Distinguished Member
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 :mad:
 

Clownfish

Established Member
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.:confused:
 

The latest video from AVForums

CES 2023 Round Up: New TV Lineups for 2023 from LG, Samsung, Panasonic, Hisense & TCL
Subscribe to our YouTube channel
Support AVForums with Patreon

Top Bottom