Check If Record Exists before adding new Record

[Davepacey]

I have searched for and found several examples, but none that help me.
My Base table (tbl_base) has a Date/Time field (dd/mm/yyyy) named "Date" My data entry form is based on tbl_base with various subforms linked. When a user enters a new record, I need it to check if one exists for that date using the following code on Before Update Property:-

If DCount("Date", "tbl_base", "Date='" & Me.Date & "'") Then
MsgBox "A Record For This Date Exists" & _
vbCrLf & " Please Add Any Additional Information On The Browse Form. ", _
vbOKOnly, "Duplicate Entry Warning"
Cancel = True
Me.Undo
Exit Sub
Else End If

I get a data type mismatch error.......Why ?

Grateful for any help.

Dave

 

[dcx693]

Try If DCount("Date", "tbl_base", "Date=" & Me.Date) Then .

I think that should work, but if it doesn't you can try: If DCount("Date", "tbl_base", "Date=#" & Me.Date & "#") Then .

You surrounded your date field with single apostrohe characters. That's done with text fields. But if your date/time field is a real date field, you don't need them. The # character is used to delineate date and time values.

 

[Davepacey]

Hi,
Both your examples let me exit the field with a duplicate value, only when I try move to the subform does access generate a duplicate record warning (field is indexed no duplicates)

Dave