Results 1 to 9 of 9
  1. #1
    mkling is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    27

    If Employee Selected Do Not Allow Employee to be Shown in Drop Down Again

    I am trying to create an available list of employees to assign to an event. Basically, when I assign someone to the event and need to assign additional people, I don't want the ones who have already been selected to show up in the drop down.



    Sheet1 is the linked Excel sheet where all of the employees are contained.
    Assigned officers is the table where those selected to work are stored.

    Using the following code, I get a prompt for sheet1.[employee id]

    Code:
    SELECT Sheet1.[Officer Name], Sheet1.[Employee Number], Sheet1.[Employee Number]
    FROM Sheet1
    WHERE (((Sheet1.[Employee Number]) Not In ([Assigned Officer].[Employee ID])));
    ;
    I don't think the "not in" is correct.

    Thanks in advance

  2. #2
    mkling is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    27
    I was able to get rid of the prompt but it does not requery the combo box.

  3. #3
    mkling is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    27
    Code:
    SELECT Employees.[Officer Name], Employees.[Employee Number]
    FROM Employees
    WHERE ((Employees.[Employee Number] Not In (select [Assigned Officer].[Employee ID] from [Assigned Officer])));
    Can someone suggest a solution? With the where statement included, I do not get anything showing in the drop down.

  4. #4
    RayMilhon is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    I would suggest using a multi-select listbox as each employee is selected they will be highlighted

  5. #5
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    Try changing your code to this:

    SELECT Sheet1.[Officer Name], Sheet1.[Employee Number], Sheet1.[Employee Number]
    FROM Sheet1
    WHERE (((Sheet1.[Employee Number]) Not In (select [Employee ID] from [Assigned officer])));

    The highlighted part uses a sub-query to create a list of employee_ID's which are already in the [Assigned Officer] table.
    The main query then selects the employee_ID's which do not appear in that "already selected" list.

    WHERE fieldname IN (...) or WHERE fieldname NOT IN (...) always require a sub-query, e.g. (Select...) or an explicit list e.g. ("A", "B", "C") in the parentheses.

    The Select in the sub-query can only return one field per record, otherwise it will generate an error.

    HTH

    John

  6. #6
    mkling is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    27
    This is killing me......

    I get the list when I just have:
    Code:
    SELECT Employees.[Officer Name], Employees.[Employee Number]
    FROM Employees;
    Of course it does not eliminate the emplyees who have been assigned.

    When I add:

    Code:
    WHERE (((Employees.[Employee Number]) Not In (select [Employee ID] from [Assigned Officer])));
    I get a blank drop down.

    What am I missing?

  7. #7
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    Are you requerying the combo box each time? Use the Select statement as the row source for the combo box, and then each time you have selected an employee, do a cboEmployeeList.Requery.

    If you are already doing that, then the other possibility is that there really are no employees whose ID is not already in the [Assigned Officer] table. Could that be the case?

    John

  8. #8
    mkling is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    27
    Quote Originally Posted by John_G View Post
    Hi -

    Are you requerying the combo box each time? Use the Select statement as the row source for the combo box, and then each time you have selected an employee, do a cboEmployeeList.Requery.

    If you are already doing that, then the other possibility is that there really are no employees whose ID is not already in the [Assigned Officer] table. Could that be the case?

    John
    No. The [assigned officers] table is empty. I understand about the requery. I just can't seem to get this to work for the first employee. All should show then after the requery. All should show less the officer in assigned after the selection. I am confused.

    Thank you for trying to help. I may have to do without this feature.

  9. #9
    mkling is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    27
    This works:

    Code:
    SELECT *
    FROM Employees
    WHERE (((Exists (select [employee id] from [assigned officer] where employees.[employee number] = [assigned officer].[employee id]))=False));
    I found it elsewhere and modified it for my use. Long way to go for this solution.

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

Similar Threads

  1. Database Design for employee
    By Ramya in forum Database Design
    Replies: 1
    Last Post: 07-29-2011, 11:57 AM
  2. Append employee name with '
    By jgelpi16 in forum Programming
    Replies: 8
    Last Post: 02-11-2011, 09:30 AM
  3. Employee Tracking
    By nycon in forum Access
    Replies: 6
    Last Post: 10-27-2010, 05:42 PM
  4. Employee Attendance database
    By oxicottin in forum Database Design
    Replies: 0
    Last Post: 02-14-2007, 02:58 PM
  5. Qry that asks for specific employee name
    By oxicottin in forum Queries
    Replies: 0
    Last Post: 02-05-2007, 08:50 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