Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Nov 2002
    Location
    Toronto, Ontario, Canada
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    building client code (Access 2000)

    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

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: building client code (Access 2000)

    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.

  3. #3
    New Lounger
    Join Date
    Nov 2002
    Location
    Toronto, Ontario, Canada
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: building client code (Access 2000)

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

  4. #4
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: building client code (Access 2000)

    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
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  5. #5
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: building client code (Access 2000)

    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
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  6. #6
    New Lounger
    Join Date
    Nov 2002
    Location
    Toronto, Ontario, Canada
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: building client code (Access 2000)

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

  7. #7
    New Lounger
    Join Date
    Nov 2002
    Location
    Toronto, Ontario, Canada
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: building client code (Access 2000)

    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.

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: building client code (Access 2000)

    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.

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: building client code (Access 2000)

    It depends on *how* they're supposed to be related as family members and how exactly you define a "family".
    Charlotte

  10. #10
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: building client code (Access 2000)

    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.

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,497
    Thanks
    3
    Thanked 42 Times in 42 Posts

    Re: building client code (Access 2000)

    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.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •