checking for redundant entries

[John Milbury-Steen]

Hi Access gurus,

My database in Access 2002 is working just fine, thanks, except the
people who use it sometimes make redundant entries into an address list. (I
work for a university department and the address list is a list of all
prospective students session by session.) The problem is that prospective
students might apply more than once. The data entry people are supposed to
check whether a student has applied before, but sometimes they don't, so we
end up with two people named John Smith with the same social security
number.
How do I check that the new entry is really unique? That is, how do I
refuse the new record when it has a SS number matching an old one already in
the list? And, by the way, I don't do Visual Basic, I just do macros.

 

[Allan Bach]

You could index the field and do not allow duplicates. However, your user will not be warned until they try to save the record. If you did VBA, I could send you a procedure that checks the field immediately after is is updated and warns the user at that time.

 

[John Vinson]

How do I check that the new entry is really unique? That is, how do I
refuse the new record when it has a SS number matching an old one already in
the list? And, by the way, I don't do Visual Basic, I just do macros.

I'd use VB - it's really easy. Let's say you have a table named
Students with a text field SS, and on your form you have a textbox
named txtSS. Open the form in design view and view the Properties of
this textbox; on the Events tab click the ... icon by the BeforeUpdate
event. Invoke the Code Builder and Access will put you into the VBA
editor with the Sub and End Sub lines already there; edit it to

Private Sub txtSS_BeforeUpdate(Cancel as Integer)
Dim iAns As Integer
If Not IsNull(DLookUp("[SS]", "[Students]", _
"[SS] = '" & Me!txtSS & "'")) Then
iAns = MsgBox("This student has already applied" _
& vbCrLf & "Cancel this addition?", vbYesNo)
If iAns = vbYes Then
Me.Undo ' erase the entire form
Else
Cancel = True ' just let the user retype the SS
End If
End If
End Sub