Generate Number on MS. Access

[Orchid]

Hello,
I am looking to generate a unique ID field on MS. Access. The ID is
with 10 digits with the combination of 5 Letters from the 26 letters
and 5 Numbers from 1 to 9. The letters and numbers are picked
randomly. When I click a button with the VB code on a form, it will
create the ID on a table call "Customer table". Can someone help me on
how to write the VB code to generate the ID??

Thanks in advance for your help!!

[Tom van Stiphout]

On 26 Jul 2006 14:15:31 -0700, "Orchid" <yh******@yahoo .comwrote:

What is the purpose of this field? Why not use an AutoNumber?

-Tom.

>
Hello,
I am looking to generate a unique ID field on MS. Access. The ID is
with 10 digits with the combination of 5 Letters from the 26 letters
and 5 Numbers from 1 to 9. The letters and numbers are picked
randomly. When I click a button with the VB code on a form, it will
create the ID on a table call "Customer table". Can someone help me on
how to write the VB code to generate the ID??

Thanks in advance for your help!!

[Mr. T]

Try this. It should work
Sub testing()
Dim str As String
Dim num As Double
Dim uniqueKey As String
'the following will create a key, then you must test to see if is
unique

GenerateNumber:
num = Int(Rnd * 100000)
str = "yourstringhere "
uniqueKey = num & str
MsgBox uniqueKey
' this will test to see if it is unique
Dim rs As Recordset
Set rs = CurrentDb.OpenR ecordset("table 1")
rs.MoveFirst
Do While Not rs.EOF
If rs!fieldwithkey = uniqueKey Then GoTo GenerateNumber
rs.MoveNext
Loop
End Sub

[Orchid]

Thanks so much for your reply!
I created a table called "Table1", but still had an error: "Run-time
error'13': Type Mismatch". What else do I need to do?
Is there a way to randomly select 5 letters from the 26 ABC letters? I
tried but didn't work.
Your help is greatly appreciated!

Mr. T wrote:

Try this. It should work
Sub testing()
Dim str As String
Dim num As Double
Dim uniqueKey As String
'the following will create a key, then you must test to see if is
unique

GenerateNumber:
num = Int(Rnd * 100000)
str = "yourstringhere "
uniqueKey = num & str
MsgBox uniqueKey
' this will test to see if it is unique
Dim rs As Recordset
Set rs = CurrentDb.OpenR ecordset("table 1")
rs.MoveFirst
Do While Not rs.EOF
If rs!fieldwithkey = uniqueKey Then GoTo GenerateNumber
rs.MoveNext
Loop
End Sub

[Lyle Fairfield]

"Orchid" <yh******@yahoo .comwrote in news:1153948531 .751148.100520
@i3g2000cwc.goo glegroups.com:

>
Hello,
I am looking to generate a unique ID field on MS. Access. The ID is
with 10 digits with the combination of 5 Letters from the 26 letters
and 5 Numbers from 1 to 9. The letters and numbers are picked
randomly. When I click a button with the VB code on a form, it will
create the ID on a table call "Customer table". Can someone help me on
how to write the VB code to generate the ID??

Thanks in advance for your help!!

You could try this:

Public Function RandomString(Op tional ByVal Length As Long = 10, _
Optional AlphaOnly As Boolean = False, _
Optional NumericOnly As Boolean = False) As String
Dim l As Long, b As Byte
If Length = 0 Then Exit Function
If AlphaOnly And NumericOnly Then
AlphaOnly = False
NumericOnly = False
End If
Randomize
For l = 1 To Length
Do
b = 48 + Fix(Rnd() * 75)
Loop Until (b < 58 And Not AlphaOnly) Or _
(b 64 And b < 91 And Not NumericOnly) Or _
(b 96 And Not NumericOnly)
RandomString = RandomString & Chr(b)
Next l
End Function

It's old, old code I wrote for a friend/client; I think I have not used
it myself. I think I would not write it as it is today.

As for a button, you would call it something like:

Private Sub Command10_Click ()
MsgBox RandomString()
End Sub

This shows a message box. You want to: "create the ID on a Table". I have
no idea what that means.

Ahhh ... I do remember why my friend/clent never really used this. He
wanted to assign passwords with it. Of course, no one could remember:

x6xUm18C43

Oops, I see this is ten alpha or numerics, but not five of each as you
requested. Oh well, I won't charge you for it then.

--
Lyle Fairfield

[Orchid]

Thanks for your reply, Lyle!
I don't know that much about the codes. sorry that I have a question:
What are these Numbers (i.e. 48, 75, 64,96) for? I want to know what
they are so I can work on the codes and make it 5 numbers and 5 letter.

Thanks for your help!!

Lyle Fairfield wrote:

"Orchid" <yh******@yahoo .comwrote in news:1153948531 .751148.100520
@i3g2000cwc.goo glegroups.com:

Hello,
I am looking to generate a unique ID field on MS. Access. The ID is
with 10 digits with the combination of 5 Letters from the 26 letters
and 5 Numbers from 1 to 9. The letters and numbers are picked
randomly. When I click a button with the VB code on a form, it will
create the ID on a table call "Customer table". Can someone help me on
how to write the VB code to generate the ID??

