DCount

L

lmcc

When entering data in the contact form I want it to check for duplicates
phone numbers; therefore, I entered the following code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("[CompanyID]", "Telephone", _
"[CompanyID] = " & Me.CompanyID & " And " & _
"[TelephoneNumber] = " & Me.txtTelephoneNumber) > 0 Then
MsgBox "Telephone number is already used.", , "Telephone Number"
Cancel = True
Exit Sub
End If

End Sub

I get the following error message:

Run-time error '3464':

Data type mismatch in criteria expression

End Degub


I can't figure out the problem.

Any help is appreciated. Thanks!
 
D

Dirk Goldgar

lmcc said:
When entering data in the contact form I want it to check for duplicates
phone numbers; therefore, I entered the following code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("[CompanyID]", "Telephone", _
"[CompanyID] = " & Me.CompanyID & " And " & _
"[TelephoneNumber] = " & Me.txtTelephoneNumber) > 0 Then
MsgBox "Telephone number is already used.", , "Telephone
Number"
Cancel = True
Exit Sub
End If

End Sub

I get the following error message:

Run-time error '3464':

Data type mismatch in criteria expression

End Degub


I can't figure out the problem.

Any help is appreciated. Thanks!
Click to expand...


Is TelephoneNumber a text field? If so, you need to wrap it in quotes:

If DCount("[CompanyID]", "Telephone", _
"[CompanyID] = " & Me.CompanyID & " And " & _
"[TelephoneNumber] = '" & Me.txtTelephoneNumber & "'") > 0 Then
 
L

lmcc

Wow, Dirk I can't believe I missed the quotes. I look over this code over
and over and did not see it. Thank you!

Also, do you know why this code will only work properly on the Form Event
Before Update and not when I put it on the Control Event Before Update and I
tried it on the After Update and it does not work?

Dirk said:
When entering data in the contact form I want it to check for duplicates
phone numbers; therefore, I entered the following code:
Click to expand...
[quoted text clipped - 22 lines]
Any help is appreciated. Thanks!
Click to expand...

Is TelephoneNumber a text field? If so, you need to wrap it in quotes:

If DCount("[CompanyID]", "Telephone", _
"[CompanyID] = " & Me.CompanyID & " And " & _
"[TelephoneNumber] = '" & Me.txtTelephoneNumber & "'") > 0 Then
Click to expand...
 
D

Dirk Goldgar

lmcc said:
Also, do you know why this code will only work properly on the Form Event
Before Update and not when I put it on the Control Event Before Update and
I
tried it on the After Update and it does not work?
Click to expand...


I don't see why the (corrected) code wouldn't work in the control's
BeforeUpdate event. It wouldn't work in the control's AfterUpdate event,
because that event has no Cancel argument to set to True -- the AfterUpdate
event can't be cancelled.

If you have the corrected code in the control's BeforeUpdate event, what
happens?

You'll have may have a problem, it seems to me, if you are editing a record
that has already been saved with the current phone number. In that case,
the previously saved version of it will turn up in your DCount. You may
need to revise your criteria to exclude the primary key of the current
record.
 
L

lmcc

I lost--not following you.

I just want it not let me enter the same telephone number twice for a company

Do you suggest something different?.
 
L

lmcc

Below is the entire code that am on using in the form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("[PhoneTypeID]", _
"Telephone", _
"[CompanyID] = " & Me.CompanyID & " And " & _
"[PhoneTypeID] = " & Me.PhoneTypeID) > 0 Then
MsgBox "Choose another phone type.", , "Phone Type"
Cancel = True
Exit Sub
End If

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


Does it look okay? Should I do something different?

Thanks!
 
D

Dirk Goldgar

lmcc said:
Below is the entire code that am on using in the form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("[PhoneTypeID]", _
"Telephone", _
"[CompanyID] = " & Me.CompanyID & " And " & _
"[PhoneTypeID] = " & Me.PhoneTypeID) > 0 Then
MsgBox "Choose another phone type.", , "Phone Type"
Cancel = True
Exit Sub
End If

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


Does it look okay? Should I do something different?
Click to expand...


