Error 13 Type Mismatch

[T5925MS via AccessMonster.com]

Need help solving a type mismatch error on the following code:
Many thanks!

Private Sub Form_BeforeUpdate(Cancel As Integer)
'Limits the number of records allowed
'based on the field FloorProgMaxObservations
If DCount("*", "tblFloorProgAudit", "[AuditID] = _
" & Me.AuditID.Value & "" And _
"[FloorProgCriteriaID] = " & _
Me.FloorProgCriteriaID.Value & "" And _
"[AuditorID] = '" & Me.AuditorID.Value & "'") = _
Me.FloorProgMaxObservations.Value 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 Sub

 

[Jeanette Cunningham]

The part of the DCount starting at "[AuditID] and finishing at
AuditorID.Value & "'") , needs to be rewritten, more like this

"[AuditID] = " & Me.AuditID & " " _
& "AND [FloorProgCriteriaID] = " & Me.FloorProgCriteriaID & " " _
& "AND [AuditorID] = """ & Me.AuditorID & """"

The above assumes that AuditID and FloorprogCriteriaID are both numbers and
AuditorID is text.

If you have trouble deciphering the quotes, copy and past it into Notepad to
see them more clearly.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[T5925MS via AccessMonster.com]

You solved the problem...Thanks so much!

The part of the DCount starting at "[AuditID] and finishing at
AuditorID.Value & "'") , needs to be rewritten, more like this

"[AuditID] = " & Me.AuditID & " " _
& "AND [FloorProgCriteriaID] = " & Me.FloorProgCriteriaID & " " _
& "AND [AuditorID] = """ & Me.AuditorID & """"

The above assumes that AuditID and FloorprogCriteriaID are both numbers and
AuditorID is text.

If you have trouble deciphering the quotes, copy and past it into Notepad to
see them more clearly.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Need help solving a type mismatch error on the following code:
Many thanks!

[quoted text clipped - 14 lines]

End If
End Sub