Unique ID's

[Guest]

I would like to create Unique ID's using some of the information in other
fields. Such as an ID for John Smith that would be alpha numeric. I would
like to be able too automatically assign the ID when I import the data into a
table.

For example, SmithJ00001 already exists in the table I will be importing the
data. For the next John Smith, I would like it to assign and ID SmithJ00002,
etc.

What is the formula to get this done?

 

[Douglas J. Steele]

Why? "Smart keys" such as what you're describing are generally not advised.
What happens when Jane Smith marries Mike Brown and changes her name to Jane
Brown? Would you change her ID?

 

[J]

You should really use autonumber for IDs. Unless you're creating
employee numbers to use, you should work around this some other way.

That said, here's how to do it anyways!



Private Sub Somethingsomething()

'If getting from a table
LastName = Dlookup("[LastName]","TableEmployees","[ID]=" &
SomeIDNumber)
FirstName = Dlookup("[FirstName]","TableEmployees","[ID]=" &
SomeIDNumber)

'If getting from controls on a form
LastName = txtLastName
FirstName = txtFirstName

GiveMeNewFinalID(LastName, FirstName)

End Sub


Public Function GiveMeNewFinalID(LastName as string, FirstName as
string) as String
Dim CombinedNames as string 'Just never use "Name" for any name
Dim Numb as Long 'Number is probably a reserved field

Numb = 0
CombinedNames = LastName & Left(FirstName,1)

Do
Number = Numb + 1
GiveMeNewFinalID = CombinedNames & Format(Numb,"00000")
Loop Until Dlookup("[ExistingID]","TableExistingIDs","[ExistingID]='" &
GiveMeNewFinalID & "'") = 0

End Function

Enjoy,
~J

 

[Guest]

I understand your point and maybe my example wasn't the best. But in keeping
with my example, yes we would want a new ID if a name was changed.

 

[Douglas J. Steele]

Then you really should have that as the Primary Key: Primary Keys should
never change.

That doesn't mean you can't store that value: just use a more appropriate
field (such as an AutoNumber) as the Primary Key.

If you know the name part, your table is named MyTable and the field in
question is named SmartKey, you can find out the highest value for that name
using something like:

Dim strCurrName As String
Dim strLastUsed As String
Dim strNextToUse As String

strCurrName = "SmithJ"
strLastUsed = Nz(DMax("SmartKey", "MyTable", _
"SmartKey Like '" & strCurrName & "*'"), "")
If Len(strLastUsed) = 0 Then
strNextToUse = strCurrName & "00001"
Else
strNextToUse = strCurrName & _
Format(CLng(Mid(strLastUsed, Len(strLastUsed) + 1)) _
+ 1, "00000")
End If

 

[Douglas J. Steele]

Sorry: I meant to say "Then you really shouldn't have that as the Primary
Key"!