Determine if an unbound field is in a table

[goeppngr via AccessMonster.com]

I have a form where a user enters a date that is unbound. The form is bound
to my table (Mill Production Data).
The user enters the date each day with the necessary production data,
including the date. Quite often the user enters the wrong date, that is a
date that they have already entered information. I am trying to write code
that will take the entered date (txtHeadDate) and check it against all date
fields (ProdDate) in the table (Mill Production Data). If the date is
already in the table I would want a message box to explain why they cannot
enter that date, and if it is not contained within the table,to proceed as
normal (I have this code in an if then statement).

Here is what I tried on the txtHeadDate lost focus command:

Dim Var1 As Variant
Var1 = DCount(txtHeadDate, "Mill Production Data")
If Var1 = Null Then
Me.Machine.SetFocus
Else
MsgBox "Please enter a valid date", vbOKOnly, "Date entry error"
txtHeadDate.Value = ""
txtHeadDate.SetFocus
End If

The value for Var1 in this case returns the number of records within my table
Mill Production Data.

Where have I gone wrong?

 

[Guest]

Use the Before Update event, not the Lost Focus. By then, it is too late

If Not IsNull(DLookup("[HeadDate]", "[Mill Production Data]",
"[HeadDate] = #" & Me.txtHeadDate & "#")) Then
MsgBox Me.txtHeadDate & " has already been used" & vbNewLine &
"Enter another Date", vbExclamation
Cancel = True
End If

Also when you are wanting to look for a specific value, the DLookup will, on
average, be faster than the DCount. DLookup will stop reading the domain
when it finds a match. The DCount has to read the entire domain every time.

 

[goeppngr via AccessMonster.com]

Thank you very much, worked like a charm.

Use the Before Update event, not the Lost Focus. By then, it is too late

If Not IsNull(DLookup("[HeadDate]", "[Mill Production Data]",
"[HeadDate] = #" & Me.txtHeadDate & "#")) Then
MsgBox Me.txtHeadDate & " has already been used" & vbNewLine &
"Enter another Date", vbExclamation
Cancel = True
End If

Also when you are wanting to look for a specific value, the DLookup will, on
average, be faster than the DCount. DLookup will stop reading the domain
when it finds a match. The DCount has to read the entire domain every time.

I have a form where a user enters a date that is unbound. The form is bound
to my table (Mill Production Data).

[quoted text clipped - 23 lines]

Where have I gone wrong?