Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

DoctorJDM

Technical User
Apr 15, 2007
60
GB
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
 
The form before update event would be better for this check. You could use Me.Undo in that event, as far as I recall.
 
How are yaDoctorJDM . . .

If [blue]qryCheckName[/blue] is indeed returning records where first & last names match that of the current record, then . . .
Code:
[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.

Calvin.gif
See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886
 
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.

 
DoctorJDM . . .

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

Calvin.gif
See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top