Using Combo Box Selection to Query a Table

[DougW]

I have a combo box where the user will select an employee from a Employee
table listing the employees with a lookup filed for the supervisors. The
supervisors' names are in a separate table, called Supervisors. What I am
trying to figure out is how, after a user selects the employee in the combo
box, can I use that information to populate an unbound text box with the
appropriate supervisor?

The combo box has two columns, the primary key from the Employee table, and
a name column which combines the first and last name fields so that it
appears as Lastname, Firstname to the user.

Am I making this too difficult? I am having trouble finding a solution to
even try!

Employee Table:
Primary Key, LastName, FirstName, Supervisor (lookup - pk from Supervisors)

Supervisor Table:
Primary Key, SpvsrLast, SpvsrFirst

 

[Douglas J. Steele]

Join the two tables in a query that returns the primary key, the Employee
Name and the Supervisor Name, and use that query as the RowSource for the
combo box. Make sure the combo box is set to have 3 columns. (You can hide
the Supervisor Name from view in the combo box by setting the ColumnWidths
property appropriately).

In the combo box's AfterUpdate event, put code to take the value from the
third column and copy it to the text box:

Private Sub cboEmployee_AfterUpdate

Me.txtSupervisor = Me.cboEmployee.Column(2)

End Sub

(Note that the Column collection starts numbering at 0, so you refer to the
third column as Column(2))

 

[strive4peace]

Hi Doug,

set these properties for your combo:

Name --> EmployeeID

RowSource -->
SELECT
[Employee Table].[Primary Key] as EmpID
, LastName & ", " & FirstName as Employee
, SpvsrLast & ", " & SpvsrFirst as SupervisorName
FROM
[Employee Table]
INNER JOIN [Supervisor Table]
ON [Employee Table].Supervisor
= [Supervisor Table].[Primary Key]

ColumnCount --> 3
ColumnWidths --> 0;1.5;1.5
ListWidth --> 3.2 (sum of column widths + 0.2 for scrollbar)
ColumnHeads --> yes

then, make a textbox control on the form
Name --> SupervisorName
ControlSource --> =EmployeeID.column(2)

the reason the column is 2 instead of 3 is that column indexes start
with 0 (zero)

if 'Employee Table' and 'Supervisor Table' are not your actual table
names, make the appropriate substitutions

Instead of naming a field something ambiguous like 'Primary Key' it is a
good idea to name it something specific like EmployeeID or SupervisorID


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*

 

[DougW]

Thank you both for your help!
--
DougW

Hi Doug,

set these properties for your combo:

Name --> EmployeeID

RowSource -->
SELECT
[Employee Table].[Primary Key] as EmpID
, LastName & ", " & FirstName as Employee
, SpvsrLast & ", " & SpvsrFirst as SupervisorName
FROM
[Employee Table]
INNER JOIN [Supervisor Table]
ON [Employee Table].Supervisor
= [Supervisor Table].[Primary Key]

ColumnCount --> 3
ColumnWidths --> 0;1.5;1.5
ListWidth --> 3.2 (sum of column widths + 0.2 for scrollbar)
ColumnHeads --> yes

then, make a textbox control on the form
Name --> SupervisorName
ControlSource --> =EmployeeID.column(2)

the reason the column is 2 instead of 3 is that column indexes start
with 0 (zero)

if 'Employee Table' and 'Supervisor Table' are not your actual table
names, make the appropriate substitutions

Instead of naming a field something ambiguous like 'Primary Key' it is a
good idea to name it something specific like EmployeeID or SupervisorID


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*

I have a combo box where the user will select an employee from a Employee
table listing the employees with a lookup filed for the supervisors. The
supervisors' names are in a separate table, called Supervisors. What I am
trying to figure out is how, after a user selects the employee in the combo
box, can I use that information to populate an unbound text box with the
appropriate supervisor?

The combo box has two columns, the primary key from the Employee table, and
a name column which combines the first and last name fields so that it
appears as Lastname, Firstname to the user.

Am I making this too difficult? I am having trouble finding a solution to
even try!

Employee Table:
Primary Key, LastName, FirstName, Supervisor (lookup - pk from Supervisors)

Supervisor Table:
Primary Key, SpvsrLast, SpvsrFirst