Combobox control source - DLookup function

[Alan T]

I have a form link to a table: tblEmployee

Fields of tblEmployee:
DepartmentNumber
EmployeeName
Address
Phone
Position

I have another table tblDepartment:
Fields:
DepartmentNumber
DepartmentName
DepartmentLevel
DepartmentManger

I have a combobox to list the Departments.
So Combobox properties:
ControlSource: DepartmentNumber <-- is this the field of
tblEmployee.DepartmentNumber ??
Row Source Type: Table/Query
Row Source: SELECT tblDepartment.DepartmentNumber,
tblDepartment.DepartmentName,
tblDepartment.DepartmentLevel,
tblDepartment.DepartmentManager
FROM tblDepartment

I have 3 text boxes, what I want to do is whenever I choose the department
in the combobox I want to populate the
3 text boxes with:
tblDepartment.DepartmentName
tblDepartment.DepartmentLevel
tblDepartment.DepartmentManager

I tried to use the function but no luck.
=DLookup(...)

Thanks

 

[Steve]

Not relevant to your question but very important to setting up your database
correctly, you need a field, EmployeeID, in TblEmployee.

To answer your question ---
Yes, the control source of the combobox needs to be Departnumber in
TblEmployee. Via the combobox you enter the department number for your
employee records. To set up your combobox, create a query based on
TblDepartment and include these fields in the order shown: DepartNumber,
DepartName, DepartmentLevel, DepartmentManager. Set sort ascending for
DepartNumber. Name your combobox DepartmentNumber. For your combobox, open
properties and go to the Data tab. Set the rowsource to the name of the
query and set the bound column to 1. Go to the Format tab. Set Column Count
to 4 and Column Width to !.5;0;0;0. Name your three textboxes
DepartmentName, DepartmentLevel and DepartmentManager. Put the following
code in the AfterUpdate event of the textboxes:
Me!DepartmentName = Me!DepartmentNumber.Column(1)
Me!DepartmentLevel = Me!DepartmentNumber.Column(2)
Me!DepartmentManager = Me!DepartmentNumber.Column(3)

Your combobox will display a list of department numbers in ascending order.
When you select a department number, the three textboxes will be
automatically filled-in.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Alan T]

Hi it works.
Thanks.

BTW, does it handles it some of the columns are blank or NULL?

 

[Steve]

Yes!

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)