newby.. creating membercode

[Swingleft]

Hallo there,

i'm a newby in Access, (i program a lot in Excel VB)

I'm looking for a way to create a unique code based on the

lastname and a following no.

f.i

my lastname is Becker then my code would be

BECK1

(the first 4 letters of my lastname and a following no.)


and if my brother becomes a member to his code would be

BECK2

so with a following no.

and if somebody's last name is shorter than 4 (f.i. : Paul van Es)

the code would be

ES1

thanks for al the help

best regards

Swingleft

 

[John W. Vinson]

Hallo there,

i'm a newby in Access, (i program a lot in Excel VB)

I'm looking for a way to create a unique code based on the

lastname and a following no.

f.i

my lastname is Becker then my code would be

BECK1

(the first 4 letters of my lastname and a following no.)


and if my brother becomes a member to his code would be

BECK2

so with a following no.

and if somebody's last name is shorter than 4 (f.i. : Paul van Es)

the code would be

ES1

thanks for al the help

best regards

Swingleft

Well...

Why?

This information is not particularly useful to your computer. A hidden,
meaningless, numeric Autonumber is faster, trivially simple to implement, and
much more useful.

Neither is it particularly useful to people using the computer. Who is BECK4?
Sidney Beckham, Byron de la Beckwith, Samuel Beckett? No way to know... unless
you have a very simple database form showing the full name; but you can do
that WITHOUT using the composite key.

Seriously, this sounds like a holdover from the days before computer
databases, when you needed a label to put on a hanging file folder. That's
fine, if that's what you need... but is it?

Please explain the real-life purpose of this code.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Swingleft]

Hi John,

There is a good reason for this system, it is used in the golfworld over
here.
app. 370.000 people use it in one way or another.
its an easy way too for everybody and it is printed on their personal card.
They use this code as logincode (together with a password)

So its not for the backend of my database but for a login methode for all
users.

hopefully you can help me with this.

thanks for all help.

best regards

Swingleft



"John W. Vinson" schreef in bericht

Hallo there,

i'm a newby in Access, (i program a lot in Excel VB)

I'm looking for a way to create a unique code based on the

lastname and a following no.

f.i

my lastname is Becker then my code would be

BECK1

(the first 4 letters of my lastname and a following no.)


and if my brother becomes a member to his code would be

BECK2

so with a following no.

and if somebody's last name is shorter than 4 (f.i. : Paul van Es)

the code would be

ES1

thanks for al the help

best regards

Swingleft

Well...

Why?

This information is not particularly useful to your computer. A hidden,
meaningless, numeric Autonumber is faster, trivially simple to implement,
and
much more useful.

Neither is it particularly useful to people using the computer. Who is
BECK4?
Sidney Beckham, Byron de la Beckwith, Samuel Beckett? No way to know...
unless
you have a very simple database form showing the full name; but you can do
that WITHOUT using the composite key.

Seriously, this sounds like a holdover from the days before computer
databases, when you needed a label to put on a hanging file folder. That's
fine, if that's what you need... but is it?

Please explain the real-life purpose of this code.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Bernd Gilles]

Hi,
as you know that your primary keys in the backend should be simple
unique numbers (autoincrement), you can use something like this to get a
'login-id':

Public Function GetCode(Lastname As String, Length As Integer) As String
Dim rs As DAO.Recordset
Dim s As String
s = Left$(Lastname, Length)
Set rs = DBEngine(0)(0).OpenRecordset("SELECT COUNT(*) AS RecCount
FROM YOUR_TABLE_NAME" & _
" WHERE Left$(YOUR_FIELD_NAME,"
& Length & ") = '" & s & "'", dbOpenForwardOnly, dbReadOnly)
GetCode = s & rs!RecCount + 1
rs.Close
Set rs = Nothing
End Function


Am 09.02.2012 17:05, schrieb Swingleft:

 

[Access Developer]

Without more info, it's going to be difficult to help. Obviously, unless
you have access to all 370,000 of those logins, you can't guarantee
uniqueness. But, of course, it's only implied in your post that uniqueness
of the generated login is required; on the other hand, if they are not
unique, it seems a useless exercise...

 

[John W. Vinson]

Hi John,

There is a good reason for this system, it is used in the golfworld over
here.
app. 370.000 people use it in one way or another.
its an easy way too for everybody and it is printed on their personal card.
They use this code as logincode (together with a password)

So its not for the backend of my database but for a login methode for all
users.

Ok... ASSSUMING (and it's a big assumption!) that you only want the code to be
unique for that subset of golfers entered in your database (i.e. that if you
are BECK1 and you aren't concerned about Sid Beckwith in Galveston, TX being
another BECK, then you can do this with some code. It's a LOT easier if you
use two fields - one for the text portion, and a Number... Long Integer field
- let's call it NameSeq - for the number. You can concatenate them for display
purposes with an expression like

UCase(Left([LastName] & " ", 4)) & [NameSeq]

This will show "Beckwith" as "BECK4" and Wu as "WU31", or whatever that
person's sequence number is.

You will also need some (reliable) way of identifying which names are in fact
new to the system. You obviously cannot use just the last name - they're not
unique; and you have no a priori way to know that John Vinson isn't already
one of the dozen VINS in your database (vins are often tres ordinaire <g>).

That said, if you are entering a new golfer into the database (and you know
that they are new!) you could use an expression like

NZ(DMax("[NameSeq]", "[YourTableName]", "LastName LIKE " &
Left(Me!txtLastName, 4) & "*")) + 1

to find the maximum existing value of NameSeq for the LastName in the textbox
txtLastName on your form and add 1 to it; the NZ() will cause names completely
new to the system to get 1 as NameSeq.

There's more to it than this of course (e.g. what do you do when PHIL32, Janet
Phillips, marries and changes her last name to Robertson?) but it should give
you a start.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Phil Hunt]

If you are trying to mimic some old system, don't
I am sure there are sample MDB out there that handle directory or contact
information that will give you a quick start