Limiting number of duplicate records

[Michael]

I'm trying to use If and DCount in my form to give me a message when a user
tries to create more than the allowed number of duplicate records. The number
of duplicates allowed is established in the table tblFloorProgCriteria.

These are the three fields where I’m looking for duplicates:

AuditID (Number Field)
FloorProgCriteriaID (Number Field)
AuditorID (Text Field)

Here’s what I have so far...

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblFloorProgAudit", "[AuditID] = " & Me.[AuditID] & _
" and [FloorProgCriteriaID] = " & Me.[FloorProgCriteriaID] & _
"' and [AuditorID] = '" & Me.[AuditorID] & "'") > ????(Help)???? Then
MsgBox "You cannot enter more than 5 observations for these
criteria. Delete 1 of the
observations."
Cancel = True
End If
End Sub

Thanks in advance for your help!

 

[Marshall Barton]

I'm trying to use If and DCount in my form to give me a message when a user
tries to create more than the allowed number of duplicate records. The number
of duplicates allowed is established in the table tblFloorProgCriteria.

These are the three fields where I’m looking for duplicates:

AuditID (Number Field)
FloorProgCriteriaID (Number Field)
AuditorID (Text Field)

Here’s what I have so far...

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblFloorProgAudit", "[AuditID] = " & Me.[AuditID] & _
" and [FloorProgCriteriaID] = " & Me.[FloorProgCriteriaID] & _
"' and [AuditorID] = '" & Me.[AuditorID] & "'") > ????(Help)???? Then
MsgBox "You cannot enter more than 5 observations for these
criteria. Delete 1 of the
observations."
Cancel = True
End If
End Sub

There's an extra ' in "' and [AuditorID] = ...

It seems like you may need to use a DLookup to get the
number of duplicates allowed. Rather than make the DCount
too complicated to read, I suggest doing it in a separate
statement.