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

 

[DevlinM]

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strSQL as String
Dim RS as Recordset
Dim DB as Database
Dim MaxCount as Integer

MaxCount = 5

strSQL = "SELECT Count(PlanTypeID) AS CountOfPlanTypes " _
"FROM FloorPlans " _
"GROUP BY PlanTypeID " _
"WHERE PlanTypeID= " & FormValue & ") " _
"HAVING ((Count(PlanTypeID))>1));

Set DB = CurrentDB
Set RS = DB.Openrecordset(strSQL, dbOpenSnapshot)

if(Not RS.EOF) then
If(RS.Fields(0)+1 > MaxCount) then
Cancel = True
MsgToUser
End If
End If

'Cleanup and exit
End Sub

 

[DevlinM]

Correction: Removed "Group By" clause from SQL statement, it isn't used

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strSQL as String
Dim RS as Recordset
Dim DB as Database
Dim MaxCount as Integer

MaxCount = 5

strSQL = "SELECT Count(PlanTypeID) AS CountOfPlanTypes " _
"FROM FloorPlans " _
"WHERE PlanTypeID= " & FormValue & ") " _
"HAVING ((Count(PlanTypeID))>1));

Set DB = CurrentDB
Set RS = DB.Openrecordset(strSQL, dbOpenSnapshot)

if(Not RS.EOF) then
If(RS.Fields(0)+1 > MaxCount) then
Cancel = True
MsgToUser
End If
End If

'Cleanup and exit
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strSQL as String
Dim RS as Recordset
Dim DB as Database
Dim MaxCount as Integer

MaxCount = 5

strSQL = "SELECT Count(PlanTypeID) AS CountOfPlanTypes " _
"FROM FloorPlans " _
"GROUP BY PlanTypeID " _
"WHERE PlanTypeID= " & FormValue & ") " _
"HAVING ((Count(PlanTypeID))>1));

Set DB = CurrentDB
Set RS = DB.Openrecordset(strSQL, dbOpenSnapshot)

if(Not RS.EOF) then
If(RS.Fields(0)+1 > MaxCount) then
Cancel = True
MsgToUser
End If
End If

'Cleanup and exit
End Sub

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

.

 

[DevlinM]

Also, Just FYI: Your use of DCount will also work, however, you can only
count one field or field expression. You are attempting to count all fields
in the table by using the "*".
DCount (FieldName, TableName, Criteria) Proper
DCount ("*", TableName, Criteria) Inproper

Correction: Removed "Group By" clause from SQL statement, it isn't used

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strSQL as String
Dim RS as Recordset
Dim DB as Database
Dim MaxCount as Integer

MaxCount = 5

strSQL = "SELECT Count(PlanTypeID) AS CountOfPlanTypes " _
"FROM FloorPlans " _
"WHERE PlanTypeID= " & FormValue & ") " _
"HAVING ((Count(PlanTypeID))>1));

Set DB = CurrentDB
Set RS = DB.Openrecordset(strSQL, dbOpenSnapshot)

if(Not RS.EOF) then
If(RS.Fields(0)+1 > MaxCount) then
Cancel = True
MsgToUser
End If
End If

'Cleanup and exit
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strSQL as String
Dim RS as Recordset
Dim DB as Database
Dim MaxCount as Integer

MaxCount = 5

strSQL = "SELECT Count(PlanTypeID) AS CountOfPlanTypes " _
"FROM FloorPlans " _
"GROUP BY PlanTypeID " _
"WHERE PlanTypeID= " & FormValue & ") " _
"HAVING ((Count(PlanTypeID))>1));

Set DB = CurrentDB
Set RS = DB.Openrecordset(strSQL, dbOpenSnapshot)

if(Not RS.EOF) then
If(RS.Fields(0)+1 > MaxCount) then
Cancel = True
MsgToUser
End If
End If

'Cleanup and exit
End Sub

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]

What is "FormValue" in your code? A function? How does it get its value?
Is PlanTypeID a numeric field? or a text field?
--

