Suggestions for code for Unique id

[powellm1976]

I'm having trouble deciding on what to do for a unique number for my program. I've tried different combinations with no set one being definitely unique. I have client info (ex: SSN, birth date, gender, etc) Does anyone out there have a formula for generating the number. the number can be any size from say...9 to 15 digits long. Please help on this one, I'm up for any suggestions.

thanks,

Mark

 

[FloBob]

why not autonumber the form?

 

[powellm1976]

I need unique identifier, not autonumber

The problem is that we have a casenumber already which is already in a sence an auto number but I need a unique number that is derived from a combination of particulars about a client. This will allow for added security.

Thanks

Mark

 

[FloBob]

Why not use the case number? Sorry im really not being sarcastic.

 

[Shep]

The format we use for customer's account numbers is the first 3 letters of their last name (or company name) and, for now, 3 numeric digits (beginning with 100, increments of 10).

Smith, John B. would become SMI100
The next Smith would become SMI110
Smith & Wesson would become SMI120, and so on...

I'm using something like this for non-commercial clients:

Dim strLastName As String
Dim strAcctPrefix As String
Dim strAcctSuffix As String
Dim strTemp As String
Dim strAccountID as String

strLastName = Me.txtLastName
strAcctPrefix = StrConv(Left(strLastName, 3), vbUpperCase)
strAcctSuffix = "100"
strTemp = strAcctPrefix & strAcctSuffix

Do Until IsNull(DLookup("AccountID", "qrytblCustomer", "[AccountID]= '" & strTemp & "'"))
    strAcctSuffix = Val(strAcctSuffix) + 10
    strTemp = strAcctPrefix & strAcctSuffix
Loop

strAccountID = strTemp

I use a similar routine for commercial customers. Actually, only the variable names are changed (to reflect the context of the form used for adding commercial clients).

Shep

 

[powellm1976]

The problem with this is that Last Name can change and it has to be unique to the client over the history of their involvement. To me there are a few things that won't change (they could but unlikely) Gender, ssn, and birth date. We have over 50,000 records in some cases so a standard numbering system isn't discrete enough and also not big enough in some cases. The reason why we don't make up some formula from those three is that what if they don't have a social. We are looking for someone that has a creative id that is secure. If you're out there please help

mark

 

[powellm1976]

We can't just use casenumber because one client can have multiple cases pending and we want his unique identifier to be the same regardless.

 

[Stew]

Your unique id for the client should not be based on pieces of information you get about the client. It will be used behind the scenes to relate all the information in the tables about the client. No one needs to ever see it (you don't need to see it on the forms). You can use autonumber to accomplish this or better yet, pick a number or a combination of a letter and numbers (c1000000 as a starting point for instance) and increment the number yourself using code whenever a new client is added. If you password protect the database so users cannot view the table information directly, no one will ever guess it.

Just my two cents worth. Hope all goes well.

Paul

 

[Shep]

It's just an example that you might get ideas from...

You don't have a field (or fields) that will never change (or you're not willing to bet that they won't), yet you want a unique id based on client particulars.

If those particulars aren't in fact very particular, then your boat is sunk before you've even floated it.

You MUST have unique data which is populated in every case.

If a client's name might change, then a properly designed relationship will take care of updating related tables/fields.

I assume your main client storage table uses an autonumber as the unique key for the record, and that you simply want to create an additional (viewable) account number of some sort.

If the above is not true then I don't understand what you're trying to do.

If it is true, then you don't actually have a problem.

Shep