Duplicate entry warning and display data

[neelsfer]

I have a problem with duplicate entries.
I can't get this code to work.
I have one other request.

When the message box appears, i would like the "racefinishtime" record in this same table to be displayed for this same "racetimingid" and equivalent "racenumber" previously added.
The Racedate is a unique field.
The addition of this duplicate record must not be blocked. I only want a warning messagebox to appear pls.
Is it Possible? Pls assist

Private Sub RaceNumber_BeforeUpdate(Cancel As Integer)
Dim Answer As Variant
 Answer = DLookup("[RaceNumber]", "RacetimingT", "[Racenumber] = " & Me.RaceNumber & " AND [RaceDate]= #" & Me.Racedate & "#")
If Not IsNull(Answer) Then
 MsgBox "You have entered a duplicate record for (this will appear now for this duplicate entry) Racenumber = 123 with  Racefinishtime = 16/11/2011 03:09:31 PM. " & vbCrLf & "Please press enter to continue and add the record and fix error afterwards.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
Cancel = True
'Me.Undo
End If
'End Sub

[neelsfer]

I got it working with this code after 4 hours of sweating

Private Function IsDuplicateRecord() As Boolean
 
    On Error Resume Next
    Dim PreviousRecordID As Long
    IsDuplicateRecord = False
 
    PreviousRecordID = 0
    PreviousRecordID = DLookup("RacetimingId", "RacetimingT", "RacetimingId<>" & racetimingId & _
    " AND RaceNumber=" & RaceNumber & " AND [RaceDate]= #" & Racedate & "#")
    If PreviousRecordID <> 0 Then
        MsgBox "You have entered this Racenumber twice. Please press ENTER to continue and make a note of this Race Number and finishtime to correct Race entry error later"
        IsDuplicateRecord = True
    End If
 
End Function
 

On Error GoTo Form_BeforeUpdate_Err
If IsDuplicateRecord Then Cancel = False
Form_BeforeUpdate_Exit:
    Exit Sub
 
Form_BeforeUpdate_Err:
    MsgBox Error$
    Resume Form_BeforeUpdate_Exit

i would still like to have the original Racenumber and it's RaceFinishtime displayed in this duplicate record messagebox
Any suggestions pls?

[NeoPa]

If RaceDate is a unique ID then why do you use the following in your DLookup() code? :

"[Racenumber] = " & Me.RaceNumber & " AND [RaceDate]= #" & Me.Racedate & "#"

NB. Above SQL code is not appropriate for date checking. Americans get away with it if they don't have to export their databases but European dates will frequently fail with that code. See Literal DateTimes and Their Delimiters (#) for how it should be done properly.

DLookup() is fine for getting single field values but when it comes to accessing a whole record of data it starts to come unstuck. It's possible to set a criteria string and call DLookup() a number of times, but really you should be thinking of DAO (unless using ODBC or other non Access/Jet links then use ADODB) Recordsets instead.

What you want is perfectly possible, but the next step is yours.