Thanks in advance for your help!!

You could try this:

Public Function RandomString(Op tional ByVal Length As Long = 10, _
Optional AlphaOnly As Boolean = False, _
Optional NumericOnly As Boolean = False) As String
Dim l As Long, b As Byte
If Length = 0 Then Exit Function
If AlphaOnly And NumericOnly Then
AlphaOnly = False
NumericOnly = False
End If
Randomize
For l = 1 To Length
Do
b = 48 + Fix(Rnd() * 75)
Loop Until (b < 58 And Not AlphaOnly) Or _
(b 64 And b < 91 And Not NumericOnly) Or _
(b 96 And Not NumericOnly)
RandomString = RandomString & Chr(b)
Next l
End Function

It's old, old code I wrote for a friend/client; I think I have not used
it myself. I think I would not write it as it is today.

As for a button, you would call it something like:

Private Sub Command10_Click ()
MsgBox RandomString()
End Sub

This shows a message box. You want to: "create the ID on a Table". I have
no idea what that means.

Ahhh ... I do remember why my friend/clent never really used this. He
wanted to assign passwords with it. Of course, no one could remember:

x6xUm18C43

Oops, I see this is ten alpha or numerics, but not five of each as you
requested. Oh well, I won't charge you for it then.

--
Lyle Fairfield

[Lyle Fairfield]

Orchid wrote:

Thanks for your reply, Lyle!
I don't know that much about the codes. sorry that I have a question:
What are these Numbers (i.e. 48, 75, 64,96) for? I want to know what
they are so I can work on the codes and make it 5 numbers and 5 letter.

Thanks for your help!!

Lyle Fairfield wrote:

"Orchid" <yh******@yahoo .comwrote in news:1153948531 .751148.100520
@i3g2000cwc.goo glegroups.com:

>
Hello,
I am looking to generate a unique ID field on MS. Access. The ID is
with 10 digits with the combination of 5 Letters from the 26 letters
and 5 Numbers from 1 to 9. The letters and numbers are picked
randomly. When I click a button with the VB code on a form, it will
create the ID on a table call "Customer table". Can someone help me on
how to write the VB code to generate the ID??
>
Thanks in advance for your help!!

You could try this:

Public Function RandomString(Op tional ByVal Length As Long = 10, _
Optional AlphaOnly As Boolean = False, _
Optional NumericOnly As Boolean = False) As String
Dim l As Long, b As Byte
If Length = 0 Then Exit Function
If AlphaOnly And NumericOnly Then
AlphaOnly = False
NumericOnly = False
End If
Randomize
For l = 1 To Length
Do
b = 48 + Fix(Rnd() * 75)
Loop Until (b < 58 And Not AlphaOnly) Or _
(b 64 And b < 91 And Not NumericOnly) Or _
(b 96 And Not NumericOnly)
RandomString = RandomString & Chr(b)
Next l
End Function

It's old, old code I wrote for a friend/client; I think I have not used
it myself. I think I would not write it as it is today.

As for a button, you would call it something like:

Private Sub Command10_Click ()
MsgBox RandomString()
End Sub

This shows a message box. You want to: "create the ID on a Table". I have
no idea what that means.

Ahhh ... I do remember why my friend/clent never really used this. He
wanted to assign passwords with it. Of course, no one could remember:

x6xUm18C43

Oops, I see this is ten alpha or numerics, but not five of each as you
requested. Oh well, I won't charge you for it then.

--
Lyle Fairfield

Hmmm ...

48 is the ascii code for zero ("0").
48 + 75 = 123 the ascii code one greater then the ascii code for "z".

What we do here is get a random number between zero and one. We
multiply it by 75 and lop off any fractions ; thus 74 is the maximum
number we can get and zero is the minimum.

So our random number gives us a range of 48 (0) to 122 ("z").

We use that number unless it's 59 to 64 or 91 to 95; these codes do not
represent alpha letters or numerals.

Chr(number) gives us our letter or numeral. For instance Chr(65) gives
us "A"

I suppose if you used something like this:

Public Function FiveandFive()
Dim b As Byte
Dim d As String
Dim s As String
Dim u As String
s = RandomString(5, True, False) & RandomString(5, False, True)
Randomize
Do
b = Fix(Rnd() * 10) + 1
d = "[" & b & "]"
If InStr(u, d) = 0 Then
u = u & d
FiveandFive = FiveandFive & Mid$(s, b, 1)
End If
Loop Until Len(FiveandFive ) = 10
End Function

with Random String it might get what you want. But this is ghastly
inefficient.

I guess that's about it.

[Lyle Fairfield]

I really don't like the FiveandFive Function I posted. It's not
efficient at all.

This one (recursive) is about four times faster. Of course, as I just
wrote it, it hasn't been extensively tested, and I'm still not sure I
have optimized the logic to have as few operations as possible.

