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