Results 1 to 6 of 6
  1. #1
    Spacle is offline Novice
    Windows 2K Access 2007
    Join Date
    Dec 2013
    Location
    Jensen Beach, FL
    Posts
    4

    Need Help Editing Drop Down on User Form

    Hi! I am good with Excel but I have very little Access experience and I have inherited an Access file that I need to update. We use the Access file as a ticketing system, to allow employees to go in and create "work tickets" for a department using a User Form.

    I need to update the Employees Table so that when people go to submit a ticket, and they click on the drop down box for "Created By" that they see an up-to-date list of our employees. The list is currently very out of date. I can go in to the table and add a new employee easily enough. My issue is that I want to remove employees that no longer work for us from the possibilities in the "Created By" drop down list, however I don't want to delete them from the Employees table because for historical purposes I may need to see who the creator of an old ticket was, and it could of course, be an employee that no longer works with us.

    So I need a solution to edit the Employees table in a way that leaves everyone in the table, yet also allows me to indicate on a record that they are no longer an active employee, and exclude them from the "Created By" drop down list since they don't work for us anymore. For example, could I add a column to my Employees table called "Status" and then make everyone either "Active" or "Inactive" and then somehow tell the combo box to only display people with a Status of "Active"?



    Can anyone tell me how to accomplish this?

    Thanks in advance!
    Spacle

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,839
    I put a button beside the drop down, "edit"
    it opens the Empoyee form/table, add new records.
    close the form
    now on the form with the combo , click the REFRESH ALL button on the toolbar.

    now all new entries will show.

  3. #3
    Spacle is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2013
    Location
    Jensen Beach, FL
    Posts
    4
    Hi ranman256! Thanks for the reply! I am able to add people to the table, I need to know how to remove people from the drop down list without removing them from the table itself. Any ideas?

    Thanks in advance!

  4. #4
    hammerman is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Jul 2014
    Posts
    32
    Novice Access user here; Have you tried adding a yes/no field for each record and when that box is checked/not checked then don't show that record. Forum Moderators can veto that suggestion if it's not a 'good' option. Any feed back is welcome; I'm learning too.

  5. #5
    Spacle is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2013
    Location
    Jensen Beach, FL
    Posts
    4
    Hi hammerman! I am totally open to doing that, as that is basically what I was assuming would be the answer (i.e. adding a column with a value that identifies which ones are active versus inactive) but I don't know how to control what the combo box is showing. At the moment, it appears to be showing everything in the table, and I need to know how to tell it to only show records that have, for example, "Active" in the status column.

    Thanks for the reply!

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,294
    I would add another field named "IsActive" (type "Yes/No") to tblEmployees.
    Set the field "IsActive" to True/False for each employee.
    Then change the combo box Row Source to include a WHERE Clause.
    Something like
    Code:
    SELECT Employee_PK, LName & ", " & FName as FullName 
    FROM Employees
    WHERE IsActive = TRUE
    Close as I can get without knowing the combo box Row Source......
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

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

Similar Threads

  1. Replies: 3
    Last Post: 06-23-2017, 07:03 AM
  2. editing drop down menu in database (not designed by me)
    By easy access in forum Database Design
    Replies: 5
    Last Post: 03-04-2017, 09:21 AM
  3. editing a drop down list
    By multitech in forum Forms
    Replies: 2
    Last Post: 05-19-2016, 07:35 AM
  4. Replies: 5
    Last Post: 03-21-2013, 01:59 PM
  5. Replies: 5
    Last Post: 08-26-2011, 04:05 AM

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 - Senior Forums