I need a message if number of duplicate records exceeds number all

[Michael]

Thanks in advance for any help. I'm trying to create If DCount code on my
form that will count the number of duplicate records and then notify me when
the number of duplicate records exceeds the number allowed. The number of
duplicates allowed is established in tblFloorProgCriteria in the field
FloorProgMaxObservations. Here's what I have so far...It's giving me the
message as soon as I attempt to add the first record.

If DCount("*", "tblFloorProgAudit", "[AuditID] = " & Me.[AuditID] &
" And [FloorProgCriteriaID] = " & _
Me.[FloorProgCriteriaID] & " And [AuditorID] = '" & Me.[AuditorID] &
"'") > " & Me.[FloorProgMaxObservations] & " Then
MsgBox "You're attempting to exeed the maximum number of
observation allowed for this criteria set. Click OK and then delete this
observation."
Cancel = True
End If

 

[Jeanette Cunningham]

I like to write the code in more steps so you can test each step to see
where the problem is.

Something like this
--start of sample code
Dim lngCount As Long
Dim strCriteria As String
Dim lngMaxObservations As Long

lngMaxObversations = Me.FloorProgMaxObservations
Debug.Print lngMaxObservations

strCriteria = "[AuditID] = " & Me.[AuditID] " _
& " And [FloorProgCriteriaID] = " & Me.[FloorProgCriteriaID] " _
& " And [AuditorID] = " & Me.[AuditorID]
Debug.Print strCriteria

lngCount = DCount("*", "tblFloorProgAudit", strCriteria)
Debug.Print lngCount

If lngCount > lngMaxObservations Then
MsgBox "You're attempting to exeed the maximum number of
observation allowed for this criteria set. Click OK and then delete this
observation."
Cancel = True
End If
--end of sample code.
You can run the code while the form is in form view then press Ctl+G to open
the immediate window where you will see the values for the 3 debug.print
statements

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia