Checking for Duplicates Records...

[donbettis]

I am not sure if this is a Query or Form Question...Maybe both...

Here’s my problem…

I have a SubForm based on a query. The query is designed so that only the current users records are shown. On the SubForm I have an AfterUpdate Event on the PhoneNumber field, to help Prevent one Salesperson from entering another Salespersons Contact. It all works great with one exception. Even though all of the records are stored in the “tblcontacts” table. The AfterUpdate Event below is only looking back at the current users records. Any idea as to how I can make it look at all the records?

Private Sub PhoneNumbers_AfterUpdate()
Dim Answer As Variant
If DCount("[LastName]", "tblcontacts", "[LastName]=[Forms]![Master]![frm-QryBySalesperson].[Form]![LastName]") _
And DCount("[FirstName]", "tblcontacts", "[FirstName]=[Forms]![Master]![frm-QryBySalesperson].[Form]![FirstName]") _
And DCount("[MI]", "tblcontacts", "[MI]=[Forms]![Master]![frm-QryBySalesperson].[Form]![MI]") _
And DCount("[DOB]", "tblcontacts", "[DOB]=[Forms]![Master]![frm-QryBySalesperson].[Form]![DOB]") _
And DCount("[PhoneNumbers]", "tblcontacts", "[PhoneNumbers]=[Forms]![Master]![frm-QryBySalesperson].[Form]![PhoneNumbers]") Then
Answer = MsgBox("This Customer is already in the Database", vbOKOnly, "Hey You!")
If Answer = vbOK Then Cancel = True
DoCmd.SetWarnings Off
DoCmd.GoToRecord , , acNewRec
Me.Title.SetFocus
Exit Sub
End If
End Sub


Many Thanks,

Don

[This message has been edited by donbettis (edited 11-15-2001).]

 

[donbettis]

I believe the problem to be in the fact that the query is filtering the table. I haven't figured out how to make it work this way...However I did find a work around...

I created one form based on the table itself...This is the Form that Sales will use to enter New Contacts...On this form I have taken' away the nav buttons and such.

The Second Form is based on the Query...This Form allows each Salesperson to see only the Contacts belonging to them...

YeeHaw

 

[neideb]

Dear Don, tks for your suggestion but I may be doing something wrong because I was not succeed in this procedure.
If you do not mind pls check the following code for me. I will be very thankful.
I need that this code checks if I already have the name and birthday and if it is OK I have to select the Yes button and set focus to Address combobox. Is No just close the module. But I do not want to save the record.The code is not checking is I already have the name and birthdate and is saving the record.
Pla help!!!


Private Sub DateOfBirth_AfterUpdate()

Dim Answer As Variant
If DCount("[FirstName]", "TbClient", "[FirstName] = [Forms]![myformname]![FirstName]") And DCount("[MiddleName]", "TbClient", "[MiddleName] = [Forms]![myformname]![MiddleName]") And DCount("[LastName]", "TbClient", "[LastName] = [Forms]![myformname]![LastName]") And DCount("[DateOfBirth]", "TbClient", "[DateOfBirth] = [Forms]![myformname]![DateOfBirth]") Then
Answer = MsgBox("This name already exists. Pls check address.", vbOKOnly, "Attention!")
If Answer = vbOK Then
Cancel = True
Me.ComboAddress.SetFocus
End If
End If
End Sub