Solved

Access 2003: Multiselect Listbox - saving and restoring

Posted on 2009-06-28
13
250 Views
Last Modified: 2012-05-07
Database created to enter workplan for the Section, which has tables for Staff, Team, Objectives and Activities. Each objective has one or more related activities, and each staff member belongs to only one team. There is an objective form and activities sub-form.

Each activity may have one person, multiple people (same or different teams), an entire team or multiple teams working on it. As such, I added a listbox which is populated by a query which unions the staff and team tables to provide a list of all staff and teams. My desired outcome is twofold:

1) Make selection(s) from the listbox and save to a table (or field?);
2) Be able to reflect the selections when the forms are being browsed.

I have not created the table (or field?) to house the listbox selections yet, since I'm not sure what I'll be saving to it. I will also be generating reports, but I'm hopeful that if the right data is saved this should not be too traumatic.

Hope this isn't too long and I am clear. Any assistance that can be provided will be greatly appreciated!
0
Comment
Question by:JayABee
  • 5
  • 4
  • 3
13 Comments
 
LVL 84
ID: 24730927
This could be a difficult relationship to illustrate, depending on what you other requirements would be.

You have an Item (an Activity, in this case) which can be related to two different entities (Staff and Team). That Item can be related to multiple numbers/combinations of those Entities.

Your Union query should bring together the needed items for a listing ... I'd suggest, however, that you represent with a subform (which can be constructed to look very similar to a listbox, if need be) which could then relate the user choices of Staff + Team with an Activity. Access would then handle both your #1 and #2 questions for you, assuming (a) you setup this table relationship properly and (b) you setup the mainform/subform properly.

Same would apply for reporting ... you'd use a report/subreport setup to display the Activity + (Staff and/or Team) relationship.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24732085
I take it persons and teams is mutually exclusive.  You have either individual(s) selected or team(s) selected??

You cannot have one or more persons plus one or more teams to which the persons do not belong??
0
 

Author Comment

by:JayABee
ID: 24733306
GRayL...that's correct.  If a team is selected then the indivual members of the team would not be, and if an individual is selected, the team that person belongs to would not be.

LSMConsulting...not sure I know how to do your (a) and (b) suggestions to make it work.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 84
ID: 24734886
How will you relate an Activity to a Staff member and/or a Team? You must represent this via a table, and typically you'd have a Join table to do this, but how would you differentiate between a Staff member and a Team?

Perhaps you would need two tables for this relationship - one to store an Activity + Team, and another to store Activity + Staff:

tActivity
-------------------
lActivityID [PK, Autonumber]
sActivityDesc
dActivityDate
etc etc

tStaff
----------------
lStaffID [PK, AN]
sLastName
sFirstName
etc etc

tTeam
--------------
lTeamID
sTeamName
etc etc

tTeamMembers
---------------
lTeamMemberID [PK]
lTeamID
lStaffID

Using this setup, you can add Staff, then add Teams, then add Staff to Teams, then add Activities. Now, to relate both:

tActivityTeams
--------------------
lActTeamID
lActID
lTeamID

And to add Staff to Activity

tActivityStaff
------------------
lActStaffID
lActID
lStaffID

tActivityTeam would store the link between an Activity and a Team, which is (of course) made up of various Staff members. tActivityStaff would store the link between an Activity and an individual Staff member.

I'm not sure how you'd represent this on a single form ... it would seem to me the simplest way would be to use 2 subforms, one based on each of the Activity join tables above. You can build a subform to look very similar to a listbox, and if you use a subform you can allow Access to keep up with the data storage.

Of course, you would need code in both to insure that a user has not already been selected when trying to add that user via Staff or Team. You'd do this in the BeforeUpdate event of those forms.
0
 

Author Comment

by:JayABee
ID: 24735843
Will give a try and see...many thanks for your efforts!
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24736509
scott:  You seem to have misplaced Objectives ;-)
0
 
LVL 44

Accepted Solution

by:
GRayL earned 250 total points
ID: 24736972
JayABee:  I would have two multi-select list boxes for Staff and Teams on a form.  The moment a selection is made in either one, the AfterUpdate() event of the selected list box would render the other box locked:

Private Sub lbxStaff_AfterUpdate()
  If Me!lbxStaff.ItemsSelected.Count > 0 Then
    Me!lbxTeams.Locked = True
  Else
    Me!lbxTeams.Locked = False
  End If
End Sub

Private Sub lbxTeams_AfterUpdate()
  If Me!lbxTeams.ItemsSelected.Count > 0 Then
    Me!lbxStaff.Locked = True
  Else
    Me!lbxStaff.Locked = False
  End If
End Sub
0
 

Author Comment

by:JayABee
ID: 24737027
Each team leader inputs the work plan objectives for the year (agreed upon with senior management) and any related activities in which the entire team is involved.  Each staff member then adds the specific activities under each objective which relate to them, or which they are working on jointly with others (having agreed who will enter which joint activity to avoid redundancy; I plan on working out a more elegant solution, time permitting).  There is a main objective form and an activities sub-form.   The initial design had each activity with only one accountable staff member, now it requires all involved staff members/teams to be reflected in each activity.

So where a combo box worked quite nicely to pick one staff member, I was considering a listbox to select multiple staff members/teams.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24737181
As you can see from my code above,  making one or more selections in either box renders the other box locked.  It is impossible to get a mixture from both boxes -  one or more Staff or one or more Teams.  
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 24737440
<scott:  You seem to have misplaced Objectives ;-)>

I've often been accused of this :} ...

As to the issue with STaff and Team, I've done something similar with other apps. In one, the client needed to be able to choose to alert (i.e. email) specific individuals, or choose a group of individuals who all share the same "role" in the database. The way I got around it was this:

Allow the user to select either the Role, or select each person individually. When the user selected an Individual, the listbox just highlighted that specific Person. If, however, the user selected a Role (which contained one or more Users), then the "User" listbox would select every person in that Role ... so, for example, if I have this:

tUser
-----------------
UserID   UserName
1            Scott
2            Ray
3            Jay
4            Bob
5            Sue
6            Lisa

tRoles
------------
RoleID  RoleName
1          Management
2          R&D
3          DataEntry

And Role associations like this:

tUserRoles
------------------
RoleID   UserID
1             1
1             2
1             3
1             4
2             1
2             5
2             6
3             3
3             6

Then when I select the "Management" role, I fire code that will select Scott, Ray, Jay and Bob in the "Users" list ... if I further select the R&D Role, then code would select Scott, Sue and Lisa. From there, I could then de-select anyone I needed to. If I de-select Management, then I'd have code that would (a) cycle through the Users listbox to see if one of the associated users is already selected and then (b) determine if that user is associated with another, selected Role and then (d) remove that selection, assuming that (b) is not True.

IOW, while you only store the Users that are associated with an Activity, you would allow your users to select either a User or a Team and your code would make the distinction behind the scenes. This allows a UI that is user-friendly, but allows you to maintain a proper relational setup between Activities and Users.

This was the best solution for my specific needs, and has served the client well for the past several years. YMMV, of course.
0
 

Author Comment

by:JayABee
ID: 24744691
Thanks Scott...will work on this today and see how it goes.

GRayL: I also like the idea of two multiselect boxes for staff and teams.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24746286
IMHO, it's the only way you can provide the option of selecting one or the other, but once made, freezing out the other until you have completely 'backed out' of the selected list box.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question