title: How to force the update of an Access combobox?
body: <p>Is there a technique I'm overlooking when coding my form?</p>
  
  <p>I have a pair of cascading combos.</p>
  
  <ol>
  <li><p>ComboSource filters the options available for choice in ComboInformation</p></li>
  <li><p>ComboInformation sets the contents of a mandatory field in a table</p></li>
  </ol>
  
  <p>The rowsource for ComboSource is:</p>
  
  <pre><code>SELECT tblSource.SourceID, tblSource.Source
  FROM tblSource
  ORDER BY tblSource.Source;
  </code></pre>
  
  <p>The rowsource for ComboInformation is:</p>
  
  <pre><code>SELECT tblInformation.InformationID, tblInformation.SourceID, tblInformation.InformationSelector
  FROM tblInformation
  WHERE (((tblInformation.SourceID)=[ComboSource])) OR ((([ComboSource]) Is Null))
  ORDER BY tblInformation.InformationSelector;
  </code></pre>
  
  <p>It is valid to select a value in ComboSource which results in the .Listcount for ComboInformation being zero. The user can choose to type a new value in ComboInformation and be prompted to create the relevant item in the Information table linked to the ComboSource entry in the Source table, or they can choose to navigate back to ComboSource to select a different Source.</p>
  
  <p>The problem arises when a user edits an existing record and changes ComboSource to a value for which there is no associated Information records. In the After_Update event for ComboSource I have the following code, intended to update ComboInformation to reflect the new source and force the user to select a new value for ComboInformation (otherwise they could save the record with the old value of Information without realising it).</p>
  
  <pre><code>Me.ComboInformation.Requery 'Reflect the current source
  
  'Set a default value for ComboInformation
  If Me.ComboInformation.ListCount &gt; 0 Then
       Me.ComboInformation.DefaultValue = Me.InformationTitle.ItemData(0)
  Else
       On Error Resume Next 'Ignore inevitable error
       Me.ComboInformation.DefaultValue = Null
       On Error GoTo PROC_ERR 'restore normal error handling
  End If
  
  'Force the user to update Information by setting content to "" -- if this isn't done, the
  'record can be saved with the 'previous value of Information and the user may not realise 
  'they haven't made any change
  
  ComboInformation.SetFocus
  
  If Not Me.NewRecord Then
       If Me.ComboInformation.ListCount = 0 Then
            'Clear info to force it to be updated
            On Error Resume Next 'Ignore inevitable error
            ComboInformation.Text = "" 'Set it to an invalid value
            ComboInformation= Null
            On Error GoTo PROC_ERR
        Else
            ComboInformation= ComboInformation.DefaultValue 'set it to a valid value
            End If
        End If
  End If
  </code></pre>
  
  <p>If the user selects a source for which there are no valid information choices, and then chooses to create a new value for Information, no problem. But if they decide to navigate back to ComboSource instead, they get an error message insisting they complete ComboInformation, which they can't do. They can undo the change they made to get back to the previous state, but that isn't an intuitive response to an error message telling them to complete ComboInformation.</p>
  
  <p>Is there either a way to allow them to navigate back to ComboSource when ComboInformation is invalid or another way to force them to update ComboInformation after updating ComboSource before saving the record that doesn't have this issue?</p>
  
  <p>Update about the error message:  I can trap it in Form_Error although the error message isn't generated there -- no other procedures in the stack. The error message is the message associated with the Information field in the Information table (Validation rule: Is Not Null; Validation text; Every piece of evidence must derive from a piece of information)</p>
  
  <p>Further update: I could handle the error (3316) in the Form_OnError but there will be occasions when isn't a 'valid error' that I don't want to ignore...</p>
  
  <p>Other things tried so far:</p>
  
  <p>Refreshing the page doesn't help.</p>
  
  <h2>Demonstration database</h2>
  
  <p>There's a database to demo the problem at <a href="https://s3-eu-west-1.amazonaws.com/genquiry/test.accdb" rel="nofollow">https://s3-eu-west-1.amazonaws.com/genquiry/test.accdb</a></p>
  
  <p>Open the only form in the database, set the Source to S3 and attempt to navigate back to Source to select a different value.</p>