How to create a custom random number AutoNumber with no repeats (1 Viewer)

TGPCB

New member
Local time
Today, 12:10
Joined
May 17, 2022
Messages
1
So I am creating a new table by pasting an excel spreadsheet of existing parts, the parts currently have no part number.
I want to create an AutoNumber field that generates a fixed prefix followed by a random 5 digit positive integer.

What I am doing currently:
Creating a new field and setting the datatype to Autonumber
Set new values to Randon
In Format typing by prefix plus 5 zeros to indicate 5 digits: "RES-"00000
Tried all combinations of options for indexed.

This generates a sequential set of numbers in the form RES-00001, RES-00002.... in the existing data records which is not what I want, I want it to be random.
Every new record has ######## in the autonumber field.

How can I do what I want?

Assign a random, unordered number following a prefix, to a field in existing records with no repeats
All new records to automatically have a random number assigned (following a prefix) with no duplicates with existing records

Hopefully, you all get what I mean as I'm not the best at explaining.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:10
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

I suppose you could use a custom function to do what you want, if the built-in ones can't do it for you.
 

plog

Banishment Pending
Local time
Today, 07:10
Joined
May 11, 2011
Messages
11,613
Why? What value are you adding to your project by doing it this way? Why are randomly assigned unique so magical?

Set it to an autonumber and your done. It will be unique, it will be handled by the system it will involve no coding on your part at all. There is no downside except that which you imagine to exist.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:10
Joined
Feb 28, 2001
Messages
27,001
First, HOWEVER you generate the number, just generate it as a number and nothing else. Then when you need it to be PREFIX-NUMBER, use a query or a Format function to make it what you want it to be. If you need it to be a key, you can make it a compound key - but the truth is that if the prefix is ALWAYS the same, then the number itself is all you need for a PK. In which case keeping the prefix is like ... what is the old phrase? Having teats on a bull.

Using the system-generated random autonumber won't help since you can't guarantee 5 digits. You could get more. You could get less. Because it is RANDOM. What would you do if that number had to exceed 100,000 (SIX digits)?
 

jdraw

Super Moderator
Staff member
Local time
Today, 08:10
Joined
Jan 23, 2006
Messages
15,364
Please give us your rationale for doing this?
Why random?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:10
Joined
May 7, 2009
Messages
19,175
you can use Short Text instead of Autonumber.
see the code in this demo.
 

Attachments

  • Random.accdb
    504 KB · Views: 236

strive4peace

AWF VIP
Local time
Today, 07:10
Joined
Apr 3, 2020
Messages
1,003
Please give us your rationale for doing this?
Why random?
good question, Jack. If I were to guess it's so people don't know what order the records were created, or how many records there are ... what other reason could there BE?

as @theDBguy said, if you don't want negative numbers, then you need to write your own function to run when records are created

and as mentioned, if they all start with 'RES-' then that doesn't have to be stored. @TGPCB (what does your user name stand for? looks like you like random letters too ;) )
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 08:10
Joined
Jan 23, 2006
Messages
15,364
It could be:
- an academic exercise
- a misunderstanding of autonumbers
- new to Access/database

I note that TGPCB has just joined the forum and may be unaware that autonumber can be set to Random.
Perhaps OP will return and tell us more.....
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 12:10
Joined
Sep 21, 2011
Messages
14,051
No, the O/P said they set the autonumber to random
 

jdraw

Super Moderator
Staff member
Local time
Today, 08:10
Joined
Jan 23, 2006
Messages
15,364
Paul,


You are quite correct. The OP said they used random,but also
This generates a sequential set of numbers in the form RES-00001, RES-00002..

Seems that something in his/her set up is at issue. Underlying question, "Why this set up"?, remains.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:10
Joined
May 21, 2018
Messages
8,463
Should be very simple to do.
1. Write a function that creates a RN (optionally within a given range)
2. Loop until that number does not exist within a given table
3. return number.
 

Users who are viewing this thread

Top Bottom