That looks like you have mixed up the combo box's display
with its value. The value is the ID field so, presuming
that the EmpName field contains an emplayee name, you have
the combo box bound to the wrong field. It should be bound
to an employee id field that acts as a foreign key to the
EMP table. You need to fix that so the fields agree with
what is in the tables.
Once that is taken care of, the DLookup would look like:
=DLookUp("[Manager]","[EMP]","ID = " & thecombobox)
Oresumable that will return the manager's ID field. If you
were expecting it to return the manager's name, you wii need
to use another DLookup to get the name from the managers
record.
--
Marsh
MVP [MS Access]
Row Source Type: Table/Query
Row Source: SELECT EMP.ID, EMP.[Full Name] FROM EMP ORDER BY [Full Name];
ControlSource: EmpName
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0";1"
The row source for the employee name is currently the EMP table: the fields
in the table are:
ID, Lname, Fname, Full Name, SAPEmp, AdjServiceDate, Wave, UserIDEmp, CMSId,
Schedule, Dept, Manager
Marshall Barton said:
It is common for combo boxes to be bound to some kind of ID
field while they display some other field (e.g. a name).
Post back with a Copy/Paste of the combo box's row source
query's SQL view. If the combo box's row source is a table,
then post a list of the table's fields.
Also provide the combo box's ControlSource, BoundColumn,
ColumnCount and ColumnWidths properties.
TheDrescher wrote:
It should match exactly. The EmpName field in the form is a combo box. The
list of values in this combo box is pulled from the Full Name field in the
table.
:
DLookup returns a null when the criteria expression fails to
find any records. Are you certain that the EmpName field on
the form exactly matches the Full Name field in the EmpName
field in the table?
TheDrescher wrote:
Thanks for the quick response! Entering the code in this fashion keeps
returning a blank field. If I could I would change the 'Full Name' field in
the table to remove the space in the name, but it is a linked table in a
database I do not have access to edit. Is there anything else you think it
might be? I've tried several different ways to write the same formula, and
the result is always either blank, or the first name in the Manager table for
all values...
:
TheDrescher wrote:
Hey all, I'm working on an access database and on the MainPage form I'm
looking to populate data using DLookup. I'm trying to populate a text box
with data from the Manager column of the EMP table where the Full Name field
in the table matches the EmpName selection on the current form. So far I'm
only getting either a blank field or the first field in the table for
everyone. The code I'm using is:
=DLookUp("[Manager]","[EMP]","[Full Name] = [EmpName] ")
If EmpName is a field on the form that you want to match,
then try something more like:
=DLookUp("Manager","EMP","[Full Name]=""" & [EmpName] &
"""")
.
