Another form question - combo box

[Guest]

I have a question regarding a combo box that I have created. It has the tite,
first, and last name of a person showing. After selecting a person from the
drop down, only the word "deputy" appears in the box. How do i make it so a
combination of their title and last name displays, i.e "Deputy Johnson"

 

[Ken Snell [MVP]]

A combo box will display multiple columns in the dropdown list, but only
displays the first visible column in the box when the list closes.

You need to add a calculated field to the query that serves as the combo
box's Row Source. That calculated field concatenates the names into the
string that you want. Then change the order of the fields in the query so
that the calculated field is the first visible column for the combo box.

Alternatively, you can have a textbox next to the combo box display the
value from a column in the combo box. See The ACCESS Web for one way to do
this:
http://www.mvps.org/access/forms/frm0058.htm

 

[Douglas J. Steele]

A combobox can only show one field. One option you have is to create a query
that concatenates the three fields into one for display purposes.

 

[Guest]

OK, so i have title, firstname, lastname, and chairsID all as values that
need to be inserted into a table, but i only have title, firstname, and
lastname displaying.

Since i need all four of these inserted into the table, wont creating a
calculation in a query to show all three affect this?

 

[Guest]

I just noticed that my code does not insert anything into the table.

I want to grab the title, firstname, lastnight, and chairsID from an
employees table. The data on the form is to be inserted into a WatchResults
table. Here are the questions I have that should solve my problems:

1) The WatchResults table has a title, firstname, lastname, and chairsID
field, which I want to be populated with the values from the drop down.

2) The drop down should only show the title, first and last names.
i.e Deputy John Johnson

3)After an item has been selected, I need something more than just one of
the fields to show up, such as "Deputy Johnson"

How do i get all four of these inserted into the WatchResults table while
displaying properly on the form?

 

[Ken Snell [MVP]]

Why are you wanting to store redundant data? Your WatchResults table should
just store the EmployeeID value; you can always get the employee's name and
title any time via a query that joins the WatchResults and Employees tables.

 

[Guest]

OK.. that makes sense.. so how do i go about showing those items, and
storing just the chairs number in the table?

 

[Ken Snell [MVP]]

Showing these data under which circumstances? In the combo box? on the form
after you select a chair in the combo box? In a different form where you are
querying the WatchResults table?

 

[Guest]

Ok.. So now I am confused. But this is simply what I want. If you cuold help
me construct the code to make this happen, i would appreciate it; for i am
very unfamilar with Access. I have a combo box called cboEmp. That combo box
should display, when both closed and open, a combination of LastName + ',' +
FirstName + ' ' + TitleID. However, when a certain record is selected in this
drop down, I want the chairs ID to be stored into the WatchResults table. How
do i do this? Thanks for your help.

 

[Ken Snell [MVP]]

OK - this is fairly easy to do.

Open the form in design view. Right click on the combo box and select
Properties from the menu.

Click on Data tab. Click in Row Source box, and type the following:
SELECT [chairs ID], [LastName] & ", " & [FirstName] & " " & [TitleID] AS
FN FROM employees;

Click in Control Source box and select chairs ID from dropdown list (this
assumes that your form is bound to a record source that contains the
WatchResults table).

Click in Bound Column box and type number 1 there.

Click on Format tab. Click in Column Count box and type 2. Click in Column
Widths box and type the following:
0";1.5"

Save the form.

Should work for you now.
--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

Great. One more thing.. Is it possible to sort them the drop down by last
name now?

OK - this is fairly easy to do.

Open the form in design view. Right click on the combo box and select
Properties from the menu.

Click on Data tab. Click in Row Source box, and type the following:
SELECT [chairs ID], [LastName] & ", " & [FirstName] & " " & [TitleID] AS
FN FROM employees;

Click in Control Source box and select chairs ID from dropdown list (this
assumes that your form is bound to a record source that contains the
WatchResults table).

