Results 1 to 4 of 4
  1. #1
    dkrumholz is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2012
    Posts
    4

    Dropdown List - Need to Retain Inactive Entries

    I have a dropdown list control that I use to indicate who accomplished a task on a form that navigates transaction records. The dropdown list is populated from an employee table. That employee table has an active and inactive indicator. When I add a new transaction record I want to be able to select only active employees from the list. But when I navigate old transaction records I want to still show the name of the employee who accomplished the task even if they are no longer active. What is the easiest way to accomplish that?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,952
    Have code that changes the RowSource. I use only VBA, don't know if macro can do this.

    Code in the form Current and/or combobox GotFocus event like:

    Me.comboboxname.RowSource = "SELECT EmpID, EmpName FROM Employees " & IIf(IsNull(Me.EmpID),"WHERE Active=True","")
    Me.comboboxname.Requery
    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.

  3. #3
    dkrumholz is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2012
    Posts
    4
    Do I then need to reset the rowsource to all employee records when it loses focus? I still need to show names that are not active.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,952
    That's the issue with filtered combobox RowSource that uses lookup when form is in Continuous or Datasheet view. On a new record, changing the RowSource to restrict the list means the RowSource is changed for all instances of the combobox so other rows will not show the alias value because it is no longer available.

    The only way I know to handle this is to have an unbound and locked textbox that displays the alias descriptive data and next to it is the combobox sized to just show the dropdown. This means including the lookup table in the form's RecordSource by query join, join type "Include all records from [your primary dataset] ..."

    Don't use Datasheet view. Controls in Continuous view can be arranged to appear as datasheet.
    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.

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

Similar Threads

  1. Dropdown list
    By imintrouble in forum Access
    Replies: 2
    Last Post: 01-20-2012, 08:09 AM
  2. Dropdown list
    By stratack in forum Access
    Replies: 1
    Last Post: 07-25-2011, 06:58 AM
  3. Dropdown List: Use Each Item Once?
    By Heavy Doody in forum Access
    Replies: 1
    Last Post: 05-02-2011, 07:44 AM
  4. Data Entry Form: Retain Selected List Box Value
    By William McKinley in forum Forms
    Replies: 1
    Last Post: 11-05-2010, 10:14 AM
  5. Set up a dropdown List
    By asherbear in forum Access
    Replies: 2
    Last Post: 06-24-2010, 06:45 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