DCount

[cuttsy]

Below is code for checking for duplicates of a surname at a school.

"duplicates" is a query with school criteria used from the open form

Private Sub Surname_LostFocus()

Dim counter As Long

counter = DCount("*", "duplicates", "Surname = [Forms]![frmSchools]![frmContacts].[Form]![surname]")
If counter > 0 Then
MsgBox "There is a contact at this school with this surname. Please ensure they are different people. ", vbExclamation, " Possible Duplication Error"
End If

End Sub

It Shows the message box if there are duplicates or not. The only time the message doesn't show is when the surname text box is left blank.

Can someone explain my error?

 

[KenHigg]

To start with you need to break your control reference out of the domain function...

DCount("*", "duplicates", "Surname = '" & [Forms]![frmSchools]![frmContacts].[Form]![surname] & "'")

 

[KenHigg]

Also, If you're going to trouble of counting, share it with the user...


MsgBox "There are " & counter & " contact(s) at this school with this surname. Please ensure they are different people. ", vbExclamation, " Possible Duplication Error"
End If

 

[Mile-O]

Better safe than sorry when working with surnames:

DCount("*", "duplicates", "Surname = """ & [Forms]![frmSchools]![frmContacts].[Form]![surname] & """")

 

[cuttsy]

I have changed that line and it is still showing the message for surnames that are unique.

 

[KenHigg]

Something looks fishy. How did you come up with 'duplicates' as a table name ?

 

[cuttsy]

duplicates is a query. Containing personID sName fName and school (This did have a criteria of the school textbox value). I've now read that you can use queries in the DCount only if they have no criteria. I have taken the criteria out and it still says any entry is a duplicate. Ill add a new person called
"Mr carslahdkiujh" It still says its a duplicate.

I realy want to have this in to make data entry as idiot proof as possible but if i dont figure it out soon I may have to miss it out.

 

[Mile-O]

I've now read that you can use queries in the DCount only if they have no criteria.

Where did you read that rubbish?

 

[cuttsy]

A website, can't find it now though.

 

[KenHigg]

I'd use the debugger to look at 'count'

Mile-O-Phile: Could this be a null issue?

 

[Mile-O]

A website, can't find it now though.

Well it's talking rubbish. Most likely yu've visited Martin Green's world of Access where even Lewis Carrol and Edward Lear combined couldn't dream up as much nonsense.

Could this be a null issue?

If it was a Null issue there would be an error that popped up and said "Invalid use of Null".

Try specifying an exact field in the query 'duplicates rather than using the wildcard.

 

[KenHigg]

Just a tought: Unless I had something to do with the number of records I wouldn't have counted them and would have used dlookup and tested

if not isnull(dlookup("sName", "duplicates", "Surname = [Forms]![frmSchools]![frmContacts].[Form]![surname]")) then
MsgBox "There is a contact at this school with this surname. Please ensure they are different people. ", vbExclamation, " Possible Duplication Error"

(I think this syntax is correct)

 

[cuttsy]

Fixed

I have it working. I changed the duplicates query criteria for surname and school to the values currently on the form and used the code below and it works Fine.

Thanks a lot for your time guys.

Private Sub Surname_AfterUpdate()

Dim counter As Long

counter = DCount("sName", "duplicates")
If counter > 0 Then
MsgBox "There is a contact at this school with this surname. Please ensure they are different people. ", vbExclamation, " Possible Duplication Error"
End If

End Sub