Generating random and unique 3 digit code and 3 letter code

[orange]

You said you wanted to control these numbers. So you have to ensure you have covered all eventualities.


You pick a random number and for every subsequent pick, you must ensure that number has not been used. If it has then you must pick again and recheck and continue until your pick is unique. You would also have to check if all numbers have been used, and, in your case, present a message indicating that fact. Then what do you do if you have exhausted the number of unique numbers.

The point here is that using autonumbers would give you a very long list of unique numbers (not necessarily sequential nor positive). But handled by Access.

[Pommetje77]

That is correct, however As I stated I would like to control it. If the number of unique numbers are exhausted, I can decide to clean my database, remove old suppliers, or simply adapt the script so 'a' and 'z' can be opened, or I can add another digit. I don't want to end up with infinity list of codes/suppliers.
So do you want to help me, and give me a step-up how I can initiate a messagebox pop-up providing me info that I am running out of codes?

[orange]

If you are using the random numbers to uniquely identify suppliers, and you have set it up so those numbers must be in the range 100-999, then you have a max of 899 ( maybe it's 900).
So, if the supplier ids are unique, and is the PK of your table, you could use something like.

....
If Dcount("*","tblSupplier") > 890 Then
   Msgbox ("You have used over 890 random Supplier Ids " & vbcrlf  _
             "Time to think about expansion", vbOKOnly)
...

[Pommetje77]

Ok, that's it!
I should have initiated the script myself, it is an easy solution

For any one who is interested or have same problem in the future, here is what I am using for generating random and unique 3 digit number codes:

Function:

Function randomNumber(Lo As Integer, Hi As Integer) As Integer
On Error GoTo random_Error
Randomize
randomNumber = Int((Hi - Lo + 1) * Rnd + Lo)


On Error GoTo 0
Exit Function


random_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure random of Module AccessMonster"
End Function

Code under command button:

Private Sub cmdGenerateCode_Click()
Dim a As Integer
Dim z As Integer
Dim TempV As String


' Check if supplier does have code
If Me.nSupplierCode > 0 Then
Exit Sub
Else


' Check if not running out of supplier codes
If DCount("*", "Suppliers") > 890 Then
MsgBox "You have used over 890 random Supplier Ids " & vbCrLf & "It is time to think about supplier code expansion", vbOKOnly
Else


'If not, continue picking random code for new suppliers
a = 100
z = 999


TempV = randomNumber(a, z)
If DLookup("[Supplier Code]", "Suppliers", "nSupplierCode=" & TempV) > 0 Then ' Look if code already exist in table
TempV = randomNumber(a, z) ' if so, continue generating
Else
Me.nSupplierCode = TempV ' else, pass code to text box
End If
End If
End If
End Sub




Thank you very much for helping me out Orange!

[orange]

You're welcome, Happy to help.

Google "Smart Indenter for MSAccess"

This is a free addin for Access. Use it to indent your code consistently. Also, when you add code examples to forums used their code tags. In this forum you highlight the vba and then click on the # sign above the message window.

[Pommetje77]

Ok, thanks for the tip!