How To Detect Multiple Record Return

[charles.kendricks]

I am a relative beginner with MS Access and Database Administration,
but I am trying to develop a database designed to track patient
medications. I use the last four digits of a patients Social Security
Number as a means of looking up each patients records (because it's
easy for the users of the database). What I want is for the database
to have the capability to notify the user when a parameter query using
the last four digits of the SSN returns multiple records, but I don't
know how to accomplish this.

 

[Allen Browne]

Could you just filter the form to matching patients?
The user can then see if there are multiple returns.

If you want to show the number of matches in a text box, it would be
something like this:
=DCount("*", "Patients", "SSN Like ""*" & [txtSSN] & """")
where txtSSN represents the name of the text box that contains the trailing
characters of the SSN field.

 

[charles.kendricks]

The form is used to look up the client information assuming that all
that is known is the last four digits of the SSN. I knew I needed to
produce a count of the returned records as you mentioned, but how can I
then trigger an alert or error message if the number is greater than 1,
and do so from the original query?

 

[Guest]

If you set your last-four-digits field as the primary key, Access will
enforce uniqueness automatically, but I assume you already knew that. It
sounds like you don't have any primary key set, which would mean your data
entry folks would have to check several fields carefully before entering or
editing data. I strongly recommend idiot-proofing. No technician or
supervisor will ever be insulted by it; they only resent inconvenient
security measures.

To answer your question, a parameter query that prompts the user for the
last four digits would automatically return all records with those digits,
whether that be zero, one, or three records.

If you need to generate the error message from the form at the time of
input, rather than through a query, setting a primary key would do that
easily. I'm a relative beginner like yourself, but I think the only way to
generate that message at input without enforcing uniqueness would be to add a
subroutine to the form's Visual Basic code. That is opening a big can of
worms, but one or more of these professionals can help you do that, if you
choose.

Good luck!

 

[Allen Browne]

I don't understand the question.

You are using a form to lookup and display the record(s) returned by the SQL
statement or query. At what point to you change the query or apply the
filter so it matches only the SSN? What event do you use to do this?

Whatever event you use, it would make sense to use the same event to count
the results.

 

[charles.kendricks]

OK I have a form which is used to return the a form containing a single
client's client record, which is another form with his personal and
other information. I use the clients last 4 (of his SSN) to indicate
which client's records I'm requesting (The clients entire SSN is used
in the client table to uniquely identify each client). The medication
form is a subform of this client form. All of this works just fine.
What I am trying to do is to guard against the eventuality when two
clients may have the same last 4. I want the database to give me a
pop-up window alerting the user that the query has returned more than
one client. It would be even nicer if the pop-up allowed the user to
see which client names were returned and allow him to choose which one
he wanted.

 

[Allen Browne]

So what event causes this form to open the other form?
Is it the Click event procedure of a command button?
Is it the DblClick of a text box?

Whatever event you use to open the other form, can perform the check to see
if there are multiple matches.