Code to determine if record exists in file

[Guest]

I have a table indexed (unique) on date. My users enter a date on a form. I
need to determine if that date exists in my table before proceding.

Can someone help? Thanks in advance.

 

[Allen Browne]

Use DLookup to see if the value is in the table. For an explanation, see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

This example assumes the field is named Date1, and comes from Table1, and
your text box is also named Date1. It checks to make sure that the field has
a value and the user has not changed it to the same value (so an existing
record does not find itself in the table.) The formatting of the date makes
sure it works regardless of the user's settings, and adds the # delimiters
around the literal date in the string.

If you want to prevent the entry, use the control's BeforeUpdate event
instead of AfterUpdate.

Private Sub Date1_AfterUpdate()
Dim strWhere As String
Dim varResult As Variant

If IsNull(Me.Date1) Or (Me.Date1 = Me.Date1.OldValue) Then
'do nothing
Else
strWhere = "Date1 = " & Format(Me.Date1, "\#mm\/dd\/yyyy\#")
varResult = DLookup("Date1", "Table1", strWhere)
If Not IsNull(varResult) Then
MsgBox "Duplicate date."
End If
End If
End Sub