Duplicates phone numbers and extensions 2

[lmcc007]

I have one table called tblCompany, which has four fields for telephone
numbers. The fields are: Phone, Phone2, Phone3, and Fax. And, four more
fields, which are: Ext (for extension), Ext2, Ext3, FaxExt. I want the code
to check for duplicates when entering or revising telephone numbers. I
entered the code below but it is not doing anything:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If DCount("[Phone]" And "[Ext]", "tblCompany", _
"[Phone2] = '" & Me.txtPhone2 & " And " & _
"[Ext2] = '" & Me.txtExt2 & " And " & _
"[Phone3] ='" & Me.txtPhone3 & " And " & _
"[Ext3] = '" & Me.txtExt3 & " And " & _
"[Fax] = '" & Me.txtFax & " And " & _
"[FaxExt] = '" & Me.txtFaxExt & " And " & _
"[CompanyID] <> " & Me.txtCompanyID) > 0 Then
MsgBox "Telephone number is already used.", , "Telephone number
search."
Cancel = True
End If
Exit Sub

End Sub

Am I missing the point? Should I be doing something different to check for
duplicate telephone numbers?

Help, please!

 

[MajP]

Not sure what to say. Because of this design you are asking for a somewhat complicated solution. For each phone number and extension combination you have to search the database for an existing phone number and extension combination existing in four different areas composed of two fields each (number and extension). Not really trivial. Compounding that you only know how to write very basic vba code. Sorry, but I think you are over your head. You say "They do not like subforms, and they want to see it exactly like this." I assume you are not a pro, so why don't "they" let you build it within your means or find someone who can?

Here is a working example. In the frm type in a phone number and extension that is a duplicate to any other phone ext combination. Click out of the field to trigger the before update.

http://www.4shared.com/document/OkuDLMCL/CheckDups.html

 

[lmcc007]

You know I don't understand why all the examples are like this--that is:

BusinessPhone (123) 123-4567
Car ( )
Home ( )
Fax ( )

Each class I have taken do this. Each book do this. Why?

Access 2007 Inside Out, Access 2007 Bible, and MS templates are all set up this way. I just went back over my practice files I followed in school and all of them are set up this way.

 

[lmcc007]

I assume you are not a pro, so why don't "they" let you build it within your means or find someone who can?"

No, I am not a pro. After all that is why I am here. The only way to become a pro is to try and do it.

I have read many many books and taken up courses, but like always the teaching are generic or too basic--not real world problems.

They hire IT professionals all the time for bigger projects.

This is a very simple database that the secretary created from MS contact management template. It works for them except this duplicate phone number problem.

I thought I could help as well as learn something new, but unfortunately that is not the case.

Anyway, thanks for your time.

 

[MajP]

Take a look at the demo. It is a working example using your table names and field names. It demonstrates how to call the functions using the proper amount of arguments. The only thing that may need to be changed are the data types of the fields (I used strings). Try to understand the code and if you have specific questions, I will be glad to answer. Good luck.

 

[lmcc007]

I have looked at the demo as well as used it. The demo is not working properly. I entered the same number four times per company, and did not receive a warning. Then I entered a number in Co1, went to Co2 and entered the same number that I entered in Col, received an error message.

 

[MajP]

This went back to my original question.

My guess is when you enter a number you want to check that other companies do not have the same number. I would think that you would want to check that you are not using the same number within the company. If that is the case just change the <> to =.

So in the code change the <> to =
"[txtCompanyID] <> " & ID
to
"[txtCompanyID] = " & ID

 

[lmcc007]

Did it and it still doing the same thing. Here's the code:

strWhere = "[txtCompanyID] = '" & ID & "' AND Trim([phone] & [ext])='" & strNumAndExt & "'

 

[MajP]

Try this version, to check for duplicates within a company (not duplicates between companies).

http://www.4shared.com/document/MzlBJk7K/CheckDups2.html

Post any error messages.

 

[lmcc007]

Just tried it.

Phone2 is not comparing against Phone.

If you have no number in Phone and/or Phone2 it does not show duplicates even though Phone3 and Fax are duplicates phone numbers.

If you decide you want to delete the phone number in Phone, it will not let you and so on.

 

[MajP]

http://www.4shared.com/document/IWvV5iqk/CheckDups3.html

Try this. There is now way to enter a duplicate using the form. If there is capture the steps, because I can not do it.

However, there will be an issue removing a duplicate if it is in the database by another means.

 

[lmcc007]

Company C, phone and phone 2 allow duplicates.

 

[PHV]

Typed, untested:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim a, i As Long, j As Long
a = Array( _
  Trim(Me!Phone & "") & Trim(Me!Ext & ""), _
  Trim(Me!Phone2 & "") & Trim(Me!Ext2 & ""), _
  Trim(Me!Phone3 & "") & Trim(Me!Ext3 & ""), _
  Trim(Me!Fax & "") & Trim(Me!FaxExt & ""))
For i = 0 To 2
  If a(i) <> "" Then
    For j = i + 1 To 3
      If a(i) = a(j) Then
        Cancel = True
        MsgBox "Duplicate PhoneExt: " & a(i)
        Exit Sub
      End If
    Next j
  End If
Next i
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

Yeah, I agree with PHV. Using a dlookup or dcount works good if you are checking one field against and existing record, but this strategy does not work well checking multiple fields because you have to check against comitted records and the new record within itself. This is a better strategy.

 

[MajP]

This is a little modification from PHVs solution to allow for a tailored message. The basic logic is the same without using arrays.

Private Sub form_beforeUpdate(Cancel As Integer)
  If isDuplicate <> "" Then
    MsgBox isDuplicate
    DoCmd.CancelEvent
    Me.Undo
  End If
End Sub
Public Function isDuplicate() As String
  Dim strPhone As String
  Dim strPhone2 As String
  Dim strPhone3 As String
  Dim strFax As String
  Dim msg As String
  
  strPhone = Trim(Me.phone & " ") & Trim(Me.ext)
  strPhone2 = Trim(Me.phone2 & " ") & Trim(Me.ext2)
  strPhone3 = Trim(Me.phone3 & " ") & Trim(Me.ext3)
  strFax = Trim(Me.fax & " ") & Trim(Me.faxext)
  
  If strPhone = strPhone2 And strPhone <> "" Then
    msg = "Duplicate in Phone and Phone2"
  ElseIf strPhone = strPhone3 And strPhone <> "" Then
    msg = "Duplicate in Phone and Phone3"
  ElseIf strPhone = strFax And strPhone <> "" Then
    msg = "Duplicate in Phone and Fax"
  ElseIf strPhone2 = strPhone3 And strPhone2 <> "" Then
    msg = "Duplicate in Phone2 and Phone3"
  ElseIf strPhone2 = strFax And strPhone2 <> "" Then
    msg = "Duplicate in Phone2 and Fax"
  ElseIf strPhone3 = strFax And strPhone3 <> "" Then
    msg = "Duplicate in Phone3 and Fax"
  End If
  isDuplicate = msg
End Function

 

[lmcc007]

Thanks for your time PHV.

 

[lmcc007]

MajP,

The last code is great! Thanks for hanging in there! I knew it had to be something easier to do. I can even follow the code now. Been using it and it works!

Thank you, thank you!