dlookup help

[gsantacruz]

Hello!

Please bear with me i'm a total rookie.

I am trying to prevent operators from filling out my "inspections log" form before they have entered the hours the are working that day in the "inspectors' hours" table through a form also called "inspectors' hours".

in order to do so, i am using a dlookup function on the before update event in my "hours" field in the "inspections log" form that looks up if there are any hours entered for that day for that operator in the "inspectors' hours" table. This is what I got:

Private Sub Inspector_Name_BeforeUpdate(Cancel As Integer)
Dim icount As Long
icount = Nz(DLookup("Hours", "Inspectors' Hours", "Inspector=" & [Inspector] & " And Date= #" & [Date] & "#"), 0)
If icount = 0 Then
MsgBox "Please enter the hours you are working today before filling the inspections log."
Cancel = True
Undo


End If
End Sub

I am having all kinds of trouble with it. I am getting the "Compile Error: External name not defined" error message when I run the nz(dlookup) function, but it works fine when i take out the date part...

any ideas?

Thank you!

[RuralGuy]

Try putting brackets around [Date]. It is a reserved word and should not be used for a field name. Is [Inspector] a numeric field?

[gsantacruz]

hello RuralGuy, and thank you for your response.

I have tried the brackets, still nothing!

Inspectors is a text field...

i am basically trying to get the dlookup function to check if there is an entry for hours in the inspectors' hours table, for which it needs to look for the name and date entered in the form. i think i am closer with this, but it still doesnt work:

Private Sub FF_Change()
Dim icount As Long
icount = Nz(DLookup("Hours", "Inspectors' Hours", "[Inspector] = " & Forms![Inspections Log]![Inspector_Name] & " And [Date] = #" & Forms![Inspections Log]![Date] & "#"), 0)
If icount = 0 Then
MsgBox "Please enter the hours you are working today before filling the inspections log."
Cancel = True
Undo
End If
End Sub

[weekend00]

I think RuralGuy is busy on something.
would you try following before RuralGuy comes back and gives a good advise?

icount = Nz(DLookup("Hours", "[Inspectors' Hours]", "[Inspector] = '" & Forms![Inspections Log]![Inspector_Name] & "' And [Date] = #" & Forms![Inspections Log]![Date] & "#"), 0)

[RuralGuy]

It looks like Weekend00 gave some pretty good advice. Let us know how it works.

[gsantacruz]

Thank you, It works perfectly!!!

[RuralGuy]

That's great! Are you ready to follow the link in my sig and mark this thread as Solved?

[ajetrumpet]

That's great! Are you ready to follow the link in my sig and mark this thread as Solved?

Hey, do you have this line store in the database somewhere and just call it with a script whenever something gets solved? :laugh: This post is a national pastime with you.

[RuralGuy]

Adam: Would you believe I type it each and every time?

[ajetrumpet]

Adam: Would you believe I type it each and every time?

You bet I would!