Re: Generate Number on MS. Access


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" <yhtong86@xxxxxxxxx> wrote in news:1153948531.751148.100520
@i3g2000cwc.googlegroups.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(Optional 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.

.