building client code (Access 2000)

Topic

New Reply

Hi
I am making a client code from the first letter of first name and first letter of last name and the total birth date. I am a little rusty at the moment. The client code needs to be generated when the new client is being entered or after it is updated. would i do it with a query . Any help would be much appreciated.

Gail

Reply | Quote

Replies

If you really want to store it, then in the afterupdate event of all three fields (first name, last name, birth date), do something similar to
me.clientcode=left(me.firstname,1) & left(me.lastname,1) & me.dob

However, since you can always do that anywhere you need it, you don’t need to store it and probably shouldn’t. Just put something similar to
=left(me.firstname,1) & left(me.lastname,1) & me.dob
in the control source of your client code field in your forms and reports.

Reply | Quote

I do need to save it cause the client code will be used to search

Reply | Quote

I hope you realise that the code will not necessarily be unique.
I had a pair of twins in my class a couple of years ago with the same initials which would cause your system a problem.
You certainly shouldn’t use this as a Primary Key field IMHO

Reply | Quote

I would exercise some caution in using the birth date as a part of a search algorithm. The reason for my caution is the furor over identity theft in the US – given that and the SSN you can go quite a ways in getting credit and in the end ruining a persons’ credit report. I expect to see legislation soon that will attempt to curb the usage of such data, or at least require it to be encrypted. Is there some reason why name and address or telephone can’t be used to identify a person? As an example, if you are tracking all of the residents of a major city, or an entire nation, it could be complicated. On the other hand we track some 100,000 persons in a database, and only have one or two cases where two people with the same name live in the same town. Hope this doesn’t muddle things for you.

Reply | Quote

Actually,

I’d put it in the BeforeUpdate event of the form and not in each control.

That way it will be generated when you go to save the record. That is, assuming the form is bound to a datasource

Reply | Quote

The reason for putting it in the afterupdate events of all the relevant controls is so you can actually see the changes to the code as you enter the data.

Reply | Quote

I told the client that but i am using an auto number Id field as the primary key

Reply | Quote

also another problem I am having is there are clients in the database that have to be related as family members . do you have any suggestions on how I would go about that.

Reply | Quote

It depends on *how* they’re supposed to be related as family members and how exactly you define a “family”.

Reply | Quote

The simple way is to have a table that has 4 fields. one for each person’s AutoID, one for the type of relationship and one for a PK. Then to query it you link the main table to this table twice and you can get the info out.

Reply | Quote