Results 1 to 14 of 14
  1. #1
    mdroulet is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Nov 2013
    Posts
    6

    Limit results of combo box to assigned tasks from a master task list

    Hello All,



    I am a long time reader, but a first time writer. I have a database for tracking training within my organization. Each employee is assigned specific tasks from a master task list. From there, each employee updates the assigned tasks as they are completed. I have a form titled "Update", which is based on a query with the criteria based on the current user. The form has a combo box with shows the tasks. However, the combo box shows all tasks, and not only the assigned tasks. I am trying to either limit the results of the combo box to only show assigned tasks, or apply a filter through a command button to toggle between all tasks and only the assigned tasks. The query criteria is set to show only required tasks, and when I run the query, it displays correctly. However, I haven't figured out how to make the combo box only display the required tasks.

    Thank you for your help...

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Have you set the RowSource of your ComboBox to the query of which you spoke?

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    By 'required', do you mean the tasks assigned to employee?

    Is this form used to enter new tasks for employee? If this is a bound combobox used to select task to associate with employee, would not want to restrict the list except to show only tasks employee is not yet associated with.

    If this an unbound combobox used to enter filter criteria, that is different. Sounds like dependent combobox. The RowSource SQL would be like:

    SELECT taskID FROM EmpTasks WHERE EmpID = [EmpID];

    EDIT: Oooops, did not see RG's reply.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    mdroulet is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Nov 2013
    Posts
    6
    Hello Again,

    Thank you for the quick replies. It is getting closer. To answer the question above: This form is not used to enter new tasks. That is taken care of at a manager level. The user opens this form to update progress on each task, as there are many steps. Ideally, the combo box would only display the assigned tasks, and the user could click on the task in the combo box to go to that record. From there, the use would update the progress on that particular task. When I set the row source as indicated above, the combo box did show only required tasks, but the results were not linked to the rest of the form. Currently, it is one form, and not a form/subform combination. If I can solve it without rebuilding, that would be great, as there are many forms for many different specialties.

    Again, your help is greatly appreciated.

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    The RowSource of the ComboBox need not be linked to the form. I asked the question so you can see the results of the RowSource on the ComboBox. I'm not sure to what task you are putting the ComboBox. Is your current ComboBox bound (does it have a control source)? If so, what is the Field Type to which it is bound? If not, what is in the AfterUpdate event of the ComboBox?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So once the desired task is selected in the UNBOUND combobox, need to use code to filter the form or go to the record. I use only VBA, like:

    Me.Filter = "TaskID=" & Me.TaskID
    Me.FilterOn = True

    Or if you have the form RecordSource as a parameterized query that references the combobox as input, then requery the form: Me.Requery

    Code could be in the combobox AfterUpdate event procedure
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    mdroulet is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Nov 2013
    Posts
    6
    Hello Ruralguy:

    Originally, I set the control of the combo box to the master task list for the specialty. That was an oversight, as it returns the entire list. I tried to set the rowsource to the corresponding value in the query, which returned the required tasks. However, the tasks returned in the combo box were not linked to the rest of the record. I hope I am conveying the message appropriately.

    Thank you again.

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Queries take up very little space in a db so your ComboBox can have a copy of the query that you spoke of as a RowSource but add additional criteria. Does that make sense?

  9. #9
    mdroulet is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Nov 2013
    Posts
    6
    RuralGuy,

    It does make sense. I am able to show the appropriate records in the combo box by using the rowsource and query as you suggested. However, the combo box values are not properly associating to the appropriate record in the form. Perhaps I will need to rebuild and use a form/subform combination. Or I could take a formal class in VBA...

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Not necessary as we can help here. Still a class in VBA would be useful.
    What do you normally have as the RowSource of the ComboBox and what is the ControlSource of that same cbo? There may be more questions depending on your answers.

  11. #11
    mdroulet is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Nov 2013
    Posts
    6
    RG,

    If you are willing, perhaps I can get this db to you for a quick look? It is currently about 20mb. Dropbox? In case that is not possible, I will do my best to answer your question:

    Initially, the control source of the combo box was set to the task table for that specialty and was locked to prevent inadvertently changing that value. However, as it grows and begins use at other offices, I realize that is not the way to go. Each employee will have a different task list, based on specific job description. I have another form built for a manager to assign tasks. It is a form/subform, and works fine.

    The Update query for each specialty sets the criteria that only required tasks are returned. So I thought. Therefore, when the employee opens the form to update a record, the record count indicates the number of required tasks. However, due to my incorrect use of the combo box, the combo box list shows all tasks in that specialty, which can be more than 200 in some cases. I have been going nuts trying to implement a filter on that combo box to show either all tasks or only required tasks. Ideally, I would like to be able to click on the required task in the combo box, which would take the user to that record to update it.

    If you are willing, perhaps I could get this db to you for a quick look? It is currently about 17 megs. Dropbox? I would be eternally grateful at this point.

  12. #12
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Do a Compact and Repair and then zip it up. If < 2MB you can attach to a post in the advanced area.

  13. #13
    mdroulet is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Nov 2013
    Posts
    6
    I just zipped and it is just under 7mb. Thank you anyway..

  14. #14
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I just sent you a PM. We can go offline for this if you like. I have the time and enjoy teaching.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 08-20-2012, 11:16 AM
  2. Limit Results
    By hithere in forum Queries
    Replies: 4
    Last Post: 02-23-2012, 02:13 PM
  3. Adding a list of tasks (at once) in a form
    By mooseisloose in forum Forms
    Replies: 8
    Last Post: 03-22-2011, 09:27 AM
  4. Replies: 0
    Last Post: 09-19-2009, 03:29 AM
  5. Export Access reports/query results to Outlook Calendar/Task
    By kfinpgh in forum Import/Export Data
    Replies: 0
    Last Post: 02-22-2007, 01:09 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums