DLookup or Code

[JudyB]

Once again I find myself scratching my head. I thought I could use the
following code to automatically return the value of a specific field from my
query but, I am having problems. After reading up on DLookup and it's
problems...I'm asking myself if I need to do something else. My Code:

=DLookUp("[DepartmentName]","Service Record Query","[EmployeeID] =" &
[Forms]![Service Record Query]![EmployeeID] & " and [DateEnd] is Null")

I continue to get #Name? error message. I put the code in an unbound text
box on the main form in hopes of automatically returning the name of an
employee's current department. Can anyone tell me how to correct the code or
advise me on another method of accomplishing my goal? Thanks in advance for
any help.

 

[Ken Snell \(MVP\)]

Your DLookup function appears to have acceptable syntax, assuming that the
names of fields and query and form and control are correct.

What is the name of the textbox that has this expression as its Control
Source?

Is EmployeeID field in the query numeric or text data type?

 

[Michael Gramelspacher]

Once again I find myself scratching my head. I thought I could use the
following code to automatically return the value of a specific field from my
query but, I am having problems. After reading up on DLookup and it's
problems...I'm asking myself if I need to do something else. My Code:

=DLookUp("[DepartmentName]","Service Record Query","[EmployeeID] =" &
[Forms]![Service Record Query]![EmployeeID] & " and [DateEnd] is Null")

I continue to get #Name? error message. I put the code in an unbound text
box on the main form in hopes of automatically returning the name of an
employee's current department. Can anyone tell me how to correct the code or
advise me on another method of accomplishing my goal? Thanks in advance for
any help.

Try inserting brackets like so: "[Service Record Query]"

Or just do it in code in the main form's Current event

something like this will get the months in an employee's current job and
department name. This assumes you have a fields named time_in_dept and dept_name
on the main form. Or just edit this and change the names to your names.

Private Sub Form_Current()

Dim s As String

s = "SELECT WeeksService,DeptName" & _
" FROM [Service Record Query] WHERE EmployeeId = " _
& Nz(Me.employee_id, 0) & " AND EndDate IS NULL;"

If Not Me.NewRecord Then
Me.time_in_dept = CurrentDb.OpenRecordset(s).Fields(0)
Me.dept_name = CurrentDb.OpenRecordset(s).Fields(1)
Else
Me.time_in_dept = Null
Me.dept_name = Null
End If

End Sub