List Boxes

[DMC]

I have two tables. One for classes and one for students. I want a drop down
box or list box in the classes table to show all students. The fields in the
student table are separate such as first and last name. I can choose
multiple names but it only shows the last name not first and last. Is this
possible. DMC

 

[Graham Mandeno]

Hi DMC

Instead of basing your list/combo box's RowSource on the Students table,
base it on a query like this:

Select [PrimaryKey], [FirstName] & " " & [LastName] as SName
from [YourTable] order by [FirstName], [LastName];

Of course you should substitute your own names for the table and fields as
required.

Then, set these other properties for the list:
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0

The ColumnWidths setting causes the first column to be hidden.
It will morph into "0 in" or "0 cm" but that doesn't matter.

Then you will see the name "John Smith" in the list, but what is actually
stored in your bound field will be the primary key value (StudentID) for
that student.

If you would prefer to see "SMITH, John", then set the RowSource to:

Select [PrimaryKey], UCase([LastName]) & ", " & [FirstName] as SName
from [YourTable] order by [LastName], [FirstName];

 

[KARL DEWEY]

Wrong!
You need a third table - a junction table that has fields to connect the
Student to the Classes. Use an Autonumber field as primary key like this --
Student ---
StudentID - Autonumber - primary key
Fname - text
Lname - text
etc.

Classes ---
ClassID - Autonumber - primary key
Title - text
StartDate - DateTime
EndDate - DateTime
Instructor - text
etc.

ClassStudent ---
ClassID - number - long integer - foreign key
StudentID - number - long integer - foreign key
Grade - text
Point - number - single
etc.

Create a one-to-many relationship between the Student and ClassStudent on
the StudentID field and one-to-many relationship between the Classes and
ClassStudent on the ClasstID field.

Use a form/subform linked Master/Child on the StudentID and ClasstID fields.
Set the subform to Datasheet view with a combo to selct student or class
depending on which form you have open.

 

[Larry Linson]

I have two tables. One for classes and one for students. I want a drop
down
box or list box in the classes table to show all students. The fields in
the
student table are separate such as first and last name. I can choose
multiple names but it only shows the last name not first and last. Is
this
possible. DMC

If you feel compelled to work "in the Table" itself, that is, Datasheet
View, you constrain the features you have available. What you want to do
would be relatively simple if you were working with the identical data in a
Form. You could put a Combo Box in the Form header, create a Query to show
all the unique students, and show the name in a user-friendly form. As far
as I know, there's no way to do what you want in Datasheet View.

Larry Linson
Microsoft Office Access MVP

 

[John W. Vinson]

I have two tables. One for classes and one for students. I want a drop down
box or list box in the classes table to show all students. The fields in the
student table are separate such as first and last name. I can choose
multiple names but it only shows the last name not first and last. Is this
possible. DMC

First off... don't use table datasheets to interact with your tables. Tables
are designed for data storage; even with the new features (misfeatures, some
would say!) in A2007, they are VERY limited in flexibility and power compared
to Forms.

What you can do is create a Form based on the Classes table, with a Subform
based on the Students table. Does the Students table have a ClassID in it to
indicate what class the student is in? Can a student take multiple classes
(perhaps over time)? If so you may need an Enrollment table; how are your
tables structured and related currently?

 

[Graham Mandeno]

PS: I did not understand that you were actually talking about a combo box in
*table* view. If so, then I agree wholeheartedly with what the others have
said. Tables are for storing data, not for viewing it and certainly not for
editing it!

(However, as you specifically mention listboxes, which cannot be used in
table view, I assume you really are talking about a form)

I also concur with John's and Karl's advice on the use of a "junction table"
for enrolments. Obviously one class can have more than one student. If one
student can also be in more than one class, even over time, then you have a
many-to-many relationship which cannot be represented by just two tables.

In any case, my advice on setting up the combo/list box still stands.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi DMC

Instead of basing your list/combo box's RowSource on the Students table,
base it on a query like this:

Select [PrimaryKey], [FirstName] & " " & [LastName] as SName
from [YourTable] order by [FirstName], [LastName];

Of course you should substitute your own names for the table and fields as
required.

Then, set these other properties for the list:
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0

The ColumnWidths setting causes the first column to be hidden.
It will morph into "0 in" or "0 cm" but that doesn't matter.

Then you will see the name "John Smith" in the list, but what is actually
stored in your bound field will be the primary key value (StudentID) for
that student.

If you would prefer to see "SMITH, John", then set the RowSource to:

Select [PrimaryKey], UCase([LastName]) & ", " & [FirstName] as SName
from [YourTable] order by [LastName], [FirstName];

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have two tables. One for classes and one for students. I want a drop
down
box or list box in the classes table to show all students. The fields in
the
student table are separate such as first and last name. I can choose
multiple names but it only shows the last name not first and last. Is
this
possible. DMC