Prev: Access - Macro - table not exist
Next: Goto/Goti Issue
From: Michael on 17 Feb 2010 21:05
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

From: Jeanette Cunningham on 18 Feb 2010 03:20
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


"Michael" <Michael(a)discussions.microsoft.com> wrote in message
news:01177127-C85C-4E67-8759-480011176950(a)microsoft.com...
> 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
>


 | 
Pages: 1
Prev: Access - Macro - table not exist
Next: Goto/Goti Issue