Select Empname and auto-pop w/Supe

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using Access 2003. I have a form for employee quality stats. Whatever I
type in the form goes into a quality table. In the form, EmpName has a drop
down list of all of the employees in the company and you have to select one
of them. The list of their names comes from another table. What I am hoping
to have happen is that you select the employee name and it automatically
populates that person's supervisor.

Now, the table with employee names only has the supervisor's last name. That
is okay if my next request is not possible. What we'd prefer is to instead
use a table that has the supe's last names as they appear in that table, plus
their full names. This way, we can link it to the employee table by their
last name, but use their full names instead. Can this be done, and if so how?
If this cannot be done, can it be done using the same table, but we'll just
get stuck having only the last name of the supes? This is fine if it is the
only way to get this done. I know that you may say that this shouldn't be
necessary to duplicate all this data, but in our case we have to. We need
this table to have the employee name and their supervisor's name.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''
ptaylor
 
The rowsource for your combo box in which you want to display the
supervisor's name should be something like:

SELECT Employee.Supervisor, Supervisor.FullName
FROM Employee INNER JOIN Supervisor on Employee.Supervisor =
Supervisor.FullName

Under the format for this combo box, give it a column count of 2. Make the
width of the first column 0 and the 2nd column 1.5" (or whatever works for
you to see the supervisor's full name).

Hope that helps.

Linda
 
Okay, thank you. However, how do I then get it to auto-populate the
supervisor field when I've chosen an employee. For example, if Harvet Dent's
supervisor is Bruce Wayne, when I choose Harvey Dent on the form, I want it
to automatically choose Bruce Wayne as his supervisor without me having to do
so.
--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

~~~~~~
| |
|c--OD
| _)
| |
|-. |
/ `-# /A
/ /_|..`#.J/
||LJ `m''
ptaylor
 
Make an OnUpdate event for your combo box and include the following code:

Me.SupervisorComboBox.Requery

Linda
 
Linda is suggesting that you have a dropdown for the supervisor name
because of the simple fact that if you have three supervisors with the
same last name you will have no way of determining which of them is the
one to use (programmatically).

With the information you have supplied, there is no way to accurately
guess who is the correct supervisor.

If you have the constraint that you never have two supervisors with the
same last name then you can do the following:

Change the employee query to include a link by supervisor last name to
the other supervisor table similar to what linda is showing, then
return employee name and supervisor full name in the query. then on the
afterupdate event of the employee name combo put
me.supername = me.empcombo.column(1)

Be sure to have the link say ALL employess and only those supervisors
that match, just on the outside chance that the employee table has the
supervisor last name mis-spelled. Otherwise not all employees will show
up in the query.

The column count starts at 0. column 0 is the employee name and
column 1 is the supervisor full name.
 
Back
Top