What is the primary key of the [Telephone] Table?
 
L

lmcc via AccessMonster.com

Dirk said:
Below is the entire code that am on using in the form:
Click to expand...
[quoted text clipped - 19 lines]
Does it look okay? Should I do something different?
Click to expand...

What is the primary key of the [Telephone] Table?
Click to expand...
The primary key is TelephoneID. Below are all the fields in the table:

TelephoneID (primary key)
CompanyID (related table)
PhoneTypeID (lookup field)
TelephoneNumber (text field)
TelephoneExtension (text field)
 
D

Dirk Goldgar

lmcc via AccessMonster.com said:
Dirk said:
Below is the entire code that am on using in the form:
Click to expand...
[quoted text clipped - 19 lines]
Does it look okay? Should I do something different?
Click to expand...

What is the primary key of the [Telephone] Table?
Click to expand...
The primary key is TelephoneID. Below are all the fields in the table:

TelephoneID (primary key)
CompanyID (related table)
PhoneTypeID (lookup field)
TelephoneNumber (text field)
TelephoneExtension (text field)
Click to expand...


Okay, then the DCount expressions need to ignore records with the same
TelephoneID as the one we're about to save. Try this code:

'------ start of code ------
Private Sub Form_BeforeUpdate(Cancel As Integer)

If DCount( _
"PhoneTypeID", _
"Telephone", _
"CompanyID = " & Me.CompanyID & _
" And " & _
"PhoneTypeID = " & Me.PhoneTypeID & _
" And " & _
"TelephoneID <> " & Me.TelephoneID _
) > 0 _
Then
MsgBox "Choose another phone type.", , "Phone Type"
Cancel = True
Exit Sub
End If

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

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

I'm assuming the the TelephoneID field is also on the form, or in the form's
recordset.
 
L

lmcc via AccessMonster.com

I can't get it to work. I copied and pasted the code, but keep getting the
following error message:

Compile error: Method or data member not found. OK HELP

It is highlighting .CompanyID--the second line of the second code.


Dirk said:
[quoted text clipped - 11 lines]
TelephoneNumber (text field)
TelephoneExtension (text field)
Click to expand...

Okay, then the DCount expressions need to ignore records with the same
TelephoneID as the one we're about to save. Try this code:

'------ start of code ------
Private Sub Form_BeforeUpdate(Cancel As Integer)

If DCount( _
"PhoneTypeID", _
"Telephone", _
"CompanyID = " & Me.CompanyID & _
" And " & _
"PhoneTypeID = " & Me.PhoneTypeID & _
" And " & _
"TelephoneID <> " & Me.TelephoneID _
) > 0 _
Then
MsgBox "Choose another phone type.", , "Phone Type"
Cancel = True
Exit Sub
End If

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

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

I'm assuming the the TelephoneID field is also on the form, or in the form's
recordset.
Click to expand...
 
L

lmcc via AccessMonster.com

