DCount

[lmcc via AccessMonster.com]

You correct--I meant to type tblTelephone.

No, I only copied and pasted the code. My form is:

[quoted text clipped - 3 lines]

I change the dot to a bang and received the same error.

What is the difference between "tblTelephone", which you say is the form's
recordsource, and "Telephone", which you are using in the "domain" argument
of your DCount() expressions? I would expect them both to be
"tblTelephone".

 

[Dirk Goldgar]

You correct--I meant to type tblTelephone.

Now I don't know what to think. If you have not been copying and pasting
your code, but have been typing it in over again, I can't trust anything
that has gone before. Please *copy and paste* (a) the RecordSource property
of the form, and (b) the code from the form's BeforeUpdate event procedure.
Also, please tell me the exact and correct names and data types of the
fields in the table(s) involved, and the names and ControlSource properties
of all data-bound controls on the form.

 

[lmcc via AccessMonster.com]

I created a small test database and reentered all the data without copying
and pasting and it's working. Here is the code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("[PhoneTypeID]", "tblTelephone", _
"[CompanyID] = " & Me.CompanyID & " And " & _
"[PhoneTypeID] = " & Me.PhoneTypeID & " And " & _
"TelephoneID <> " & Me.TelephoneID) > 0 Then
MsgBox "Please, choose another phone type.", , "Phone
Type"
Cancel = True
Else

If DCount("[CompanyID]", "tblTelephone", _
"[CompanyID] = " & Me.CompanyID & " And " & _
"[TelephoneNumber] = '" & Me.TelephoneNumber & "'" & " And " & _
"TelephoneID <> " & Me.TelephoneID) > 0 Then
MsgBox "Telephone number is being used.", , "Telephone
Number"
Cancel = True
End If
End If
End Sub


Sometimes when you reread, reread, and reread you still can't see what is
wrong. Also, I do have to say that in table design view, I did not set any
validation rules on none of the fields.

I do appreciate all your help and thank you for hanging in here with me.
Thanks!

 

[Dirk Goldgar]

I created a small test database and reentered all the data without copying
and pasting and it's working. Here is the code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("[PhoneTypeID]", "tblTelephone", _
"[CompanyID] = " & Me.CompanyID & " And " & _
"[PhoneTypeID] = " & Me.PhoneTypeID & " And " & _
"TelephoneID <> " & Me.TelephoneID) > 0 Then
MsgBox "Please, choose another phone type.", , "Phone
Type"
Cancel = True
Else

If DCount("[CompanyID]", "tblTelephone", _
"[CompanyID] = " & Me.CompanyID & " And " & _
"[TelephoneNumber] = '" & Me.TelephoneNumber & "'" & " And " &
_
"TelephoneID <> " & Me.TelephoneID) > 0 Then
MsgBox "Telephone number is being used.", , "Telephone
Number"
Cancel = True
End If
End If
End Sub


Sometimes when you reread, reread, and reread you still can't see what is
wrong. Also, I do have to say that in table design view, I did not set
any
validation rules on none of the fields.

I do appreciate all your help and thank you for hanging in here with me.
Thanks!

So it's working now, and the case is closed? Great!

You're welcome.