Need- "that SSN already exists"

[Bonnie]

Using AXP. I have a main form with contract info and
subform for participant withdrawal requests. Unique field
on the subform is SSN. I need to have a message pop up on
the SSN BeforeUpdate event to say "that SSN already
exists, are you sure you need an additional record for
that participant".

What is the best way to handle that? Don't want to use
Primary Key (no dupes) as sometimes we do have more than
one withdrawal request for one SSN. (Records are
periodically appended to another table so each year a
person can have another withdrawal.)

Thanks in advance for any help.

 

[Bruce M. Thompson]

Using AXP. I have a main form with contract info and

subform for participant withdrawal requests. Unique field
on the subform is SSN. I need to have a message pop up on
the SSN BeforeUpdate event to say "that SSN already
exists, are you sure you need an additional record for
that participant".

What is the best way to handle that? Don't want to use
Primary Key (no dupes) as sometimes we do have more than
one withdrawal request for one SSN. (Records are
periodically appended to another table so each year a
person can have another withdrawal.)

You can use the DCount() function here to retrieve a count of the number of
records in the table already containing that SSN. It's interesting to note that
you don't want to use a "no duplicates" index on this field, yet you earlier
state that this field is a "unique field" (did you mean something other than
"unique values" here?).

 

[Arvin Meyer]

You may want to find a better "number" for a "unique" number. SSN's are no
longer unique if they ever were. There are at least 3/4 of a million illegal
aliens using unique SSNs which belong to someone else. There are
approximately 10 millions cases of identity theft a year. There are also
millions upon millions of data entry mistakes.

That said use the BeforeUpdate event to do a DLookup or build a recordset to
do it:

Sub SSN_BeforeUpdate(Cancel As Integer)
If (Not IsNull(DLookup("SSN", "tblWhatever", "SSB =Forms!FormName!txtSSN")))
Then
MsgBox ("That SSN is already in the database.")
Cancel = True
End If
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Bonnie]

I used 'unique' only to indicate that is the field I want
to use to identify if there is already a withdrawal
request. (All my tables use an autonumber field as
their "no duplicates" and/or primary key.) I don't want to
utilize the 'no dupes' here because sometimes, though
rarely, there will be a legitimate second withdrawal
request.

Thanks VERY much for the info.

 

[Bonnie]

I used 'unique' only to indicate that is the field I want
to use to identify if there is already a withdrawal
request. All my tables use an autonumber field as
their "no duplicates" and/or primary key. I don't want to
utilize the 'no dupes' here because sometimes, though
rarely, there will be a legitimate second withdrawal
request.

Thanks VERY much for the example, it is EXACTLY what I was
looking for. I am very grateful for these newsgroups.

 

[Bruce M. Thompson]

I used 'unique' only to indicate that is the field I want

to use to identify if there is already a withdrawal
request. (All my tables use an autonumber field as
their "no duplicates" and/or primary key.) I don't want to
utilize the 'no dupes' here because sometimes, though
rarely, there will be a legitimate second withdrawal
request.

Thanks VERY much for the info.

I wasn't suggesting that you enforce "no duplicates" here, although it might
have sounded that way. My suggestion to use DCount() would work much the same as
Arvin's suggested use of DLookup(), so I guess you're all set now.