More combo box help

[Guest]

I did this yesterday - I'm not sure what the problem is.

I'm setting up tables to create training sessions. Employees (coming from
different orgs) can attend multiple training sessions.

Tables:

Employees
-EmployeeID (pk)
-FirstName
-LastName
-Organization

Organizations
-OrganizationID (pk)
-OrganizationName

Training Sessions
-TrainingSessionID (pk)
-TrainingSessionLocation
-TrainingSessionDate

UsersInTrainingSessions
-UserTrainingID (pk)
-UserInTraining
-TrainingSession

Training Junction
-TrainingJunctionID (pk)
-TrainingUserJun
-SessionJun

What I would love is:

1. The UserInTraining field to be a list box which reads "[LastName] & ", "
& [FirstName] & " - " & [OrganizationName]. Should this be linked to
OrganizationID or OrganizationName? When I link it to OrganizationID, I just
get that PK number, and when I link it to OrganizationName, it beeps at me
when I try to input data.

2. That list to be listed alphabetically by last name

Please tell me exactly where your reply needs to go - which table, which
field and which properties item. I guess I'm just not getting it.

Thank you in advance.

 

[Brian Bastl]

Comments in line (way down towards the bottom):

I did this yesterday - I'm not sure what the problem is.

I'm setting up tables to create training sessions. Employees (coming from
different orgs) can attend multiple training sessions.

Tables:

Employees
-EmployeeID (pk)
-FirstName
-LastName
-Organization

Organizations
-OrganizationID (pk)
-OrganizationName

Training Sessions
-TrainingSessionID (pk)
-TrainingSessionLocation
-TrainingSessionDate

UsersInTrainingSessions
-UserTrainingID (pk)
-UserInTraining
-TrainingSession

Training Junction
-TrainingJunctionID (pk)
-TrainingUserJun
-SessionJun

What I would love is:

1. The UserInTraining field to be a list box which reads "[LastName] & ", "
& [FirstName] & " - " & [OrganizationName]. Should this be linked to
OrganizationID or OrganizationName? When I link it to OrganizationID, I just
get that PK number, and when I link it to OrganizationName, it beeps at me
when I try to input data.

Is it your intent to click/ double click the person in the Listbox to add
them to your UsersInTrainingSessions table? Could you use a combobox
instead? Either way, what you'll actually be writing to the table is just
the PK from your Employees table.

2. That list to be listed alphabetically by last name

Easy to do, but you may want to do a secondary sort on First names, if there
could be more than 1 Smith or Jackson or Rodriquez or Wang. The rowsource
for the listbox (or combo) would be along the lines of the following:

SELECT Employees.EmployeeID, [Employees].[LastName] & ", " &
[Employees].[FirstName] & " - " & [Organizations].[Organization] AS Employee
FROM Employees INNER JOIN Organizations ON Employees.Organization =
Organizations.OrganizationID
ORDER BY Employees.LastName, Employees.FirstName;

I'm assuming in the above SQL that [Employees].[Organization] is being
stored as a long integer, and not text, since the joining fields need to be
the same data type.


Post back if you need further assistance,
Brian