Duplicates phone numbers and extensions

[lmcc via AccessMonster.com]

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!

 

[John W. Vinson]

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.

Ouch.

It would really, really be a lot easier to have a tblPhones related one to
many to tblCompany; it would have fields Phone, PhoneType (e.g. "Work", "Fax")
and an optional Ext field. You could have a unique Index to prevent adding
duplicates in the first place. This would also allow for that big company that
has *six* phones that you need to record.

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

This can perhaps be made to work if every company has all four phone fields
filled, they all match, and none of the fields are NULL... but that's not what
you want.

With your current flawed design, you'll need a loop - comparing *each* of the
four phone numbers you enter on the form to *each* of the four phone numbers
in the table, controlling for possibly null extensions. Nasty complicated
code, and unnecessary if you normalize!

 

[lmcc via AccessMonster.com]

I understand about a separate tblPhones table. I did that for another
database and work great.

This is another database, and they have a set look and want to stick with
their design--plus they do not like subforms look. It's a simple database,
but the design is:

Company ABC Production
Add1 123 Nowhere Lane
Add2
City/State/Zip Detroit, MI 77001

Business (123) 456-7890 ext. 4000
Business 2 ( ) ext.
Mobile ( ) ext.
Fax (888) ext.

Industry Mfg
Acct # 10000

And a couple more fields are included in the database.

I have been trying for a while to get them what they want but I keep facing a
brick wall.

With subforms if there is no record, the that design will not show. And, if
there is only one phone number, you get the one number and a blank row to
enter another.

I tried four text boxes with the phone number field added in as a subform.
That achieved the look, but it created extra blank fields in the tblPhone
table.

I am out of ideas, and thought this code would work.

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.

Ouch.

It would really, really be a lot easier to have a tblPhones related one to
many to tblCompany; it would have fields Phone, PhoneType (e.g. "Work", "Fax")
and an optional Ext field. You could have a unique Index to prevent adding
duplicates in the first place. This would also allow for that big company that
has *six* phones that you need to record.

I want the code
to check for duplicates when entering or revising telephone numbers. I

[quoted text clipped - 12 lines]

MsgBox "Telephone number is already used.", , "Telephone number
search."

This can perhaps be made to work if every company has all four phone fields
filled, they all match, and none of the fields are NULL... but that's not what
you want.

With your current flawed design, you'll need a loop - comparing *each* of the
four phone numbers you enter on the form to *each* of the four phone numbers
in the table, controlling for possibly null extensions. Nasty complicated
code, and unnecessary if you normalize!

 

[Steve Sanford]

Like John said, it would really be a lot easier to have a tblPhones related
one to
many to tblCompany. But just because 'they' like a certain look, doesn't
mean the underlying structure has to be "wrong".

There are at least 3 other ways to display the phone numbers using the
related tables.

~Lots of work: the form can be unbound and code reads and writes the data.
(LOTS of work)

~Some work: the form still bound, except for the phone data. the phone
number data is read from and wrote to by code.

~By using a query: use a query to transpose the phone data fron "columns" to
"rows". The form is bound.... easy, but still limited to the 4 phone numbers.



But to solve your immediate dilemma, here is some code that should get you
moving forward.

I read the numbers into an array, sort the array, then compare the current
number in the array to the next number. The lines commented out are for
debugging purposes.

(watch for line wrap)
'--------beg code-----------------
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim PH(4, 2) ' an array
Dim x As Integer
Dim y As Integer

'variables for sorting
Dim swapped As Boolean
Dim P1 As String
Dim E1 As String

Dim c As Integer 'counter

'you should add checks here for Phone number length
' to ensure the phone number comparisons
' are comparing apples to apples


'fill array with the phone num and ext
For x = 1 To 3
For y = 1 To 2
If x = 1 Then
If Len(Trim(Me.Phone)) > 0 Then
PH(1, 1) = Trim(Me.Phone)
End If
If Len(Trim(Me.Ext)) > 0 Then
PH(1, 2) = Trim(Me.Ext)
End If
Else
If Len(Trim(Me("Phone" & x))) > 0 Then
PH(x, 1) = Trim(Me("Phone" & x))
End If
If Len(Trim(Me("Ext" & x))) > 0 Then
PH(x, 2) = Trim(Me("Ext" & x))
End If
End If
Next y
Next x
If Len(Trim(Me.Fax)) > 0 Then
PH(4, 1) = Trim(Me.Fax)
End If
If Len(Trim(Me.FaxExt)) > 0 Then
PH(4, 2) = Trim(Me.FaxExt)
End If


' print phone nums/ext to debug
'---------------------------------------
' For x = 1 To 4
' For y = 1 To 2
' Debug.Print x, y, PH(x, y)
' Next y
' Next x
'
' Debug.Print
'
' For x = 1 To 4
' If Len(PH(x, 1)) > 0 Then
' c = c + 1
' End If
' Next x
' Debug.Print c
'---------------------------------------


