473,848 Members | 1,628 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Generate Number on MS. Access


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!!

Jul 26 '06 #1
15 3005
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!!
Jul 27 '06 #2
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

Jul 27 '06 #3
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
Jul 28 '06 #4
"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
Jul 28 '06 #5
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
Jul 31 '06 #6

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.

Jul 31 '06 #7
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

Aug 1 '06 #8
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
Aug 1 '06 #9
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.
Aug 1 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
8294
by: Laphan | last post by:
Hi All This is a strange request, but I just cannot fathom how to do it. In theory the requirement is very basic, but in practise its a noodle!! I have 10 team names like so: Team A Team B
6
3824
by: Sebastien | last post by:
Hi, I am building a products database, linking sales and production. Each part has a unique sales Stock Code and Production Number. The sales stock code is a combination of letters and numbers taken from the part name (BrandA 300 4 Bladed : B3004B). The production number is a 4 or 5 digits number with first figure(s) indicating category (BrandA 300 4 Bladed: BrandA is category 1000. This Product would be 1003 if it was the third...
29
3766
by: Lauren Wilson | last post by:
Does anyone know how the following info is extracted from the user's computer by a Front Page form? HTTP User Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.5) Gecko/20041107 Firefox/1.0 I only ask because I believe I could use the same info as part of a scheme to generate a unique (or at least less common) serialized id code for the user's computer as part of a software locking and activation system. If I had a DLL...
1
2778
by: Robert Davis | last post by:
I would like to be able to create a recordset in MS Access 2000 that allows a macro to run and create a report for each ID that appears in the recordset. So I thought that I would use a scheduling program to start the database and then have an AutoExec Macro start the process. My issue is that I can not seem to get any results to generate a report. I am somewhat confused as to what the difference is in DAO and ADDO. Here is the code...
2
15816
by: jmpinto | last post by:
I have a field on a form that I would like to automatically generate a number, in sequential order, each time a new record is created, and be stored in a field. The problem is that I do not want this field to be an autonumber field because if a new record is opened by mistake and then not used, the autonumber's field would not be in sequential order any more. Is there a way to do this? Maybe something along the lines of matching the...
12
5234
by: Jim Michaels | last post by:
I need to generate 2 random numbers in rapid sequence from either PHP or mysql. I have not been able to do either. I get the same number back several times from PHP's mt_rand() and from mysql's RAND(). any ideas? I suppose I could use the current rancom number as the seed for the next random number. but would that really work?
11
4510
by: Alan Mailer | last post by:
A project I'm working on is going to use VB6 as a front end. The back end is going to be pre-existing MS Access 2002 database tables which already have records in them *but do not have any AutoNumber* fields in them. Correct me if I'm wrong, but I'm assuming this means that I cannot now alter these existing Access tables and change their primary key to an "AutoNumber" type. If I'm right about this, I need some suggestions as to the...
2
5831
by: G | last post by:
Hello Firends I want a sample code for developing an " auto genearte number " and want to store in database. Please if you find any code please send Thanks in Advance G
4
1808
by: Michiel Rapati-Kekkonen | last post by:
Hi, I would like to generate a versionnumber (and save it in a internal table) on the moment I make a mde. Then there would also be such version tabel in the central, linked data mdb. When the mde is opened it checks if the number in the datafile is higher, lower or the same. If it is lower than it's own, it would update the number in the datafile. If it is lower it knows that there is a newer version of the application and
0
9735
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10997
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10661
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10718
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10347
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9497
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7889
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7067
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
4542
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.