If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using Combo Box Selection to Query a Table
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 -- Doug |
#2
|
|||
|
|||
Using Combo Box Selection to Query a Table
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)) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "DougW" wrote in message ... 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 -- Doug |
#3
|
|||
|
|||
Using Combo Box Selection to Query a Table
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 wrote: 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 |
#4
|
|||
|
|||
Using Combo Box Selection to Query a Table
Thank you both for your help!
-- DougW "strive4peace" wrote: 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 wrote: 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 |
Thread Tools | |
Display Modes | |
|
|