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.OpenRecordset("table1")
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.OpenRecordset("table1")
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.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

[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.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

[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.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.

[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 LettersRemainingtoGet As Long = 5, _
Optional ByVal NumeralsRemainingtoGet As Long = 5)
Dim AsciiCode As Long
Randomize
If LettersRemainingtoGet _
And (CBool(Round(Rnd(), 0)) _
Or (Not NumeralsRemainingtoGet)) Then
AsciiCode = 65 Or (Round(Rnd(), 0) * 32) _
+ Fix(Rnd() * 26)
LettersRemainingtoGet = LettersRemainingtoGet - 1
Else
AsciiCode = 48 + Fix(Rnd() * 10)
NumeralsRemainingtoGet = NumeralsRemainingtoGet - 1
End If
TemporaryString = TemporaryString & Chr$(AsciiCode)
If LettersRemainingtoGet Or _
NumeralsRemainingtoGet Then
FiveandFiveA = FiveandFiveA( _
TemporaryString, _
LettersRemainingtoGet, _
NumeralsRemainingtoGet)
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 LettersRemainingtoGet As Long = 5, _
Optional ByVal NumeralsRemainingtoGet As Long = 5)
Dim AsciiCode As Long
Randomize
If LettersRemainingtoGet _
And (CBool(Round(Rnd(), 0)) _
Or (Not NumeralsRemainingtoGet)) Then
AsciiCode = 65 Or (Round(Rnd(), 0) * 32) _
+ Fix(Rnd() * 26)
LettersRemainingtoGet = LettersRemainingtoGet - 1
Else
AsciiCode = 48 + Fix(Rnd() * 10)
NumeralsRemainingtoGet = NumeralsRemainingtoGet - 1
End If
TemporaryString = TemporaryString & Chr$(AsciiCode)
If LettersRemainingtoGet Or _
NumeralsRemainingtoGet Then
FiveandFiveA = FiveandFiveA( _
TemporaryString, _
LettersRemainingtoGet, _
NumeralsRemainingtoGet)
Else
FiveandFiveA = TemporaryString
End If
End Function

[Lyle Fairfield]

Try this:

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

To use the function just point to it:

MsgBox FIVEANDFIVEUPPERCASE()

or

Debug.Print FIVEANDFIVEUPPERCASE()

or

SomeString = FIVEANDFIVEUPPERCASE()

or

DBEngine(0)(0).Execute "UPDATE Employees SET Password =
FIVEANDFIVEUPPERCASE([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
FIVEANDVIVEUPPERCASE() value for each record.

[CDMAPoster]

Orchid wrote:

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 did ask for VB code, but here's an interesting way to do it using
SQL:

Step 1: Put an ordinary Autonumber key on [Customer table] called CID.

Step 2: Run the following Make Table query:

SELECT [Customer table].CID, Rnd(CID)*10^9 AS Seed,
"ABCDEFGHIJKLMNOPQRSTUVWXYZ" AS Alpha, "123456789" AS Digits, Seed Mod
26+1 AS Pos1, Mid([Alpha],[Pos1],1) AS Letter1, Seed Mod 9+1 AS Pos2,
Mid([Digits],[Pos2],1) AS Digit1, [Letter1] & [Letter1] & [Letter1] &
[Letter1] & [Letter1] AS Part1, [Digit1] & [Digit1] & [Digit1] &
[Digit1] & [Digit1] AS Part2, Part1 & Part2 AS MyID INTO tblForJoin
FROM [Customer table];

Step 3: Run the following Update query:

UPDATE [Customer table] INNER JOIN tblForJoin ON [Customer table].CID =
tblForJoin.CID SET [Customer table].MyID = tblForJoin.MyID;

Step 4: Check for duplicate MyID's:

SELECT MyID, Count(MyID) AS CountOfMyID FROM [Customer table] GROUP BY
MyID HAVING Count(MyID)>1;

Why is it interesting? Because I don't know how it works! Note that
when viewing the Make Table query as a Select query that some of the
values, such as Seed and MyID are ephemeral. My guess is that each
reference such as Letter1 references Pos1 which, in turn, references
Seed causing it to regenerate a new value within the same record. Can
all this be done with a single update query? I don't know that either!
Maybe a lot of cutting and pasting could yield a single expression that
does the same thing, but it doesn't seem worth it.

James A. Fortune
CD********@FortuneJames.com

[Orchid]

Thanks so much for your help!
It works perfect. I want the 10 digits ID of the Function result to
put onto a table called "Customer Table". I have it below, but with
error
*************
Private Sub Command0_Click()
MsgBox FiveandFiveA
Dim db As Database
Dim CountRow As Long
Dim cLastRow As Long
Dim i As Double
Dim rstSource As DAO.Recordset
Dim tblGenNum As DAO.Recordset
Dim ID As String
Dim CID As String
ID = FiveandFiveA()

Set db = CurrentDb
'Set rstSource = ID
Set rstSource = db.OpenRecordset("tblGenNum")

DBEngine(0)(0).Execute "UPDATE tblGenNum SET CID = TemporaryString"

'cLastRow = 100
'For CountRow = 2 To cLastRow
' rstSource.("tblGenNum").Cells(CountRow, 1).Value = i
' i = i + 500000
' Next CountRow
DoCmd.OpenTable ("tblGenNum")

MsgBox "Generate Number is Completed!"

Exit_Gen_Numbers_Click:
Exit Sub

'Err_LoopZip_Click:
' MsgBox Err.Description
' Resume Exit_LoopZip_Click

End Sub
*************
Could you help me on where is wrong?
Your help is greatly appreciated!!

Lyle Fairfield wrote:

Try this:

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

To use the function just point to it:

MsgBox FIVEANDFIVEUPPERCASE()

or

Debug.Print FIVEANDFIVEUPPERCASE()

or

SomeString = FIVEANDFIVEUPPERCASE()

or

DBEngine(0)(0).Execute "UPDATE Employees SET Password =
FIVEANDFIVEUPPERCASE([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
FIVEANDVIVEUPPERCASE() value for each record.

[Lyle Fairfield]

Orchid wrote:

Thanks so much for your help!
It works perfect. I want the 10 digits ID of the Function result to
put onto a table called "Customer Table". I have it below, but with
error
*************
Private Sub Command0_Click()
MsgBox FiveandFiveA
Dim db As Database
Dim CountRow As Long
Dim cLastRow As Long
Dim i As Double
Dim rstSource As DAO.Recordset
Dim tblGenNum As DAO.Recordset
Dim ID As String
Dim CID As String
ID = FiveandFiveA()

Set db = CurrentDb
'Set rstSource = ID
Set rstSource = db.OpenRecordset("tblGenNum")

DBEngine(0)(0).Execute "UPDATE tblGenNum SET CID = TemporaryString"

'cLastRow = 100
'For CountRow = 2 To cLastRow
' rstSource.("tblGenNum").Cells(CountRow, 1).Value = i
' i = i + 500000
' Next CountRow
DoCmd.OpenTable ("tblGenNum")

MsgBox "Generate Number is Completed!"

Exit_Gen_Numbers_Click:
Exit Sub

'Err_LoopZip_Click:
' MsgBox Err.Description
' Resume Exit_LoopZip_Click

End Sub
*************
Could you help me on where is wrong?
Your help is greatly appreciated!!

I'm not sure. Usually when there is an error a message box shows with
two options, debug or end. When we click debug, the line where the
error occurs is highlighted. This helps us identify the error. Of
course, The error message also includes a sometimes helpful error
message.

Do you want to update one record or all records.

If all records then I expect all you need is this:

1. Save the FIVEANDFIVEUPPERCASE function in a standard (ordinary
module).

2. Run your code as follows -

Private Sub Command0_Click()

DBEngine(0)(0).Execute "UPDATE tblGenNum SET CID =
FIVEANDFIVEUPPERCASE([SomeDummyField]).
End Sub

SomeDummyField (Dummy is used here in the sense of blank/place holder
not as stupid) should be replaced. While you could use CID, I would
probably choose the name of some other field in the Table, although I
can't think of any good reason for doing so.
If you don't include the Dummy field then all the records will be
updated with the same value which I think, is not what you want.

******
If you want to update just one record then we have to qualify the
UPDATE statement.

as in

DBEngine(0)(0).Execute "UPDATE tblGenNum SET CID =
FIVEANDFIVEUPPERCASE([SomeDummyField])
WHERE ID =" & Me.txtID.Value
assuming that you have text control call txtID bound to a uniquely
valued field ID.

If you are just using this value as a default entry in a form set the
default value of the control to
=FIVEANDFIVEUPPERCASE()

My recollection is that one cannot set the default value of a field in
a Table to a User Defined Function and that this is number 74,536 in
the list of reasons why we should not open Tables directly.

I have written this quickly OTTOMH. There very well may be thoughtical,
logical, syntactical, knowledgical, spellingical, hallucinationical,
senilitical, depravitical or udder errors.

[Orchid]

Thanks Lyle!
Now when I click on the Command Button, a message box shows the random
ID which created from the Function by the Event Procedure. However,
the ID doesn't save anywhere when I click the button. I want to save
the ID to a table called "Customer Table". By the way, I am using
function "FiveandFiveA", since the client prefers the letters &
numbers are all mixed. So this function works perfect. Would you
please help me on how to save the IDs into the table? Thanks a
million!

Lyle Fairfield wrote:

Orchid wrote:

Thanks so much for your help!
It works perfect. I want the 10 digits ID of the Function result to
put onto a table called "Customer Table". I have it below, but with
error
*************
Private Sub Command0_Click()
MsgBox FiveandFiveA
Dim db As Database
Dim CountRow As Long
Dim cLastRow As Long
Dim i As Double
Dim rstSource As DAO.Recordset
Dim tblGenNum As DAO.Recordset
Dim ID As String
Dim CID As String
ID = FiveandFiveA()

Set db = CurrentDb
'Set rstSource = ID
Set rstSource = db.OpenRecordset("tblGenNum")

DBEngine(0)(0).Execute "UPDATE tblGenNum SET CID = TemporaryString"

'cLastRow = 100
'For CountRow = 2 To cLastRow
' rstSource.("tblGenNum").Cells(CountRow, 1).Value = i
' i = i + 500000
' Next CountRow
DoCmd.OpenTable ("tblGenNum")

MsgBox "Generate Number is Completed!"

Exit_Gen_Numbers_Click:
Exit Sub

'Err_LoopZip_Click:
' MsgBox Err.Description
' Resume Exit_LoopZip_Click

End Sub
*************
Could you help me on where is wrong?
Your help is greatly appreciated!!

I'm not sure. Usually when there is an error a message box shows with
two options, debug or end. When we click debug, the line where the
error occurs is highlighted. This helps us identify the error. Of
course, The error message also includes a sometimes helpful error
message.

Do you want to update one record or all records.

If all records then I expect all you need is this:

1. Save the FIVEANDFIVEUPPERCASE function in a standard (ordinary
module).

2. Run your code as follows -

Private Sub Command0_Click()

DBEngine(0)(0).Execute "UPDATE tblGenNum SET CID =
FIVEANDFIVEUPPERCASE([SomeDummyField]).
End Sub

SomeDummyField (Dummy is used here in the sense of blank/place holder
not as stupid) should be replaced. While you could use CID, I would
probably choose the name of some other field in the Table, although I
can't think of any good reason for doing so.
If you don't include the Dummy field then all the records will be
updated with the same value which I think, is not what you want.

******
If you want to update just one record then we have to qualify the
UPDATE statement.

as in

DBEngine(0)(0).Execute "UPDATE tblGenNum SET CID =
FIVEANDFIVEUPPERCASE([SomeDummyField])
WHERE ID =" & Me.txtID.Value
assuming that you have text control call txtID bound to a uniquely
valued field ID.

If you are just using this value as a default entry in a form set the
default value of the control to
=FIVEANDFIVEUPPERCASE()

My recollection is that one cannot set the default value of a field in
a Table to a User Defined Function and that this is number 74,536 in
the list of reasons why we should not open Tables directly.

I have written this quickly OTTOMH. There very well may be thoughtical,
logical, syntactical, knowledgical, spellingical, hallucinationical,
senilitical, depravitical or udder errors.

[Lyle Fairfield]

The Previous Functions I posted were slightly biased towards making the
first character alpha rather than numeric.

I recommend you use the following function which I believe is not.

In my previous post I suggested some ways to accomplish what you might
want to do with this function. I think they exhaust my knowledge.
Perhaps, you could look at these, try one or the other and report any
specific problems.

Public Function RandomStringA( _
Optional ByVal Dummy As Variant, _
Optional ByVal TemporaryString As String, _
Optional ByVal LettersRemainingtoGet As Long = 5, _
Optional ByVal NumeralsRemainingtoGet As Long = 5)
Dim AsciiCode As Long
If LettersRemainingtoGet 0 And NumeralsRemainingtoGet 0 Then
If Rnd() 0.5 Then
AsciiCode = 65 Or (Round(Rnd(), 0) * 32) _
+ Fix(Rnd() * 26)
LettersRemainingtoGet = LettersRemainingtoGet - 1
Else
AsciiCode = 48 + Fix(Rnd() * 10)
NumeralsRemainingtoGet = NumeralsRemainingtoGet - 1
End If
Else
If LettersRemainingtoGet 0 Then
AsciiCode = 65 Or (Round(Rnd(), 0) * 32) _
+ Fix(Rnd() * 26)
LettersRemainingtoGet = LettersRemainingtoGet - 1
Else
AsciiCode = 48 + Fix(Rnd() * 10)
NumeralsRemainingtoGet = NumeralsRemainingtoGet - 1
End If
End If
TemporaryString = TemporaryString & Chr$(AsciiCode)
If LettersRemainingtoGet Or NumeralsRemainingtoGet Then
RandomStringA = RandomStringA( _
Dummy, _
TemporaryString, _
LettersRemainingtoGet, _
NumeralsRemainingtoGet)
Else
RandomStringA = TemporaryString
End If
End Function

--
Lyle Fairfield

[Orchid]

Thanks alot for all your help! I greatly appreciated!

Lyle Fairfield wrote:

The Previous Functions I posted were slightly biased towards making the
first character alpha rather than numeric.

I recommend you use the following function which I believe is not.

In my previous post I suggested some ways to accomplish what you might
want to do with this function. I think they exhaust my knowledge.
Perhaps, you could look at these, try one or the other and report any
specific problems.

Public Function RandomStringA( _
Optional ByVal Dummy As Variant, _
Optional ByVal TemporaryString As String, _
Optional ByVal LettersRemainingtoGet As Long = 5, _
Optional ByVal NumeralsRemainingtoGet As Long = 5)
Dim AsciiCode As Long
If LettersRemainingtoGet 0 And NumeralsRemainingtoGet 0 Then
If Rnd() 0.5 Then
AsciiCode = 65 Or (Round(Rnd(), 0) * 32) _
+ Fix(Rnd() * 26)
LettersRemainingtoGet = LettersRemainingtoGet - 1
Else
AsciiCode = 48 + Fix(Rnd() * 10)
NumeralsRemainingtoGet = NumeralsRemainingtoGet - 1
End If
Else
If LettersRemainingtoGet 0 Then
AsciiCode = 65 Or (Round(Rnd(), 0) * 32) _
+ Fix(Rnd() * 26)
LettersRemainingtoGet = LettersRemainingtoGet - 1
Else
AsciiCode = 48 + Fix(Rnd() * 10)
NumeralsRemainingtoGet = NumeralsRemainingtoGet - 1
End If
End If
TemporaryString = TemporaryString & Chr$(AsciiCode)
If LettersRemainingtoGet Or NumeralsRemainingtoGet Then
RandomStringA = RandomStringA( _
Dummy, _
TemporaryString, _
LettersRemainingtoGet, _
NumeralsRemainingtoGet)
Else
RandomStringA = TemporaryString
End If
End Function

--
Lyle Fairfield