Generate random and unique number (1 Viewer)

Hi all,

I'm having problem generating an unique customer number.
It should always have 7 digits and the 'SSE' prefix (for example 'SSE1234567')

Can you see what's wrong?


I think the Do While construction is not correct...?

why do you think it's not working? wrong number? stays in loop? or what?

And why not just use dmax+1

Thanks for the reply.

It triggers the 'Duplicate found' line very often.
Usually it does find a random option, but I have the feeling the code is not good enough.

I didn't use the Dmax function because I prefer having a random result.

I would expect to see the RANDOMIZE statement at the beginning of the code to ensure a "random" seed.

think you need to use randomise first - see this link

http://www.techonthenet.com/excel/formulas/randomize.php

ah - see stopher has already responded

Aha, excellent, thanks guys.

I'll see if this improves the results.

MrHans,

I'm having problem generating an unique customer number.

Click to expand...

What is wrong with CJ's suggestion DMax + 1?
Why RANDOM?
What is the purpose of "SSE"?

I see posters requesting this often, but they're usually looking for a unique identifier. The purpose of Access' autonumber --for use by the database system itself.

If the identifier has some meaning to users, then make a separate field and avoid autonumber. Dmax +1 does the job.

Please tell us why Random is needed.

Hi Jdraw,

It's a customer number that customers can use refer to when they contact our company.
The SSE part is a reference for us, that identifies the product-group.

I don't want to give my customers any insight on the amount of customers I have. That's why I don't want to use the Dmax function.

The number is not linked to Access' internal autonumber.
My table with SSE customers has the Access autonumber as primary key.
Additionally each customer gets his own generated Customer number:
SSE + 7-digit random and unique number.

Is my code, with the addition of Randomize, the best way to get this unique customer number?

as stopher said

if you do not randomize first, you get a duplicate sequence of random numbers. useful for testing, but probably not when in real use.

even so, rnd() may give duplicates anyway, so it would be a good idea to check,

Yes, that's clear.

That's why I created this Do While loop.
It doesn't matter if it find a duplicate, as long as it detects it, generates a new one and check again.

I think the problem is solved with the Randomize addition though.
Thanks again.

Unless I am totally lost, you can ask Access to generate a random autonumber. You could then use that autonumber as your customer number, since you wanted it to be random anyway. (Of course, you might end up with some fairly big customer numbers, but that is the nature of randomness.) You can still use that same autonumber field as a PK for the table. If the SSE is constant, just add it in when you format any reports or displayable entities. If it is NOT constant, then does the randomness requirement apply for the other values that this text field could assume? Because if so, you can use the random autonumber there, too. Then SSE would just be one of the possible values of your product-group info field.

Remember, the PK value, whatever it is, has no other meaning than a unique number to identify some base record. If you had a "natural" key you would use it, but you are asking for a synthetic (random) key. The randomness doesn't change the implications of using an autonumber. That number cannot be manually changed once the record has been entered, but it is still a valid number that one could choose to identify something.

Some folks here will say that you should not use an autonumber as an ID, but I disagree. It is my opinion that as long as you respect it as an ID that is immutable after definition, an autonumber is as good a number as any other. Just remember that it has no "real" meaning except as a fast way to locate a particular record in a table for which that field is the PK. But since you wanted a random number anyway, you are already meeting that condition.

you can ask Access to generate a random autonumber.

Click to expand...

Good point, you can - but it will include negatives as well - don't think you can limit it to positive only or the range so number length will vary.

You could use the abs function to convert negatives to positives and truncate it if necessary to get to 7 digits although this will provide a slight opportunity for duplication.

Back to your original question and ignoring the discussion about how to generate the random number and/or use autonumbers ...
Unless I'm misunderstanding it totally, surely you've got the condition in your Do While clause wrong - shouldn't it be
You are using two different tables in the Dlookups. If they should be the same, then you wouldn't need the two lines before the Do While, instead just make sure that Result is null at the start.