'now sort the Ph numbers & ext
'using the bubble sort
'(yeah, its slow......but only 4 elements)
Do
swapped = False
For x = 1 To 3
If PH(x, 1) > PH(x + 1, 1) And PH(x, 2) > PH(x + 1, 2) Then
' swap
P1 = PH(x, 1)
E1 = PH(x, 2)
PH(x, 1) = PH(x + 1, 1)
PH(x, 2) = PH(x + 1, 2)
PH(x + 1, 1) = P1
PH(x + 1, 2) = E1

swapped = True
End If
Next
Loop While swapped

' print sorted phone nums/ext to debug
'---------------------------------------
' Debug.Print 'blank lines
' Debug.Print 'blank lines
'
' For x = 1 To 4
' For y = 1 To 2
' Debug.Print x, y, PH(x, y)
' Next y
' Next x
'
' Debug.Print
'
' c = 0
' For x = 1 To 4
' If Len(PH(x, 1)) > 0 Then
' c = c + 1
' End If
' Next x
' Debug.Print c
'---------------------------------------

'=======do comparisons=========

'check the Phone and ext for duplicates
For x = 1 To 3
'first check the Phone not null
If Len(PH(x, 1)) > 0 And Len(PH(x + 1, 1)) Then
'check phone numbers
If PH(x, 1) = PH(x + 1, 1) Then
'check the Ext not null
If Len(PH(x, 2)) > 0 And Len(PH(x + 1, 2)) > 0 Then
'check the Ext
If PH(x, 2) = PH(x + 1, 2) Then
MsgBox "ERROR - duplicate Phone and ext. Please correct"
Cancel = True
Me.Undo
End If
End If
End If
End If
Next x

End Sub
'--------end code-----------------



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

I understand about a separate tblPhones table. I did that for another
database and work great.

This is another database, and they have a set look and want to stick with
their design--plus they do not like subforms look. It's a simple database,
but the design is:

Company ABC Production
Add1 123 Nowhere Lane
Add2
City/State/Zip Detroit, MI 77001

Business (123) 456-7890 ext. 4000
Business 2 ( ) ext.
Mobile ( ) ext.
Fax (888) ext.

Industry Mfg
Acct # 10000

And a couple more fields are included in the database.

I have been trying for a while to get them what they want but I keep facing a
brick wall.

With subforms if there is no record, the that design will not show. And, if
there is only one phone number, you get the one number and a blank row to
enter another.

I tried four text boxes with the phone number field added in as a subform.
That achieved the look, but it created extra blank fields in the tblPhone
table.

I am out of ideas, and thought this code would work.

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.

Ouch.

It would really, really be a lot easier to have a tblPhones related one to
many to tblCompany; it would have fields Phone, PhoneType (e.g. "Work", "Fax")
and an optional Ext field. You could have a unique Index to prevent adding
duplicates in the first place. This would also allow for that big company that
has *six* phones that you need to record.

I want the code
to check for duplicates when entering or revising telephone numbers. I

[quoted text clipped - 12 lines]

MsgBox "Telephone number is already used.", , "Telephone number
search."

This can perhaps be made to work if every company has all four phone fields
filled, they all match, and none of the fields are NULL... but that's not what
you want.

With your current flawed design, you'll need a loop - comparing *each* of the
four phone numbers you enter on the form to *each* of the four phone numbers
in the table, controlling for possibly null extensions. Nasty complicated
code, and unnecessary if you normalize!

--



.

 

[lmcc via AccessMonster.com]

Thanks Steve for all your hard work, but I have to say that I am not at all
an advanced user; I am still learning. I do not understand it, but will
reread until I have some type of understanding. I was copying and pasting
code without knowing what they do, but my computer crashed and Access kept
closing and restarting and I eventually had to have the computer cleaned and
stuff. Luckily there were a backup of data. I was advised not to copy and
paste--know the code before adding.

This particular database was created from Access contact template, so she
just followed how MS had set it up. The only problem is occasionally a
company will have the same phone number in Phone2 and Fax fields.

I thought it would very simple to do that:

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.

Like John said, it would really be a lot easier to have a tblPhones related
one to
many to tblCompany. But just because 'they' like a certain look, doesn't
mean the underlying structure has to be "wrong".

There are at least 3 other ways to display the phone numbers using the
related tables.

~Lots of work: the form can be unbound and code reads and writes the data.
(LOTS of work)

~Some work: the form still bound, except for the phone data. the phone
number data is read from and wrote to by code.

~By using a query: use a query to transpose the phone data fron "columns" to
"rows". The form is bound.... easy, but still limited to the 4 phone numbers.

But to solve your immediate dilemma, here is some code that should get you
moving forward.

I read the numbers into an array, sort the array, then compare the current
number in the array to the next number. The lines commented out are for
debugging purposes.

(watch for line wrap)
'--------beg code-----------------
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim PH(4, 2) ' an array
Dim x As Integer
Dim y As Integer

