want a non-repeating autonumber between 10000 and 100000

[rob]

looking to generate random numbers between the 2 values in
a table. i want to pull a random number and use it for a
part number..no duplicates ~thanks rob

 

[Guest]

I'm new to Access but i do have some background in Basic so i can point you
in the right direction but i cant tell you step by step how to do what you
want to do.

Open any form in design view and click the Code button on the toolbar to
open the vba editor. Open the help file and goto the index tab. Type in
"rnd" and double click "Rnd" in the topics section.

This gives you a good example on how to generate a random number between 2
other given values. You have to know which value is the lower boundry and
which is the upper so if your field values are not consistently higher or
lower by column, you will have to use code to distinguish them.

Hope this helps...

 

[Guest]

thanks very much, i'll give it a shot! have a great
weekend ~rob

 

[Ed Warren]

Ozzone's method will give you the numbers but will not assure the
"non-repeating" requirement.

If you have a really good reason for the requirements for a non-repeating
random number between 10,000 and 100,000

1. build a table with one field (PartNumber) then enter the values 10,000 to
100,000 in that table.
either via code or use excel to build the numbers, then import.

2. build another table with two fields ([randomNumber]: set as key field,
autonumber, long integer, New Values: Random), [PartNumber], number, long
integer

3. build an append query to append all the records from the first table into
the second.

Now you have a table with two columns

Now you have a table with part numbers matched to random numbers that are
unique, but not within the given range.

4. build yet another table with [SelectOrder] as autonumber, KeyField, New
Values: Increment, [Partnumber], number, long integer)

5. Now build an append query based on table two (order by randomNumber
ascending) and append the PartNumber to the table.

Now you have partnumbers in the assigned range randomly assigned to integers
1-90,000 which are unique.

Build your Parts Table, use [PartID], autonumber, increment, keyfield

Then anywhere you need the random [PartNumber] add the crossreference table
to the query and pull out the random number.

e.g.

CrossRefTable
[SelectOreder] [PartNumber]
1 12345
2 98765
3 xxxxxxx

Parts Table

PartID PartDescription
1 widget, large, left handed
2 widget, small, right handed
3 widget, large, right handed
ect.

with a query would result in:

[partID] =1
[PartNumber] = 12345
[PartDescription] = widget, large, left handed

etc.


Now when you database grows past 90,000 widgets you can just add new numbers
to the crossref and proceed.



Ed Warren