Check that person being added to form isn't already present 1

[DoctorJDM]

Hi

When adding a person to a contacts form I'm checking for existence of the same name already in the contacts table.

I've added the code below as a BeforeUpdate procedure on the forms's LastName field, where qryCheckName is a simple query taking the form's FirstName and LastName fields as criteria.

If a user says no to adding the person I want the form to be cleared but the code below fails on trying to set me.LastName = Null.

It says the 'macro or function set to the BeforeUpdate or Validation rule property for this field is preventing Access from saving the data in the field'.

There's no validation rule and can't see why it objects to the LastName field being set to null when setting the FirstName to null is ok.

Or maybe there's an easier way of clearing all the fields in the form?

****************


If DCount("*", "qryCheckName") < 1 Then
Exit Sub
End If

Dim stDocName As String
stDocName = "frmCheckName"
DoCmd.OpenForm stDocName

If MsgBox("OK to add new person?", vbYesNo) = vbNo Then
DoCmd.Close acForm, "frmCheckName"
Me.FirstName = Null
Me.LastName = Null
Cancel = True
Else
DoCmd.Close acForm, "frmCheckName"
End If

 

[Remou]

The form before update event would be better for this check. You could use Me.Undo in that event, as far as I recall.

 

[TheAceMan1]

How are yaDoctorJDM . . .

If [blue]qryCheckName[/blue] is indeed returning records where first & last names match that of the current record, then . . .

[blue]    If DCount("*", "qryCheckName") < 1 Then
        Exit Sub
    End If[/blue]

. . . exits the sub if no duplicates are found! Using the greater symbol ([blue]>[/blue]) should fix this.

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[DoctorJDM]

Hi Guys

Always know I'm in safe hands with you two.

The Me.Undo is what I needed (and forgotten), thanks. It seems to me it's better having the BeforeUpdate on the name field rather than the form - otherwise wouldn't the latter fire off when scrolling through records having allowed a duplicate name? Do put me straight if not.

Thanks Ace, I think what I have is right - isn't it, again put me straight if not? If the DCount is < 1 it says there isn't anyone with that name so no need to warn about possible duplicates.

 

[TheAceMan1]

DoctorJDM . . .

At 2nd look, your code is fine . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886