coding issue

[pfm721]

Form frmInfo with a subform frmAppointment. I want to check to see that all appointments have a complete date before I allow the user to check complete on a check box on the main form. My code (which was written by someone else) is as follows. I am getting error 2001 you canceled the previous operation. I am unable to figure out why. The references are all correct. If anyone can see whats wrong and help me with it, it would be greatly appreciated.

Private Sub Complete_BeforeUpdate(Cancel As Integer)
Dim varIncompleteProject As Variant
   varIncompleteProject = (DLookup("[AppID]", "tblAppointment", _
   "[ConsultNumber]= """ & Me.ConsultNumber & _
   """ And[CompletedDate] Is Null"))
 If Not IsNull(varIncompleteProject) Then
    MsgBox "Appointment " & varIncompleteProject & " Is Not Competed"
    Cancel = True
 End If

End Sub

[RuralGuy]

Try:

varIncompleteProject = DLookup("[AppID]", "tblAppointment", _
"[ConsultNumber]= '" & Me.ConsultNumber & _
"' And IsNull([CompletedDate])")

...If [ConsultNumber] is a Text field.

[pfm721]

Almost there I think, no I am getting a syntax error. The code now reads

Dim varIncompleteProject As Variant
    varIncompleteProject = DLookup("[AppID]", "tblAppointment", _
    "[ConsultNumber]= " & Me.ConsultNumber & _
    "' And IsNull[CompletedDate]")
  If Not IsNull(varIncompleteProject) Then
    MsgBox "Appointment " & varIncompleteProject & " Is Not Competed"
    Cancel = True
  End If

Now the error is 3075
Syntax error in strintg in query expression '[ConsultNumer]= 1234567'
And IsNull[CompletedDate]'.

with 1234567 being the consult number and yes consult number is a text field.

Thanks

[Stewart Ross]

Hi. You are so nearly correct now. When referring to string literals, use single quotes to enclose the string value:...

"[ConsultNumber]= '" & Me.ConsultNumber & "'" _

-Stewart

[NeoPa]

Check out Quotes (') and Double-Quotes (") - Where and When to use them.