Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    36

    Unanswered: Duplicate Search Query

    I am having some problems with a customer database we use that records basic details such as name, address, contract number etc.

    When users input new details to the Form, rather than search records on one of the fields to see if we already hold existing data, the users are creating a new record (despite being warned many times that they need to check first)

    Is there anyway that when the users first open the form a pop up could appear with a couple of the fields (name & postcode) so that they could do a quick search for existing details and if none found then they could start a new record.

    Many Thanks

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    yup.

    there's a ton of ways to do that. Could you be more specific about what you have in mind and what you've tried?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Mar 2004
    Posts
    36
    Teddy - Thanks for the reply, I'll be more specific -

    We have a 'Customer Details' form and on which we record basic details i.e. Name, Address, Tel number etc.

    The problem we are experiencing is that customers are being contacted a number of times because users are too lazy to search the database and are creating new records every time contact is made with a customer, rather than search if any existing details are held. (Each time a new record is created a telephone call is made and letter sent).

    Users have been warned countless times but we have been fruitless in success, what I have found for example is that if they are speaking to Mr Jones, they will search the name Jones find 213 records and create a new records rather than find the one applicable to him. (I know they could then again do a further search but this does not happen)

    Ideally what I would like is that when a user clicks on the 'Create New Record' command button which takes them to a new record. A 'Pop Up' will appear with two field’s i.e. Customer name and postcode (These fields are ones used on the form).

    The user can then enter these to quickly view any records that match the 2 fields.

    Long winded I know – I’d be grateful for any help you can give me

    Kind regards

    Matthew

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    yeah that's not too tricky.

    The simplest method would be to populate a two column combo box with name and zip code.

    By default, typing into a no-entry combo box will automatically filter records.

    If you were to use this combo box as the criteria for your form, then you'd have an automatically scrolling form. If the customer doesn't exist in this combo box, THEN they could create a new user.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011

    Talking

    On critical fields, such as Phone Number, place an after update event that queries the table to see if it already exists. If it does, take some action (such as canceling the new record and changing the focus to the matching record or at least notifying the user).

    If you need help with this, reply back.

    Also, I've found a good way to pursuade users to do their job properly is to catch them red handed NOT doing it properly. Add 2 fields to your customer table: UserAdded and UserDate. Set the default for the UserDate field to =Now() and on your form to add new customers, insert the userID of the person adding the record (make this field hidden).

    This way, you can create a query that shows duplicates, sort by UserDate ascending and show the Last of UserAdded and you'll have a list of who didn't check first. Everybody makes an occasional mistake, but if somebody keeps coming up again and again, they're simply not doing their job properly.

    Good luck and remember: Big Brother is always watching, and it's nice to be in the Big Brother seat from time to time. Nothing was more powerful in preventing the manufactuing crew from making duplicate requests for materials after I presented them with a detailed report of who did and how many times they did over the course of a week.
    Last edited by tcace; 06-25-04 at 21:00.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by tcace
    Also, I've found a good way to pursuade users to do their job properly is to catch them red handed NOT doing it properly. Add 2 fields to your customer table: UserAdded and UserDate. Set the default for the UserDate field to =Now() and on your form to add new customers, insert the userID of the person adding the record (make this field hidden).
    This needs to be in a FAQ.

    Nothing promotes "proper" application usage like accountability.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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