I made changes to the code since yesterday. Here is the code that I am using
in the form:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If DCount("[PhoneTypeID]", "Telephone", _
"[CompanyID] = " & Me.CompanyID & " And " & _
"[PhoneTypeID] = " & Me.PhoneTypeID) > 0 Then
MsgBox "Choose another phone type.", , "Phone Type"
Cancel = True
Else
If DCount("[CompanyID]", "Telephone", _
"[CompanyID] = " & Me.CompanyID & " And " & _
"[TelephoneNumber] = '" & Me.TelephoneNumber & "'") > 0 Then
If MsgBox("Telephone number is already used. You want it?
", vbYesNo, "Telephone Number") = vbNo Then
Cancel = True
End If
End If
End If
End Sub
 
D

Dirk Goldgar

lmcc via AccessMonster.com said:
I can't get it to work. I copied and pasted the code, but keep getting the
following error message:

Compile error: Method or data member not found. OK HELP

It is highlighting .CompanyID--the second line of the second code.
Click to expand...

And do you have a control (or field) named CompanyID on the form? The
message says you don't.
 
D

Dirk Goldgar

lmcc via AccessMonster.com said:
I made changes to the code since yesterday. Here is the code that I am
using
in the form:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If DCount("[PhoneTypeID]", "Telephone", _
"[CompanyID] = " & Me.CompanyID & " And " & _
"[PhoneTypeID] = " & Me.PhoneTypeID) > 0 Then
MsgBox "Choose another phone type.", , "Phone Type"
Cancel = True
Else
If DCount("[CompanyID]", "Telephone", _
"[CompanyID] = " & Me.CompanyID & " And " & _
"[TelephoneNumber] = '" & Me.TelephoneNumber & "'") > 0 Then
If MsgBox("Telephone number is already used. You want
it?
", vbYesNo, "Telephone Number") = vbNo Then
Cancel = True
End If
End If
End If
End Sub
Click to expand...


This is somewhat better than the code you originally had, because it avoids
the unnecessary "Exit Sub" statement; however, it isn't functionally
different. Once you figure out the error message you're getting, you can
adapt the code I posted to be more like this if you want.
 
L

lmcc via AccessMonster.com

I couldn't get this " "TelephoneID <> " & Me.TelephoneID" part to work. When
I removed it, it worked.

What is "TelephoneID <> " & Me.TelephoneID suppose to do?
 
D

Dirk Goldgar

lmcc via AccessMonster.com said:
I couldn't get this " "TelephoneID <> " & Me.TelephoneID" part to work.
When
I removed it, it worked.

What is "TelephoneID <> " & Me.TelephoneID suppose to do?
Click to expand...


When you perform your checks for duplicates, you need to ensure that the
record you are currently updating is not counted as a duplicate when you use
DLookup to count duplicates. That won't matter if you are adding a new
record, but it will matter if you are updating an existing one.

You told me that TelephoneID is the primary key of the table, so that
criterion ("TelephoneID <> " & Me.TelephoneID) in the DLookup should exclude
this current record from the count. However, as I mentioned in my earlier
post, the criterion requires that the TelephoneID field exist as a control
on the form, or at least as a field in the form's recordset.

What is the recordsource of the form?
 
L

lmcc via AccessMonster.com

Yes, I have CompanyID. I retyped it, chose me.CompanyID, but still got the
error message.
 
L

lmcc via AccessMonster.com

I went back to my original database and copied it so I could retry everything
fresh.

I am still getting the same error message, which is:

Compile error: Method or data member not found

It goes to:

If DCount( _
"CompanyID", _
"Telephone", _
"CompanyID = " & Me.CompanyID

and highlights .CompanyID
 
D

Dirk Goldgar

lmcc via AccessMonster.com said:
I went back to my original database and copied it so I could retry
everything
fresh.

I am still getting the same error message, which is:

Compile error: Method or data member not found

It goes to:

If DCount( _
"CompanyID", _
"Telephone", _
"CompanyID = " & Me.CompanyID

and highlights .CompanyID
Click to expand...


Are you assigning the form's RecordSource at runtime, by any chance? Does
it work if you change the dot (.) to a bang (!), as in:

"CompanyID = " & Me!CompanyID

?
 
L

lmcc via AccessMonster.com

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

frmTelephone
Record Source = tblTelephone
Before Update = [Event Procedure] -- this is where I pasted the code.

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

Dirk said:
I went back to my original database and copied it so I could retry
everything
Click to expand...
[quoted text clipped - 12 lines]
and highlights .CompanyID
Click to expand...

Are you assigning the form's RecordSource at runtime, by any chance? Does
it work if you change the dot (.) to a bang (!), as in:

"CompanyID = " & Me!CompanyID

?
Click to expand...
 
D

Dirk Goldgar

lmcc via AccessMonster.com said:
No, I only copied and pasted the code. My form is:

frmTelephone
Record Source = tblTelephone
Before Update = [Event Procedure] -- this is where I pasted the code.

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Duplicates phone numbers and extensions 6
Check for similar phone types. 6
Limiting number of duplicate records 1
Dcount operation 2
Open alternate form with criteria 2
Error 13 Type Mismatch 2
DMax on existing record to increment serialized # fails 1
Set focus problem 21

Top