Returning matching records

[PamSt]

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

 

[HarleyQuinn]

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 before posting.

 

[PamSt]

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

 

[HarleyQuinn]

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 before posting.

 

[SBerthold]

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

 

[BobRodes]

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.

 

[PamSt]

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.

 

[BobRodes]

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