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]

If your capturing multiple phone numbers for a contact then I would normalize the data in its own table. Something like

tblContactNumbers
contactID_fk (foreign key to contact table)
numberType (fax, home,work, cell,etc)
contactNumber (phone number)
contactExtension (extension)

make an index out of contactNumber and contactExtension and you can enforce unique numbers.

This allows you to add as many possible numbers for a contact.

 

[lmcc007]

I know about creating a different table and so on. And this is the way I did the other databases.

This is a simple db. They want there design to stay as is--do not like subforms.

 

[MajP]

I have no idea what your code is supposed to do, it is too jacked up to make sense of.

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 =.

Public Function isDuplicate(ID As Long, strNumber As String, Optional strExt = "") As Boolean
  Dim strNumAndExt As String
  Dim phone1 As String
  Dim phone2 As String
  Dim phone3 As String
  Dim fax As String
  Dim strSql As String
  Dim rs As DAO.Recordset
  strNumAndExt = Trim(strNumber & strExt)
  Set rs = CurrentDb.OpenRecordset("tblCompany")
  strSql = "SELECT txtCompanyID, Trim([phone] & [ext]) AS Phone1_ext, Trim([phone2] & [ext2]) AS Phone2_ext, Trim([phone3] & [ext3]) AS Phone3_ext, Trim([fax] & [faxext]) AS fax_ext"
  strSql = strSql & " FROM tblCompany"
  strSql = strSql & " WHERE (((txtCompanyID)<>" & ID & ") AND ((Trim([phone] & [ext]))='" & strNumAndExt & "')) OR (((txtCompanyID)<>1) AND ((Trim([phone2] & [ext2]))='" & strNumAndExt & "')) OR (((txtCompanyID)<>1) AND ((Trim([phone3] & [ext3]))='" & strNumAndExt & "')) OR (((txtCompanyID)<>1) AND ((Trim([fax] & [faxext]))='" & strNumAndExt & "'))"

  Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
  If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst
    rs.MoveLast
  End If
  isDuplicate = (rs.RecordCount > 0)
 End Function

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

Yes, design your database properly, but you seem to know better.

 

[MajP]

you can delete the following:
Dim phone1 As String
Dim phone2 As String
Dim phone3 As String
Dim fax As String
that is leftover.

 

[lmcc007]

MajP,

You are correct, I do want to check that we are not using the same number within the company.

 

[lmcc007]

MajP,

Thanks for the code.

I am still learning VBA, so I am not quite understanding the code.

I am trying to use the isDuplicate() function, but get the following error:

The expression you entered has a function containing the wrong numbers of arguments.

Don't know what that means.

 

[lmcc007]

MajP,

In a book called Fixing Access Annoyances give a similar example. It states:

"The following example checks for duplicate names in a contact table, issues a warning, and allows an override in case two different people have the same
name...."

Private Sub Form_BeforeUpdate(Cancel As Integer)

