Using a button to create semi-random numbers

[Guest]

I have a database that tracks inventory. Sometimes we receive orders that
have a number pre-assigned, and sometimes we don't.

When the user goes to create a new entry in the system, I'm popping a msgbox
that asks whether or not the new item needs a number. When the user selects
"Yes" I want to generate a 10-digit # beginning with EX, for example
"EX01234567", then automatically insert that back into the form's order #
field.

A potential problem is duplicate #'s, in which case I want to produce
another # and attempt again...preferably without giving the user any error
messages.

Is there a way to generate a #, verify it as unique, if the test fails
recall the script to generate and test a new number?

Any help would be greatly appreciated.

 

[pietlinden]

Good challenge... thanks, I needed that!

Before I get ahead of myself...

I have a form with two controls, a textbox, "txtRecordID" and a button
"cmdAddRecord"

'---FORM CODE
Option Compare Database
Option Explicit

Private Sub cmdAddRecord_Click()
Dim intReply As Integer

'--ask user if a new ID should be generated
intReply = MsgBox("Is this a new record?", vbYesNo, "Generate a new
record ID?")

If intReply = vbYes Then
Me.txtRecordID = GenerateRandomID("txt", 10)
Else
'---do something else - like use an old ID...
Me.txtRecordID = "OLD ID"
End If
End Sub


'--MODULE CODE (which could just as well be in the form... and probably
should be)

Option Compare Database
Option Explicit

'--all these functions are Pieter's doing...
'--9/14/06

Public Function GenerateRandomID(ByVal strPrefix As String, ByVal
intDigits As Integer) As String

'---strPrefix is the text you want prepended to the ID, e.g. "txt"
'---intDigits is the number of digits you want in the suffix...
'---for example ?GenerateRandom("XYZ",3) could return "XYZ012"

Dim sngRandomNumber As Single
Dim strRandom As String 'holder variable for numbers
Dim intCounter As Integer
Dim blnUnique As Boolean

blnUnique = False 'assume the ID already exists in the table

'--keep generating randomID's until we get one that's not already
in the table
Do
'--initialize strRandom to "" so we erase any leftovers from
failed attempts
strRandom = ""
'--generate string of numbers intDigits long
For intCounter = 1 To intDigits
Randomize
sngRandomNumber = Int(Rnd * 10)
strRandom = strRandom & sngRandomNumber
Next intCounter

'---prepend the prefix
GenerateRandomID = strPrefix & strRandom

'--check for existence in the table
'--"RandomID is the *field* in my table that holds the ID's for
each record
'--"RandomIDs" is the *table* I'm looking in.
blnUnique = (DCount("[RandomID]", "RandomIDs", "[RandomID]='" &
GenerateRandomID & "'") = 0)

'---the debug line was just so I could see what IDs were being
generated
'---and so I could compare them to the contents of my table.
'Debug.Print GenerateRandomID, blnUnique

Loop Until blnUnique = True
End Function

'--The following is frivolous code... It was for testing.if the
returned IDs were unique.

Public Sub RunRandoms(ByVal strPrefix As String, ByVal intDigits As
Integer)
'--this is only here to test out the GenerateRandomID function (to see
if the values are
'--getting inserted into the table....

Dim intCounter As Integer
Dim strRandomString As String
For intCounter = 1 To 10
strRandomString = GenerateRandomID(strPrefix, intDigits)
DBEngine(0)(0).Execute "INSERT INTO RandomIDs VALUES ('" &
strRandomString & "')", dbFailOnError
Next intCounter
End Sub

'---END CODE

The code is reasonably well commented (at least I think so... it's
miles better than I usually do!). It's safe to ignore/delete the
"RunRandoms" sub. I used it to test unique values. the intDigits
argument is so I could return small enough IDs to guarantee duplicates
.... I wanted to make sure it would work right in case of duplicate and
rerun the code to generate a new ID.

Hope this helps (instead of confusing things...)

Pieter

 

[Guest]

Pieter,

The script works beautifully. I tried to email you a thank you and a follow
up question, but I didn't go through.