Prevent duplicate entries in a form in Access 2007

[neelsfer]

Hi
This code worked until i changed the "RaceNo" file to text from number format.
What brackets or code should i change to make it work?
This is to prevent duplicate entries.
thx for helping
Neels

Private Function IsDuplicateRecord() As Boolean
 
    On Error Resume Next
    Dim PreviousRecordID As Long
    IsDuplicateRecord = False
 
    PreviousRecordID = 0
    PreviousRecordID = DLookup("RacetimingID", "RacetimingT", "RacetimingID<>" & RacetimingID & _
    " AND RaceNo=" & RaceNo & " AND [RaceDate]= #" & RaceDate & "#")
    If PreviousRecordID <> 0 Then
        MsgBox "You have entered this item twice"
        IsDuplicateRecord = True
    End If
 
End Function
 
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
 
    If IsDuplicateRecord Then Cancel = 1
 
End Sub
 

[MMcCarthy]

RaceNo needs to be enclosed in quotes as it is text. Try this ...

PreviousRecordID = DLookup("RacetimingID", "RacetimingT", "RacetimingID<>" & RacetimingID & _
    " AND RaceNo='" & RaceNo & "' AND [RaceDate]= #" & RaceDate & "#")

Also I assume RaceNo and RaceDate are variables based on your code. If they are control names and not variables you should add a form object reference such as Me! (e.g.) Me!RaceNo

[neelsfer]

thx for help

[NeoPa]

For the full info on what to use and where see Quotes (') and Double-Quotes (") - Where and When to use them. It even links to another article that deals with how correctly to handle dates in SQL. Probably the area where most mistakes are made.

[neelsfer]

thx for the info