'variables for sorting
Dim swapped As Boolean
Dim P1 As String
Dim E1 As String

Dim c As Integer 'counter

'you should add checks here for Phone number length
' to ensure the phone number comparisons
' are comparing apples to apples

'fill array with the phone num and ext
For x = 1 To 3
For y = 1 To 2
If x = 1 Then
If Len(Trim(Me.Phone)) > 0 Then
PH(1, 1) = Trim(Me.Phone)
End If
If Len(Trim(Me.Ext)) > 0 Then
PH(1, 2) = Trim(Me.Ext)
End If
Else
If Len(Trim(Me("Phone" & x))) > 0 Then
PH(x, 1) = Trim(Me("Phone" & x))
End If
If Len(Trim(Me("Ext" & x))) > 0 Then
PH(x, 2) = Trim(Me("Ext" & x))
End If
End If
Next y
Next x
If Len(Trim(Me.Fax)) > 0 Then
PH(4, 1) = Trim(Me.Fax)
End If
If Len(Trim(Me.FaxExt)) > 0 Then
PH(4, 2) = Trim(Me.FaxExt)
End If

' print phone nums/ext to debug
'---------------------------------------
' For x = 1 To 4
' For y = 1 To 2
' Debug.Print x, y, PH(x, y)
' Next y
' Next x
'
' Debug.Print
'
' For x = 1 To 4
' If Len(PH(x, 1)) > 0 Then
' c = c + 1
' End If
' Next x
' Debug.Print c
'---------------------------------------

'now sort the Ph numbers & ext
'using the bubble sort
'(yeah, its slow......but only 4 elements)
Do
swapped = False
For x = 1 To 3
If PH(x, 1) > PH(x + 1, 1) And PH(x, 2) > PH(x + 1, 2) Then
' swap
P1 = PH(x, 1)
E1 = PH(x, 2)
PH(x, 1) = PH(x + 1, 1)
PH(x, 2) = PH(x + 1, 2)
PH(x + 1, 1) = P1
PH(x + 1, 2) = E1

swapped = True
End If
Next
Loop While swapped

' print sorted phone nums/ext to debug
'---------------------------------------
' Debug.Print 'blank lines
' Debug.Print 'blank lines
'
' For x = 1 To 4
' For y = 1 To 2
' Debug.Print x, y, PH(x, y)
' Next y
' Next x
'
' Debug.Print
'
' c = 0
' For x = 1 To 4
' If Len(PH(x, 1)) > 0 Then
' c = c + 1
' End If
' Next x
' Debug.Print c
'---------------------------------------

'=======do comparisons=========

'check the Phone and ext for duplicates
For x = 1 To 3
'first check the Phone not null
If Len(PH(x, 1)) > 0 And Len(PH(x + 1, 1)) Then
'check phone numbers
If PH(x, 1) = PH(x + 1, 1) Then
'check the Ext not null
If Len(PH(x, 2)) > 0 And Len(PH(x + 1, 2)) > 0 Then
'check the Ext
If PH(x, 2) = PH(x + 1, 2) Then
MsgBox "ERROR - duplicate Phone and ext. Please correct"
Cancel = True
Me.Undo
End If
End If
End If
End If
Next x

End Sub
'--------end code-----------------

HTH

I understand about a separate tblPhones table. I did that for another
database and work great.

[quoted text clipped - 57 lines]

 

[PieterLinden via AccessMonster.com]

I understand about a separate tblPhones table. I did that for another
database and work great.

This is another database, and they have a set look and want to stick with
their design--plus they do not like subforms look. It's a simple database,
but the design is:

Company ABC Production
Add1 123 Nowhere Lane
Add2
City/State/Zip Detroit, MI 77001

Business (123) 456-7890 ext. 4000
Business 2 ( ) ext.
Mobile ( ) ext.
Fax (888) ext.

Industry Mfg
Acct # 10000

And a couple more fields are included in the database.

I have been trying for a while to get them what they want but I keep facing a
brick wall.

With subforms if there is no record, the that design will not show. And, if
there is only one phone number, you get the one number and a blank row to
enter another.

I tried four text boxes with the phone number field added in as a subform.
That achieved the look, but it created extra blank fields in the tblPhone
table.

I am out of ideas, and thought this code would work.
[quoted text clipped - 22 lines]

in the table, controlling for possibly null extensions. Nasty complicated
code, and unnecessary if you normalize!

I hope you're getting a lot of extra money to do it the wrong way... Ask 'em
what they want you to do if they ever need more phone numbers... It would
make your job (and theirs in the long run) so much easier!

 

[lmcc via AccessMonster.com]

No, I am not. I am volunteering and trying to learn VBA at the same time.

I understand about a separate tblPhones table. I did that for another
database and work great.

[quoted text clipped - 36 lines]
I hope you're getting a lot of extra money to do it the wrong way... Ask 'em
what they want you to do if they ever need more phone numbers... It would
make your job (and theirs in the long run) so much easier!