UtterAccess Discussion Forums > What’s The Best Way To Handle Duplicate Record Entries?

Full Version: What’s The Best Way To Handle Duplicate Record Entries?

UtterAccess Discussion Forums > Microsoft® Access > Access Forms

martinr

Jan 23 2012, 11:37 PM

We’re using a single table to record daily data – the date field is set as the
pk so only one record per date is allowed.
If the user enters a date (when creating a new record) that
already exists in the table what is the best way to bypass the
Access error message about duplicate values and allow the
user to enter a new date?
(So instead the of getting the error message the user is simply
prompted to re-enter the date)

Doug Steele

Jan 24 2012, 06:59 AM

Put logic in the form's BeforeUpdate event to check whether the date already exists in the table (use DLookup, for example)

CODE

Private Sub Form_BeforeUpdate(Cancel As Integer)

  If Not IsNull(DLookup("MyDateField", "MyTable", "MyDateField = " & Format(Me!txtMyDateField, "\#yyyy\-mm\-dd\#"))) Then
    MsgBox Me!txtMyDateField & " has already been input." & vbCrLf & "Try again."
    Me!txtMyDateField.SetFocus
    Cancel = True
  End If

End Sub

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.