Update Query: Random Number No Duplicates

[erinwithane]

Hello

I need some help. I have a form which allows the user to add new records to a table. After the user had entered all the information into the form, they click a command button to add the record. In addition to adding the new record, my command button runs an query which is supposed to generate a random number between 1 & 1,000,000,000 and update the record ID field with that number.

Here is the formula I have been using in the “update To” now of my query: Int((1000000000-1+1)*Rnd()+1)

My problem is that I keep getting duplicates. You would think that the chances of getting a duplicate number would be pretty small with this large of a range, but I get a duplicate almost every time.

I have tried indexing (No duplicates) the field in the table, but that did not work. When my query generated a duplicate number, the record was just not added to the table.

I also tried a two step approach:
1-Make a table of all in use record ID numbers from my table (tblIdNo)
2-Update new record with a random number that is not in tblIdNo

This was a no-go too

Can somebody please help me build an update query that will update each new record added to the table with a random number between 1 & 1,000,000,000 without any duplicates? This seems like it should be so simple, and I am starting to get really frustrated.

I would prefer to accomplish this through a query/queries (if possible) rather than with 100 lines of code. This database is not for me, it’s for another group, and the individuals in this group are totally freaked out by code.

Thank you

 

[billmeye]

1) Why a random number? Instead consider using field type Autonumber and allow Access to add an incrementing number.

2) If you want to verify the new random number is unique you will need to use VBA code, a query can not do that.

Dim TestNumb as long
GetNewNumb:
TestNumb = Int(1000000000*Rnd())
If Dcount("*","tblYourTable","[RandomNumber]=" & TestNumb) = 0 Then
'No duplicates so assign value
Me.RandomNumber = TestNumb
Else
'duplicate so try another number
Goto GetNewNumb
End If

 

[erinwithane]

Hi billmeye,

There are two types of records bing entered in to my table. New cases that have never existed before (these get the unique random number), and follow ups to those cases. Any follow up records added to the table should have the same ID number as the origional case. This is why I cant use auto number.

Im not really sure what to do with this code…..do I just use this inplace of my query or do I use with with my query?

I tried running it but got an error

Thanks for your help

 

[billmeye]

this code should be run in the data entry form in the forms Before Insert event. The Before Insert event will fire upon your first action when entering data. You will need to replace my generic identifiers with your specific names.

If Dcount("*","tblYourTable","[RandomNumber]=" & TestNumb) = 0 Then
'No duplicates so assign value
Me.RandomNumber = TestNumb