Public Function FiveandFiveA( _
Optional ByVal TemporaryString As String, _
Optional ByVal LettersRemainin gtoGet As Long = 5, _
Optional ByVal NumeralsRemaini ngtoGet As Long = 5)
Dim AsciiCode As Long
Randomize
If LettersRemainin gtoGet _
And (CBool(Round(Rn d(), 0)) _
Or (Not NumeralsRemaini ngtoGet)) Then
AsciiCode = 65 Or (Round(Rnd(), 0) * 32) _
+ Fix(Rnd() * 26)
LettersRemainin gtoGet = LettersRemainin gtoGet - 1
Else
AsciiCode = 48 + Fix(Rnd() * 10)
NumeralsRemaini ngtoGet = NumeralsRemaini ngtoGet - 1
End If
TemporaryString = TemporaryString & Chr$(AsciiCode)
If LettersRemainin gtoGet Or _
NumeralsRemaini ngtoGet Then
FiveandFiveA = FiveandFiveA( _
TemporaryString , _
LettersRemainin gtoGet, _
NumeralsRemaini ngtoGet)
Else
FiveandFiveA = TemporaryString
End If
End Function

[Orchid]

Thanks for your reply, Lyle!
I call the function as below:

Private Sub Command0_Click( )
Call FiveandFiveA
MsgBox RandomString()
End Sub

But the result still not 5 letters and 5 nums. What else do I miss?
Also, how can I make the Letters in UPPER CASE? Thanks for your help!!

Lyle Fairfield wrote:

I really don't like the FiveandFive Function I posted. It's not
efficient at all.

This one (recursive) is about four times faster. Of course, as I just
wrote it, it hasn't been extensively tested, and I'm still not sure I
have optimized the logic to have as few operations as possible.

Public Function FiveandFiveA( _
Optional ByVal TemporaryString As String, _
Optional ByVal LettersRemainin gtoGet As Long = 5, _
Optional ByVal NumeralsRemaini ngtoGet As Long = 5)
Dim AsciiCode As Long
Randomize
If LettersRemainin gtoGet _
And (CBool(Round(Rn d(), 0)) _
Or (Not NumeralsRemaini ngtoGet)) Then
AsciiCode = 65 Or (Round(Rnd(), 0) * 32) _
+ Fix(Rnd() * 26)
LettersRemainin gtoGet = LettersRemainin gtoGet - 1
Else
AsciiCode = 48 + Fix(Rnd() * 10)
NumeralsRemaini ngtoGet = NumeralsRemaini ngtoGet - 1
End If
TemporaryString = TemporaryString & Chr$(AsciiCode)
If LettersRemainin gtoGet Or _
NumeralsRemaini ngtoGet Then
FiveandFiveA = FiveandFiveA( _
TemporaryString , _
LettersRemainin gtoGet, _
NumeralsRemaini ngtoGet)
Else
FiveandFiveA = TemporaryString
End If
End Function

[Lyle Fairfield]

Try this:

Public Function FIVEANDFIVEUPPE RCASE( _
Optional ByVal Dummy As Variant, _
Optional ByVal TemporaryString As String, _
Optional ByVal LettersRemainin gtoGet As Long = 5, _
Optional ByVal NumeralsRemaini ngtoGet As Long = 5)
Dim AsciiCode As Long
Randomize
If LettersRemainin gtoGet 0 And NumeralsRemaini ngtoGet 0 Then
If CBool(Round(Rnd (), 0)) Then
AsciiCode = 65 + Fix(Rnd() * 26)
LettersRemainin gtoGet = LettersRemainin gtoGet - 1
Else
AsciiCode = 48 + Fix(Rnd() * 10)
NumeralsRemaini ngtoGet = NumeralsRemaini ngtoGet - 1
End If
Else
If LettersRemainin gtoGet 0 Then
AsciiCode = 65 + Fix(Rnd() * 26)
LettersRemainin gtoGet = LettersRemainin gtoGet - 1
Else
AsciiCode = 48 + Fix(Rnd() * 10)
NumeralsRemaini ngtoGet = NumeralsRemaini ngtoGet - 1
End If
End If
TemporaryString = TemporaryString & Chr$(AsciiCode)
If LettersRemainin gtoGet Or _
NumeralsRemaini ngtoGet Then
FIVEANDFIVEUPPE RCASE = FIVEANDFIVEUPPE RCASE( _
Dummy, _
TemporaryString , _
LettersRemainin gtoGet, _
NumeralsRemaini ngtoGet)
Else
FIVEANDFIVEUPPE RCASE = TemporaryString
End If
End Function

To use the function just point to it:

MsgBox FIVEANDFIVEUPPE RCASE()

or

Debug.Print FIVEANDFIVEUPPE RCASE()

or

SomeString = FIVEANDFIVEUPPE RCASE()

or

DBEngine(0)(0). Execute "UPDATE Employees SET Password =
FIVEANDFIVEUPPE RCASE([EmployeeID])"

What does Dummy do? Nothing! It simply ensures that when we use it in a
Query designating any field as Dummy, we will generate a new
FIVEANDVIVEUPPE RCASE() value for each record.