Creating Auto Id Format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create an auto id that is similar to this:

B = Random Letter
20051103 = Year, Month, Day
1T4SZ = Random Letters and Numbers

B200511031T4SZ is how it would look.

I would like this to be my unique id in a table when I create a record. But
I am having problems trying to figure out how to create the id when I update
a form with all of my new info.

Can anyone lend me a hand?
 
Hi Tony,

why don't you want to use the built in auto number function in Access?

If you want a reference when this record was created, simply add a column
named CREATED as DATE with default value Now().

When you script a function which generates the custom key you'll have to
declare it as unique primary key in the table. Please remember, it could
happen that random generated values have the same value.
 
The reason that I need this number instead of a Access Based number is we are
trying take over from an old program and we want to keep the same format in
the numbers. There is no way we would have dup's doing it this way because
everything in the number changes on a daily basis. But I did figure it out.

Thanks for the response.

Public Function GenPrimaryValue() As String
Dim strStart As String
Dim strDate As String
Dim strEnd As String
Dim CrntDate As Date
Dim intChar As Integer
Dim upperbound As Long
Dim lowerbound As Long
Dim X As Long
Randomize

CrntDate = Now
upperbound = 65
lowerbound = 89
intChar = CInt((upperbound - lowerbound + 1) * Rnd + lowerbound)

strStart = Chr(intChar)

strDate = DatePart("YYYY", CrntDate)
strDate = strDate & Format(DatePart("m", CrntDate), "00")
strDate = strDate & Format(DatePart("d", CrntDate), "00")

strEnd = ""

For X = 1 To 5
upperbound = 0
lowerbound = 4
intChar = CInt((upperbound - lowerbound + 1) * Rnd + lowerbound)
Select Case intChar
Case 1
upperbound = 65
lowerbound = 89
intChar = CInt((upperbound - lowerbound + 1) * Rnd + lowerbound)
Case Else
upperbound = 51
lowerbound = 48
intChar = CInt((upperbound - lowerbound + 1) * Rnd + lowerbound)
End Select
strEnd = strEnd & Chr(intChar)
Next X

GenPrimaryValue = strStart & strDate & strEnd
PRONUM = GenPrimaryValue

End Function
 
Hi,

sorry for the delay, but i had pre-X-mas-holidays.

To declare a table column as primary key, you simply have to do this in the
edit table mode, e.g. select table, right click, choose edit (or something
similar, sorry I only have a localized version of Access), select the table
column, e.g. RowId, right click on, select Primary Key. The index should show
you "Yes, without duplicates". If you want to add new records via a form, you
will have to add GenPrimaryValue() to the field "RowId" or how do you has
named it, now a RowId for every new record should be generated.

Regards

Oliver
 
Back
Top