title: Hide items in Access 2007 combo box without also clearing the text for records that refer to the hidden items
body: <p>Say I have two tables in Access 2007:</p>
  
  <pre><code>Table 'Person:
    Id : Autonumber
    Name : Text
    IsActive : Yes/No
  
  Table 'Note':
    Id : Autonumber
    PersonId : Number (foreign key, Person.Id)
    Note : Memo
  </code></pre>
  
  <p>I have a form that lists all entries in the Note table, and allows users to insert/update records.  The control for the Person field on the form is a combo box with </p>
  
  <pre><code>Control Source = PersonId
  Row Source = SELECT Person.Name, Person.Id FROM Person;
  Bound Column = 2
  Column Count = 2
  Column Widths = 3cm;0cm
  </code></pre>
  
  <p>Now I want to limit the combo box so it only allows active persons to be selected, but when I add a 'WHERE Person.IsActive' to the RowSource query, the combo box for all records which refer to inactive people show up blank.  I thought that setting the 'Limit To List' option to 'No' would fix this, but when I do Access forces me to change the displayed column so that the combo box now displays the Person ids rather than the Person names, which is not helpful.</p>
  
  <p>Is there a good way to have the best of both worlds?  I want to be able to hide inactive people from the combo box list, but still have inactive people show up as the value for the combo box in records that were created while currently-inactive people were still active.</p>
  
  <p><strong>Edit</strong>: related question: <a href="https://stackoverflow.com/q/86278/1030345">Custom row source for combo box in continuous form in Access</a></p>