DCount

[Chris]

Hi All, I am trying to use the following code to stop duplicate entries
being entered into my table.

If DCount("*", "tblStaffEvidence", "EvidenceID=" & intEvidence & _
" And StaffID =" & intStaffID & _
" And ColTerm =" & intTerm) > 0 Then
MsgBox "This member of staff has already been assigned
this evidence," & vbCrLf & _
"Please assign to someone else .", vbInformation
end IF

The message box is appearing whenever the staffID is in the table but I only
want the message box to appear when the 3 values are found to be true. Any
Idea's?

tblStaffEvidence
StaffEvidenceID (pk) (int)
staffID (fk)
EvidenceID (fk)
ColTerm (fk)

 

[Wayne Morgan]

Right off-hand, I don't see anything wrong with the statement. Have you
tried stepping through the code and checking the values of the variables to
make sure you're passing what you think you're passing?

 

[Chris]

Yes! the variables are set to the correct values and these values are not
in my table but the msgbox still appears

 

[Duane Hookom]

Try create a SQL expression that you can paste into a blank query. Pressing
Ctrl+G following the code (or add a break point in the code) allows access
to the SQL. See if the query results are what you expected.
Dim strWhere as String
Dim strSQL as String
strWhere = "EvidenceID=" & intEvidence & _
" And StaffID =" & intStaffID & _
" And ColTerm =" & intTerm
strSQL = "SELECT Count(*) FROM tblStavvEvidence " & strWhere
debug.Print strSQL
If DCount("*", "tblStaffEvidence", strWhere) > 0 Then
MsgBox "This member of staff has already " & _
"been assigned this evidence," & vbCrLf & _
"Please assign to someone else .", vbInformation
End If

 

[Wayne Morgan]

The word "WHERE" is missing in the example. It will need to be there before
copying and pasting the SQL into a blank query.

Change:

strSQL = "SELECT Count(*) FROM tblStavvEvidence " & strWhere

to:
strSQL = "SELECT Count(*) FROM tblStavvEvidence WHERE" & strWhere

 

[Duane Hookom]

Thanks Wayne...

--
Duane Hookom
MS Access MVP

The word "WHERE" is missing in the example. It will need to be there
before copying and pasting the SQL into a blank query.

Change:
to:
strSQL = "SELECT Count(*) FROM tblStavvEvidence WHERE" & strWhere

 

[Wayne Morgan]

And I left a space out of it, let's try again:

strSQL = "SELECT Count(*) FROM tblStavvEvidence WHERE " & strWhere