Ken Snell
http://www.accessmvp.com/KDSnell/

Correction: Removed "Group By" clause from SQL statement, it isn't used

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strSQL as String
Dim RS as Recordset
Dim DB as Database
Dim MaxCount as Integer

MaxCount = 5

strSQL = "SELECT Count(PlanTypeID) AS CountOfPlanTypes " _
"FROM FloorPlans " _
"WHERE PlanTypeID= " & FormValue & ") " _
"HAVING ((Count(PlanTypeID))>1));

Set DB = CurrentDB
Set RS = DB.Openrecordset(strSQL, dbOpenSnapshot)

if(Not RS.EOF) then
If(RS.Fields(0)+1 > MaxCount) then
Cancel = True
MsgToUser
End If
End If

'Cleanup and exit
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strSQL as String
Dim RS as Recordset
Dim DB as Database
Dim MaxCount as Integer

MaxCount = 5

strSQL = "SELECT Count(PlanTypeID) AS CountOfPlanTypes " _
"FROM FloorPlans " _
"GROUP BY PlanTypeID " _
"WHERE PlanTypeID= " & FormValue & ") " _
"HAVING ((Count(PlanTypeID))>1));

Set DB = CurrentDB
Set RS = DB.Openrecordset(strSQL, dbOpenSnapshot)

if(Not RS.EOF) then
If(RS.Fields(0)+1 > MaxCount) then
Cancel = True
MsgToUser
End If
End If

'Cleanup and exit
End Sub

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

.

 

[T5925MS via AccessMonster.com]

Sorry, I'm not sure what DevlinM was responding to because the question I
posted didn't include any of those fields.

What is "FormValue" in your code? A function? How does it get its value?
Is PlanTypeID a numeric field? or a text field?

Correction: Removed "Group By" clause from SQL statement, it isn't used

[quoted text clipped - 77 lines]

 

[T5925MS via AccessMonster.com]

I can't even describe to you how much I appreciate your assistance on this
issue I'm having trouble with DevlinM. Is your response an example that was
used in another application that uses these table/fields names? The reason I
ask is because I'm confused about where the table/field names came from that
you used in your response. If these table/field names are examples that I can
replace with my table/field names, how does your example look up the number
of duplicate records that exceed the number allowed?

Correction: Removed "Group By" clause from SQL statement, it isn't used

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strSQL as String
Dim RS as Recordset
Dim DB as Database
Dim MaxCount as Integer

MaxCount = 5

strSQL = "SELECT Count(PlanTypeID) AS CountOfPlanTypes " _
"FROM FloorPlans " _
"WHERE PlanTypeID= " & FormValue & ") " _
"HAVING ((Count(PlanTypeID))>1));

Set DB = CurrentDB
Set RS = DB.Openrecordset(strSQL, dbOpenSnapshot)

if(Not RS.EOF) then
If(RS.Fields(0)+1 > MaxCount) then
Cancel = True
MsgToUser
End If
End If

'Cleanup and exit
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strSQL as String

[quoted text clipped - 42 lines]

 

[DevlinM]

This is quite simple to understand. First, I don't know or care about all
the table fields you are using, nor do I know what form fields you are using.
Without taking the time to determine the details of your table attributes or
your form(s), I have given you a generic code structure.

The query structure is sound, just replace column values with your own. Add
any others if necessary. The variable "FormValue" would be whatever input
field on your form that is utilized for comparison.

Thanks,

Devlin

Please feel free to let me know if you still have questions.

I can't even describe to you how much I appreciate your assistance on this
issue I'm having trouble with DevlinM. Is your response an example that was
used in another application that uses these table/fields names? The reason I
ask is because I'm confused about where the table/field names came from that
you used in your response. If these table/field names are examples that I can
replace with my table/field names, how does your example look up the number
of duplicate records that exceed the number allowed?

