Update Label Caption Based On Query Result

[David Layzell]

Good Day,

I have an access database with an employees table. This table has
employee ids, first names and last names. I also have a form which includes
an employee ID field and a label. What I would like to do is use the
AfterUpdate event of the field to query the employees table for the first
and last names associated with the employee ID that has just been entered in
the field and use the results to update the label's caption. Can anyone
advise on how to go about this?

Thanks,

David Layzell

 

[Malcolm Cook]

one hint:

the label of control c is itself a control
it is the first control in c.controls

 

[Allen Browne]

Some possiblities:

1. Use a combo box instead of the text box and label.
The combo can store the EmployeeID, but display the employee name if you set
its properties like this:
RowSource SELECT EmployeeID,
Trim([Surname] & ", " + [FirstName]) AS FullName
FROM Employees ORDER BY Surname, FirstName;
Column Count 2
Column Widths 0

2. Use a text box with Control Source of:
=DLookup("[FirstName] & "" "" & [Surname]", "Employees", "EmployeeID = "
& Nz([EmployeeID],0))

 

[Ken Sheridan]

David:

I think this is a 'road to Dublin' question. Don't use labels; instead use
text boxes. You can format them to look like labels if you wish. You can
then handle it in several alternative ways:

1. If the EmployeeID control is a text box make the ControlSource
properties of the two unbound text boxes:

=DLookup("Firstname", "Employees", "EmployeeID = " & [EmployeeID]
and:
=DLookup("Lastname", "Employees", "EmployeeID = " & [EmployeeID]

2. If the EmployeeID control is a combo box make its RowSource:

SELECT, EmployeeID, FirstName, LastName
FROM Employees
ORDER BY EmployeeID;

Set the combo box's ColumnCount property to 3 and for the ControlSource
properties of the two unbound text boxes use:

=EmployeeID.Column(1)
and:
=EmployeeID.Column(2)

Note that EmployeeID here would be the name of the combo box as well as of
its underlying field. The Column property is zero-based, so Column(1) is the
second column of thecontrol's RowSource.

3. Again if the EmployeeID is a combo box, but this time you want it to
show the FirstName when a selection is made and have the LastName in an
unbound text box. Set the combo box's RowSource to:

SELECT, EmployeeID, FirstName, LastName
FROM Employees
ORDER BY LastName, FirstName;

and its ColumnCount propertiy to 3 as above, but this time set its
ColumnWidths property to something like 0cm;3cm;3cm (experiment to get the
best fit, but the first dimension must be zero) and its ListWidth property to
6cm (i.e. the sum of the column widths). For the unbound text box to show
the LastName use a ControlSource of:

=EmployeeID.Column(2)

This last approach is the usual one in this sort of situation as the
EmployeeID is generally just a unique identifier with arbitrary values of no
intrinsic meaning, so does not need to be exposed to the user. To select an
employee the user simply selects a name from the list, which is sorted
alphabetically by name. The value of the control will be the unseen
EmployeeID, however,

4. Base the form on a query which joins its table to the Employees table on
the EmployeeID fields.

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

Oops! Missed the closing parentheses:

=DLookup("Firstname", "Employees", "EmployeeID = " & [EmployeeID])
and:
=DLookup("Lastname", "Employees", "EmployeeID = " & [EmployeeID])

 

[David Layzell]

Thanks to everyone who has offered suggestions. I've found the answer I was
looking for in a different newsgroup.