Click in Bound Column box and type number 1 there.

Click on Format tab. Click in Column Count box and type 2. Click in Column
Widths box and type the following:
0";1.5"

Save the form.

Should work for you now.
--

Ken Snell
<MS ACCESS MVP>

Ok.. So now I am confused. But this is simply what I want. If you cuold help
me construct the code to make this happen, i would appreciate it; for i am
very unfamilar with Access. I have a combo box called cboEmp. That combo box
should display, when both closed and open, a combination of LastName + ',' +
FirstName + ' ' + TitleID. However, when a certain record is selected in this
drop down, I want the chairs ID to be stored into the WatchResults table. How
do i do this? Thanks for your help.

 

[Ken Snell [MVP]]

Just add an ORDER BY clause:

SELECT [chairs ID], [LastName] & ", " & [FirstName] & " " & [TitleID] AS
FN FROM employees ORDER BY [LastName];

--

Ken Snell
<MS ACCESS MVP>

Great. One more thing.. Is it possible to sort them the drop down by last
name now?

OK - this is fairly easy to do.

Open the form in design view. Right click on the combo box and select
Properties from the menu.

Click on Data tab. Click in Row Source box, and type the following:
SELECT [chairs ID], [LastName] & ", " & [FirstName] & " " & [TitleID] AS
FN FROM employees;

Click in Control Source box and select chairs ID from dropdown list (this
assumes that your form is bound to a record source that contains the
WatchResults table).

Click in Bound Column box and type number 1 there.

Click on Format tab. Click in Column Count box and type 2. Click in Column
Widths box and type the following:
0";1.5"

Save the form.

Should work for you now.
--

Ken Snell
<MS ACCESS MVP>

Ok.. So now I am confused. But this is simply what I want. If you

cuold
help

me construct the code to make this happen, i would appreciate it; for i am
very unfamilar with Access. I have a combo box called cboEmp. That

combo
box

should display, when both closed and open, a combination of LastName +

','
+

FirstName + ' ' + TitleID. However, when a certain record is selected

in
this

drop down, I want the chairs ID to be stored into the WatchResults

table.
How

do i do this? Thanks for your help.

:

Showing these data under which circumstances? In the combo box? on

the
form

after you select a chair in the combo box? In a different form where

you
are

querying the WatchResults table?

--

Ken Snell
<MS ACCESS MVP>

OK.. that makes sense.. so how do i go about showing those items, and
storing just the chairs number in the table?

:

Why are you wanting to store redundant data? Your WatchResults table
should
just store the EmployeeID value; you can always get the

employee's
name

and
title any time via a query that joins the WatchResults and Employees
tables.

--

Ken Snell
<MS ACCESS MVP>

I just noticed that my code does not insert anything into the table.

I want to grab the title, firstname, lastnight, and chairsID

from
an

employees table. The data on the form is to be inserted into a
WatchResults
table. Here are the questions I have that should solve my problems:

1) The WatchResults table has a title, firstname, lastname, and
chairsID
field, which I want to be populated with the values from the drop
down.

2) The drop down should only show the title, first and last names.
i.e Deputy John Johnson

3)After an item has been selected, I need something more than

just
one

of
the fields to show up, such as "Deputy Johnson"

How do i get all four of these inserted into the WatchResults table
while
displaying properly on the form?

:

OK, so i have title, firstname, lastname, and chairsID all

as
values

that
need to be inserted into a table, but i only have title, firstname,
and
lastname displaying.

Since i need all four of these inserted into the table, wont
creating a
calculation in a query to show all three affect this?

:

A combobox can only show one field. One option you have is to
create a
query
that concatenates the three fields into one for display purposes.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I have a question regarding a combo box that I have

created.
It

has
the
tite,
first, and last name of a person showing. After selecting a
person
from
the
drop down, only the word "deputy" appears in the box.

How do
i

make
it so
a
combination of their title and last name displays, i.e "Deputy
Johnson"