If DCount("*", "tblContacts", "lastName = """ & Me!txtLastName _
& """ And firstName = """ & _
Me!txtFirstName & """") > 0 Then
If MsgBox("There is already a user by that name in the" _
& "table. Do you wisd to add it anyway?", _
vbYesNo) = vbNo Then
Cancel = True
End If
End If

End Sub

The above code I am using for the FirstName and LastName fields and it works.

I thought I could do the same for the phone fields, but it is checking all the records instead of Company by Company and so on.

 

[lmcc007]

MajP,

I am now reading up on Trim. There are no spaces or hyphens in the phone number field--just the 10 digit number.

Okay, maybe I following you.

Are you triming the separate fields into one--that is, jointing the phone number field with extension and then comparing?

 

[MajP]

my assumption is that a person enters
number:555-555-1234
ext: 4567
and you want to check that the number and the extension together do not exist. So 555-555-1234 can exist as long as it has as different extension and 4567 can exist as long as it has a different number.

I just concatenated the number and get
555-555-12344567 then I check the database for each other number concatenated.

I could have done it the other way without the concatenation by checking that the phone number = the entered phone number and the extension = the entered extension.

Again the example you give works because they have normalized data. You have added far more difficulty by having non-normalized data with similar data in different fields. Then there is another layer of difficulty (if my assumption is correct) and that numbers with different extensions are unique numbers. Are these assumptions correct?

 

[lmcc007]

Yes MajP all your assumptions are correct.

I prefer having a tblTelephone table, but the problem is many users create databases using templates. In MS contact templates they're telephone number fields are set up this way.

I tried many ways to get the subform to look exactly the way they wanted, but was unsuccessful. E.g.,

Phone (123) 123-4567 ext.
Phone2 ( )
Phone3 ( )
Phone4 (123) 123-4567 ext. 1022

The user form must look like the above, whether there is a number or not. Therefore, I am trying this code and if I can't get to work, will just have to tell them they will have to manually check each number as they enter them.

Again MajP, I tried again to use the isDuplicate() function, but get the following error:

The expression you entered has a function containing the wrong numbers of arguments.

What do I need to do?

 

[MajP]

Can you post how you call the function? The function has three arguments
the txtCompanyID
the phone number
the extension

Should be something like

Private Sub Form_BeforeUpdate(Cancel As Integer)
if isDuplicate(me.txtCompanyID,me.txtPhone,me.TxtExt) then
msgbox "..."
...
end if
End Sub

But you could have duplicates in four places. So you would have something like

Private Sub Form_BeforeUpdate(Cancel As Integer)
if isDuplicate(me.txtCompanyID,me.txtPhone,me.TxtExt) then
msgbox "Primary Phone is a duplicate"
...
elseif isDuplicate(me.txtCompanyID,me.txtPhone2,me.TxtExt2) then
msgbox "Phone2 is a duplicate"
elseif isDuplicate(me.txtCompanyID,me.txtPhone3,me.TxtExt3) then
msgbox "Phone3 is a duplicate
..
elseif isDuplicate(me.txtCompanyID,me.txtfax,me.Txtfaxext) then
msgbox "Fax is a duplicate"
..
end if
End Sub

 

[MajP]

here is another version without recordsets. It is a little longer, but maybe easier to understand.

Public Function isDuplicate2(ID As Long, strNumber As String, Optional strExt = "") As Boolean
  Dim strNumAndExt As String
  Dim strWhere As String
  strNumAndExt = Trim(strNumber & strExt)
  
  strWhere = "[txtCompanyID] <> " & ID & " AND Trim([phone] & [ext])='" & strNumAndExt & "'"
  If DCount("txtCompanyID", "tblCompany", strWhere) > 0 Then
    isDuplicate2 = True
    Exit Function
  End If
  
  strWhere = "[txtCompanyID] <> " & ID & " AND Trim([phone2] & [ext2])='" & strNumAndExt & "'"
  If DCount("txtCompanyID", "tblCompany", strWhere) > 0 Then
    isDuplicate2 = True
    Exit Function
  End If
  
  strWhere = "[txtCompanyID] <> " & ID & " AND Trim([phone3] & [ext3])='" & strNumAndExt & "'"
  If DCount("txtCompanyID", "tblCompany", strWhere) > 0 Then
    isDuplicate2 = True
    Exit Function
  End If
  
  strWhere = "[txtCompanyID] <> " & ID & " AND Trim([fax] & [faxExt])='" & strNumAndExt & "'"
  If DCount("txtCompanyID", "tblCompany", strWhere) > 0 Then
    isDuplicate2 = True
    Exit Function
  End If

End Function

 

[lmcc007]

Before Update = isDuplicate()

 

[lmcc007]

Okay, it looks easier to read. I am not quite there yet, but I have to read it over a couple of times. (I am still trying to learn VBA.)

How to I use it?

When put Before Update = isDuplicate(), I get an error. That is:

The expression you entered has a function containing the wrong numbers of arguments.

Thanks!

 

[MajP]

Yes you will get an error. You have to pass in three things
the current ID
the current phone#
the current ext
my post of 2 May 22:46 suggests a technique. But as I suggested you need to call the function 4 times within the before update to check
is phone and phoneExt a dupe
is phone2 and ext2 a dupe
is phone3 and ext3 a dupe
is fax and faxext a dupe.
as I said it would look something like:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  if isDuplicate(me.txtCompanyID,me.txtPhone,me.TxtExt) then
    msgbox "Primary Phone is a duplicate"
    ...
  elseif isDuplicate(me.txtCompanyID,me.txtPhone2,me.TxtExt2) then
    msgbox "Phone2 is a duplicate"
  elseif isDuplicate(me.txtCompanyID,me.txtPhone3,me.TxtExt3) then
    msgbox "Phone3 is a duplicate
    ..
  elseif isDuplicate(me.txtCompanyID,me.txtfax,me.Txtfaxext) then
   msgbox "Fax is a duplicate"
   ..
  end if
End Sub

each time I call the function I pass in 3 parameters so it knows what to check

 

[MajP]

You would call isduplicate2 the same way just change the function name

 

[lmcc007]

Hi MajP,

Tried the code, but keep receiving the error below:

Run-time error '3061':

Too few parameters. Expected 1.

When I hit Debug, it brings me to the isDuplicate function and highlights in yellow:

Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)

What am I suppose to do now?

Thanks!

 

[MajP]

It does not like my sql code. Could be lots or reasons. I use different datatypes then you, my names are not exact as yours.

You can try the second function I provided. Or put a debug.print StrSql in the code after the sql is built. Copy the string from the debug window and then pop it in the query developer. See if it runs. I pass in the ID as a long. You most likely need to change that to a string.
(ID As Long, strNumber As String, Optional strExt = "")...
to
(ID as string,..)

 

[lmcc007]

I am totally lost. Remember, I am learning VBA--not a pro like you guys.