Hello:

Greetings to all.

I am puzzle with an inconsistant result from dlookup, warning of “Duplicated
Value” in a form control “before_update” property. I am creating data entry
forms for different work areas. The sub worked for one area but not the
other.

The one based on a combined fields of  Date and Period (Time) worked, as
written below. The domain is queryDatePeriod and the current form is
formarea_1.

Private Sub Period_BeforeUpdate(Cancel As Integer)
 
On Error GoTo Err_Exit_this_sub

Dim x As Variant

   x = DLookup("[date]&[period]", "[ queryDatePeriod]", "[date]&[period]=
'" & Forms! _
[ formarea_1]![Date] & [Period] & "'")
 
  If Not IsNull(x) Then
     Beep
     MsgBox "That value already exists", vbOKOnly, "Duplicate Value"
     Cancel = True
  End If

Exit_this_sub:
   Exit Sub

Err_Exit_this_sub:
   MsgBox Error$
   Resume Exit_this_sub
   
End Sub

However, it DID NOT work when I used the "same" sub (Private Sub
Date_BeforeUpdate(Cancel As Integer) with different “x” expression for
another area. The expression is as stated below. It used a combined fields of
Shift and Date. The domain is queryShiftDate and the current form is now
formarea_2.

x = DLookup("[shift]&[date]", "[ queryShiftDate]", "[shift]&[date]= '" & _
Forms![ formarea _2]![Shift] & [Date] & "'")

I multiple checked the table and form properties. Between the tables and
forms, they have the same property types with the fields and controls in
concern.

Yet, the Warning “Duplicate Value” did not shown in the second case as the
control loses its focus. And per debug window in Access VBE, this fouled
expression is “out of context”.

What is “out of context”?

I substituted the dllokup criteria with a real value from queryShiftDate as
shift one & date (116/03/2005), the debug return “null”.

Why??? It is there. But dlookup did not detect it. Sleepless nights.

Thank you in advance for any suggestion. I tried with Access 97 and 2000.

James