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

[T5925MS]

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

 

[Ken Snell]

Which event are you using to run this code? It should be the form's
BeforeUpdate event, most likely.

Also, on which part of your form is the [FloorProgMaxObservations] control
located (form header? detail? form footer?)? or is that just a field in the
form's recordsource query? Are you using Single Form or Continuous Forms
view for your form?

 

[T5925MS via AccessMonster.com]

I'm using the form's BeforeUpdate event to run the code. The
[FloorProgMaxObservations] control
is just a field in the form's recordsource query. Should I have it in the
form somewhere? I'm using using Single Form view for the form.

Which event are you using to run this code? It should be the form's
BeforeUpdate event, most likely.

Also, on which part of your form is the [FloorProgMaxObservations] control
located (form header? detail? form footer?)? or is that just a field in the
form's recordsource query? Are you using Single Form or Continuous Forms
view for your form?

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

[quoted text clipped - 15 lines]

Cancel = True
End If