By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,606 Members | 1,628 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,606 IT Pros & Developers. It's quick & easy.

Duplicate entry warning and display data

P: 547
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub RaceNumber_BeforeUpdate(Cancel As Integer)
  2. Dim Answer As Variant
  3.  Answer = DLookup("[RaceNumber]", "RacetimingT", "[Racenumber] = " & Me.RaceNumber & " AND [RaceDate]= #" & Me.Racedate & "#")
  4. If Not IsNull(Answer) Then
  5.  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"
  6. Cancel = True
  7. 'Me.Undo
  8. End If
  9. 'End Sub
Nov 16 '11 #1
Share this Question
Share on Google+
2 Replies

P: 547
I got it working with this code after 4 hours of sweating
Expand|Select|Wrap|Line Numbers
  1. Private Function IsDuplicateRecord() As Boolean
  3.     On Error Resume Next
  4.     Dim PreviousRecordID As Long
  5.     IsDuplicateRecord = False
  7.     PreviousRecordID = 0
  8.     PreviousRecordID = DLookup("RacetimingId", "RacetimingT", "RacetimingId<>" & racetimingId & _
  9.     " AND RaceNumber=" & RaceNumber & " AND [RaceDate]= #" & Racedate & "#")
  10.     If PreviousRecordID <> 0 Then
  11.         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"
  12.         IsDuplicateRecord = True
  13.     End If
  15. End Function
Expand|Select|Wrap|Line Numbers
  1. On Error GoTo Form_BeforeUpdate_Err
  2. If IsDuplicateRecord Then Cancel = False
  3. Form_BeforeUpdate_Exit:
  4.     Exit Sub
  6. Form_BeforeUpdate_Err:
  7.     MsgBox Error$
  8.     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?
Nov 16 '11 #2

Expert Mod 15k+
P: 31,754
If RaceDate is a unique ID then why do you use the following in your DLookup() code? :
Expand|Select|Wrap|Line Numbers
  1. "[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.
Nov 17 '11 #3

Post your reply

Sign in to post your reply or Sign up for a free account.