DCount Not Providing Expected Results

[ARK72]

Hi,
I've been working on trying to get this code to work as expected for days. I'm trying to find duplicates (I can't use primary keys or indexes alone to weed out duplicates due to the structure of the tables involved) in a subform as a user enters data. As soon as a project number is added, the code is supposed to count the number of records that contain that particular project number as well as a category number (there can be multiples of the same project numbers as long as their category numbers are different). This is the code I am using in the "Before Update" event of the field in the subform:

Private Sub ProjectID_BeforeUpdate(Cancel As Integer)

Dim strCriteria As String
Dim RecCount As Integer

strCriteria = "([ProjectID] = " & Me.ProjectID & ") AND ([CatID] = " & Me.CatID & ")"
RecCount = DCount("[ProjHrsID]", "tblProjHrs", strCriteria)

If RecCount > 1 Then
MsgBox "Duplicate Project Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
Me.ProjectID.Undo
Cancel = True
Else
'Do Nothing
End If

End Sub

What could I be doing wrong? Nothing about this code seems to work properly - even the Undo and Cancel=True is a problem (I get the "No current record" error). If anyone could help me out, I'd really appreciate it. Thanks in advance!

 

[MarkK]

Probably one thing is you don't mean reccount > 1. You prohibit duplicates, correct? So before your pending update there can never be more than one, so reccount > 1 is never true. You want to know if one record already exists, so check for reccount > 0.

But I would set this as a constraint in an index of the table so that the table prohibits the duplication. You can create an index that requires uniqueness in a single field, but you can also create an index that requires uniqueness in a combination of fields, and that constraint will be enforced even if your code based validation never runs.

 

[ARK72]

I can't imagine why I hadn't seen that! Thank you! It is now fixed. And, I appreciate the indexing idea. I will definitely look into that.