MS Access help

[MarkB]

I am attempting to build a little routing to prevent the entry of duplicate event records in my database.

Here is the code:
'------------------------------------------------------




Private Sub Form_BeforeUpdate(Cancel As Integer)

If Not (IsNull(DLookup("[From date]", "Events", "[Venue] = '" & [Forms]![Event entry]![Venue] & "'" & _

" and [From date] >= #" & [Forms]![Event entry]![From date] & "#" & _



" and [From date] <= #" & [Forms]![Event entry]![Thru date] & "#")) _



​

And IsNull(DLookup("[From date]", "Events", "[Venue] = '" & [Forms]![Event entry]![Venue] & "'" & _

" and [Thru date] >= #" & [Forms]![Event entry]![From date] & "#" & _



" and [Thru date] <= #" & [Forms]![Event entry]![Thru date] & "#"))) Then



​

MsgBox "The current event overlaps an existing event at this venue." & vbCrLf & vbCrLf & _

"This record will be undone.", vbCritical + vbOKOnly, "Event overlap!"



​

Cancel = True




DoCmd.RunCommand acCmdUndo
End If


​

End Sub


'------------------------------------------------------------------------
As you can see from the 'Sub' it is to run prior to record update.











When it runs i get an error:

Run-time error '3464': Datatype mismatch in criteria expression.










​

The Venue field is stored as a foreign key and is a combo box bound to the foreign key. The dates are just that, dates.






I think it is due to the dates, but am not sure.

Any help would be greatly appreciated.

TIA

 

[O-Ace]

Are you making sure the entered date is a valid date/time?

 

[MarkB]

Are you making sure the entered date is a valid date/time?

The entered date is bound to a DB date/time field and the form before update routine is run using this field and the date field from the underlying DB. Access won't let you exit the date field unless the entered date is valid.

 

[Ojas]

Mark, I threw a database together that contains a similar table structure and form code, but could not reproduce the 3464 error. I suggest adding some Debug.Prints in there, then check your Immediate Window (Ctrl+G) in the VBA designer to help figure out the issue. You can also set a breakpoint, debug, then step through the code.

Unrelated to this error... I’m not sure your form allows updates as well as additions, but if the same form and code is used for updates, the validation check may prevent you from updating records in certain scenarios.

You might want to try something like this (replace my EventIDs, with the name of the Event table’s primary key):

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim sSqlCriteria As String
    Dim bDateOverlap As Boolean
    
    sSqlCriteria = _
        "[Venue] = '" & [Forms]![Event entry]![Venue] & "'" & _
        " AND " & _
        " (" & _
        " ([From date] BETWEEN #" & [Forms]![Event entry]![From date] & "# AND #" & [Forms]![Event entry]![Thru date] & "#)" & _
        " OR" & _
        " ([Thru date] BETWEEN #" & [Forms]![Event entry]![From date] & "# AND #" & [Forms]![Event entry]![Thru date] & "#)" & _
        " )" & _
        " AND [color=red]EventID[/color] <> " & [Forms]![Event entry]![[color=red]EventID[/color]]
    Debug.Print ("Query: SELECT * FROM Events WHERE " & sSqlCriteria)
    
    bDateOverlap = Not IsNull(DLookup("0", "Events", sSqlCriteria))
    Debug.Print ("Date Overlap: " & bDateOverlap)
    
    If (bDateOverlap) Then
        MsgBox "The current event overlaps an existing event at this venue." & vbCrLf & vbCrLf & _
            "This record will be undone.", vbCritical + vbOKOnly, "Event overlap!"
        Cancel = True
        DoCmd.RunCommand acCmdUndo
    End If
End Sub

You will see output like this in the Immediate debug window:

Query: SELECT * FROM Events WHERE [Venue] = 'MSR' AND  ( ([From date] BETWEEN #3/3/2002# AND #3/5/2003#) OR ([Thru date] BETWEEN #3/3/2002# AND #3/5/2003#) ) AND EventID <> 17
Date Overlap: False
Query: SELECT * FROM Events WHERE [Venue] = 'MSR' AND  ( ([From date] BETWEEN #3/4/2003# AND #3/6/2003#) OR ([Thru date] BETWEEN #3/4/2003# AND #3/6/2003#) ) AND EventID <> 19
Date Overlap: True

 

[MarkB]

Thanks Ojas! I'll give that a try.