Correction: Removed "Group By" clause from SQL statement, it isn't used

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strSQL as String
Dim RS as Recordset
Dim DB as Database
Dim MaxCount as Integer

MaxCount = 5

strSQL = "SELECT Count(PlanTypeID) AS CountOfPlanTypes " _
"FROM FloorPlans " _
"WHERE PlanTypeID= " & FormValue & ") " _
"HAVING ((Count(PlanTypeID))>1));

Set DB = CurrentDB
Set RS = DB.Openrecordset(strSQL, dbOpenSnapshot)

if(Not RS.EOF) then
If(RS.Fields(0)+1 > MaxCount) then
Cancel = True
MsgToUser
End If
End If

'Cleanup and exit
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strSQL as String

[quoted text clipped - 42 lines]

.

--



.

 

[AccessVandal via AccessMonster.com]

The result will always be "True" unless there's no record. What is number of
exceeded records? You did not indicate with an "=" operator or whatever. And
why did it work? I'm puzzled. You have a missing parenthesis.

Here's the added parenthesis. "Me.[FloorProgMaxObservations] & ")" Then

Please also correct the code as you also have missing "&".

T5925MS wrote:
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

 

[T5925MS via AccessMonster.com]

The number of duplicates allowed for records that have the same values on the
three fields is established in the table tblAdminProgCriteria. The field in
that table is called FloorProgMaxObservations. This one's really got me
stumped because if I use the same code and just put a fixed number in the
code, rather than " & Me.[FloorProgMaxObservations] & " it works fine.

This is the code works correctly but it doesn't meet my needs:

If DCount("*", "tblFloorProgAudit", "[AuditID] = " & Me.[AuditID] & "
And [FloorProgCriteriaID] = " & _
Me.[FloorProgCriteriaID] & " And [AuditorID] = '" & Me.[AuditorID] &
"'") > 5 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

The result will always be "True" unless there's no record. What is number of
exceeded records? You did not indicate with an "=" operator or whatever. And
why did it work? I'm puzzled. You have a missing parenthesis.

Here's the added parenthesis. "Me.[FloorProgMaxObservations] & ")" Then

Please also correct the code as you also have missing "&".

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

 

[AccessVandal via AccessMonster.com]

We can't what's the data like but here's the original code with the missing
quotes and ampersand.
Watch out for word wrap as it may remove spaces.

I notice the error is here " And [AuditorID] = '" & Me.[AuditorID] & "'") & "

" & _"

(the part " & Me.[AuditorID] & "'") & " > " & _"

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

I have remove the unwanted quote and ampersand after the "Me.
[FloorProgMaxObservations]"

The number of duplicates allowed for records that have the same values on the
three fields is established in the table tblAdminProgCriteria. The field in
that table is called FloorProgMaxObservations. This one's really got me
stumped because if I use the same code and just put a fixed number in the
code, rather than " & Me.[FloorProgMaxObservations] & " it works fine.

This is the code works correctly but it doesn't meet my needs:

If DCount("*", "tblFloorProgAudit", "[AuditID] = " & Me.[AuditID] & "
And [FloorProgCriteriaID] = " & _
Me.[FloorProgCriteriaID] & " And [AuditorID] = '" & Me.[AuditorID] &
"'") > 5 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

 

[T5925MS via AccessMonster.com]

You're a genius! That was the problem. Now it works perfectly!!! Thanks SO
much!

The result will always be "True" unless there's no record. What is number of
exceeded records? You did not indicate with an "=" operator or whatever. And
why did it work? I'm puzzled. You have a missing parenthesis.

Here's the added parenthesis. "Me.[FloorProgMaxObservations] & ")" Then

Please also correct the code as you also have missing "&".

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

 

[T5925MS via AccessMonster.com]

Thanks to everyone for your input. This problem is now solved.

What is "FormValue" in your code? A function? How does it get its value?
Is PlanTypeID a numeric field? or a text field?

Correction: Removed "Group By" clause from SQL statement, it isn't used

[quoted text clipped - 77 lines]