How do I disable selection from multi-select combobox dropdown if it has already been selected in another record?

Question

I volunteer for the Red Cross and have been asked to create a database to assign tasks to employees based on the days and shift they work. I am using multi-select comboboxes, so for each employee there may be multiple tasks for Monday, Tuesday, etc. I have a separate table for tasks which populates each combobox on the main form. I have filtered the tasks by shift so only the appropriate tasks for each shift will show up in the combobox [cmbMondayTasks, cmbTuesdayTasks, etc.] However, management has now asked me to build it such that if they have already assigned a task to someone, it should no longer be available in the dropdown to select for another employee, or it could just pop up a message saying "This task has already been assigned to another employee." I think either method would be acceptable to them.

I haven't used Access for many many years and am struggling with this so any assistance you can provide would be appreciated. I am somewhat familiar with VBA if the solution requires using that. Thank you in advance.

Answer 1

Thank you for your continued guidance. Attached is the design of the revised tblTasks. I have the formfields populating with the proper shifts .... so how would I go about checking to see if a task has already been assigned to another employee for a specific day?

tblTasks Design.png

Answer 2

Thanks, Joanne.

This is what I feared. The table design is inappropriate, which complicates the selection process in the interface.

Your table with fields for each day of the week encodes Data (i.e. the name of a Weekday) into the field names. This is most often seen when importing XLSX spreadsheets as tables, but it is also done in error in Access.

Rather than post an extended explanation here, I'm going to refer to some resources to help you sort out the problems in the table design..

Here is a series of blog articles on the design flaw and how to correct it.

[http://www.rogersaccessblog.com/2017/08/16/the-normal-forms-first-normal-form-1nf/]

[http://www.rogersaccessblog.com/category/problems-with-repeated-columns/]

You'll find a lot of good examples of properly designed databases on this location:

https://www.dropbox.com/scl/fo/0scigd3r48hx5xrev2jrf/AB0-GMdTgMAO5O1cGdr3QW0?rlkey=ib6bs6g9jqcrywwzivur3265t&e=2&dl=0

You'll find several good introductory level YouTube videos here:
https://www.youtube.com/@decomplexify
I like these because they put the concepts in easy-to-understand terms.

In addition, you've used MVF fields, which is highly discouraged for all but the simplest of choice selections. What you are doing is far too complex to be managed with these simple controls.

Once you get your tables sorted out, you can take another look at the interface, which needs to be set up as a main form with subforms.

Answer 3

The exclusion of previously assigned takes should be pretty simple, but it depends on the underlying table designs. And we need more details about that table design than the general description so far.

Please show us, in design view, the tables you have. Or a screenshot of the relationships window. Thank you.

It does no good to start designing an interface until after you have solidified the tables.

The way you describe "separate [list]boxes for each weekday", hints at an invalid table design, so that needs to be vetted and verified first.

BTW. Multi-Select is a property of listboxes. It is not a property of comboboxes.

Answer 4

To achieve the functionality of disabling selection from a multi-select combo box dropdown in Microsoft Access, you can use VBA to check if a task has already been assigned to another employee. Here’s a general approach you can follow:

1. Create a Function to Check Task Assignment: Write a VBA function that checks if the selected task is already assigned to another employee. This function can query your tasks table to see if the task is already linked to any other records.

      Function IsTaskAssigned(taskID As Long) As Boolean
          Dim rs As DAO.Recordset
          Set rs = CurrentDb.OpenRecordset("SELECT * FROM Tasks WHERE TaskID = " & taskID & " AND EmployeeID <> " & CurrentEmployeeID)
          IsTaskAssigned = Not rs.EOF
          rs.Close
          Set rs = Nothing
      End Function
   

2. Use the AfterUpdate Event: In the AfterUpdate event of your combo box, call this function to check if the selected task is already assigned. If it is, you can either disable the selection or show a message.

      Private Sub cmbMondayTasks_AfterUpdate()
          If IsTaskAssigned(Me.cmbMondayTasks.Value) Then
              MsgBox "This task has already been assigned to another employee."
              Me.cmbMondayTasks.Value = Null  ' Clear the selection
          End If
      End Sub
   

3. Filter the Combo Box: You may also want to filter the combo box to exclude tasks that are already assigned. This can be done by modifying the RowSource of the combo box based on the current assignments.

      Private Sub Form_Current()
          Me.cmbMondayTasks.RowSource = "SELECT TaskID, TaskName FROM Tasks WHERE TaskID NOT IN (SELECT TaskID FROM Tasks WHERE EmployeeID <> " & CurrentEmployeeID & ")"
      End Sub
   

This way, when a task is assigned to an employee, it will not appear in the dropdown for other employees, or it will notify the user if they attempt to select an already assigned task.