warn about, but allow, duplicate entries

[Guest]

Hi,
I have a form where a lot of different cases are entered. It is possible for
there to be multiple cases with the same person's name on them, but i still
want to avoid having doubles.
Is it possible to warn or prompt about a duplicate entry, but still allow it
if it is correct?
Note: The cases already have a case ID that is the primary key, this does
not allow duplicates, so the field I am wondering about is a separate field
that lists the person's name attached to the case. I'd rather not have to
change their name to avoid duplicates.
Thanks for the help

 

[Smartin]

Hi,
I have a form where a lot of different cases are entered. It is possible for
there to be multiple cases with the same person's name on them, but i still
want to avoid having doubles.
Is it possible to warn or prompt about a duplicate entry, but still allow it
if it is correct?
Note: The cases already have a case ID that is the primary key, this does
not allow duplicates, so the field I am wondering about is a separate field
that lists the person's name attached to the case. I'd rather not have to
change their name to avoid duplicates.
Thanks for the help

Knee-jerk reaction:

What is the business rule? It either is, or is not acceptable to have
such combinations duplicated.

Is it possible at this point to reorganize the table design?

You could query the table on the BeforeUpdate event to see if a
duplicate will be created and take intervention, if that's what you
need, but I'm guessing there is a flaw in the structure that makes this
more cumbersome than it needs to be.

 

[Guest]

Duplicate entries are allowed,
See, sometimes the same client will have 2 or more cases that are different.
That is fine, and we want those to be separate. However, this allows the
possibility that a case will be entered twice, when it really shouldnt be. I
simply want a prompt to come up that tells the user that there is an entry
with the name already, and if they want to continue anyway, they may do so,
but if they don't want to add a new entry, they will know that they shouldnt.
Does that make sense?

 

[Smartin]

Duplicate entries are allowed,
See, sometimes the same client will have 2 or more cases that are different.
That is fine, and we want those to be separate. However, this allows the
possibility that a case will be entered twice, when it really shouldnt be. I
simply want a prompt to come up that tells the user that there is an entry
with the name already, and if they want to continue anyway, they may do so,
but if they don't want to add a new entry, they will know that they shouldnt.
Does that make sense?

Well, no. Now you're talking about different "cases", and yet earlier
you inferred cases have a unique ID. Is this really the case?

I'm not trying to be argumentative, just trying to understand the
scenario you are trying to model.

I fear you may back yourself into a corner allowing these duplicates
without a proper way to distinguish them.

 

[Guest]

I am sorry I have not been clear,
Each case has a unique ID, This is a number. Each case also has a clients
name attached to it, this may not be unique. It is possible for a client to
have more than one case, say they were here a year ago with a completely
different issue. This is fine, and should be allowed. However, due to our
overwhelming case load and understaffed office, I am worried that some cases
may be accidently entered into the system twice, with two different unique
IDs, and thereby confuse our summary data. So what I want is, during data
entry in a form, to be able to check whether or not there is an existing case
with the same client name attached. If there is, I would like to alert the
user, BUT, if the user still wants to enter the new case, I want this to be
allowed. I am getting the sense that this may not be possible, but if so, I
would really appreciate the help,
Thank you

 

[Smartin]

I am sorry I have not been clear,
Each case has a unique ID, This is a number. Each case also has a clients
name attached to it, this may not be unique. It is possible for a client to
have more than one case, say they were here a year ago with a completely
different issue. This is fine, and should be allowed. However, due to our
overwhelming case load and understaffed office, I am worried that some cases
may be accidently entered into the system twice, with two different unique
IDs, and thereby confuse our summary data. So what I want is, during data
entry in a form, to be able to check whether or not there is an existing case
with the same client name attached. If there is, I would like to alert the
user, BUT, if the user still wants to enter the new case, I want this to be
allowed. I am getting the sense that this may not be possible, but if so, I
would really appreciate the help,
Thank you

Are using an autonumber to identify cases? This would be a stumbling
block here, because you can't look at multiple records easily and say
they belong to the same case.

If you are using some other element to identify a case, e.g., some
numbering system that makes sense in your operation, this will be a bit
easier, and the rest of my suggestions below can build from this.

Your business rule is to encourage updating an existing case, but allow
multiple entries when needed.

Depending on how your office works, something like this might do it: On
case entry, request the case number from the user. Check to see if any
records exist that match. If yes, prompt the user and give them the
opportunity to update the existing case. If no, take them straight to
the "new case" data entry form.

Next, to handle the piece about having more than record per case, maybe
you need a second identifier, a "sub-case" or something. This would have
to be incorporated in your forms and reports to give you the summary
information you need at the appropriate level.

Hope this helps!