INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Returning matching records

Returning matching records

(OP)
Hi I have a small database in my office and there are some mistakes being made in the patient registration table, the girl that works here is putting in duplicate patients in the table. The key field is just an auto number (patient ID) we have a field for the soc number but cannot use that as the key because we do not keep a soc for all patients. What I would like to do is on the after update event on the SSN check and see if that number is in the database and if it is come back with a msgbox, but also if there is no SSN on the click of the enter patient I would like it to check the last name and first name to see if there are any matching records for those and return the results if any. Can anyone help with the best way to accomplish this? Thank you in advance Pam

RE: Returning matching records

Hi Pam,

What have you tried so far?

Cheers

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

RE: Returning matching records

(OP)
I haven’t tried anything yet I was looking for the best way to go about it any ideas

RE: Returning matching records

I'd open a recordset based on the table on click of the button, you could then filter it on the ID (if entered) or you can filter it on the name(s).

If the recordcount after the filter is >=1 then you have a matching record and can throw up the messagebox.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

RE: Returning matching records

Put an index on the Auto number and/or SSN field, allowing no duplicates.
Then catch the error when saving.

RE: Returning matching records

That's a concise way of handling most of the stated problem.  Additionally, though, when you "catch the error", you'll need to implement logic where, if the SSN field is blank, you'll do a search on the first and last name to see if you come up with duplicates.

This of course is imperfect, for it's easy enough to put the same name in differently in two records, thereby effectively creating a duplicate.  A more foolproof solution would be to discipline the underlying process to require a social security number.   Then you'd only have to do what SB recommends.

RE: Returning matching records

(OP)
Thank you for the ideas, I haven't started this yet but have to do it real soon. You guys pointed me in the right direction thanks again.

RE: Returning matching records

<same name differently in two records
Might be clearer to say same person's name in differently in two records.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close