title: Subform doesn't update with new data added on the fly
body: <p>I'm having real trouble getting a subform to display the latest data when the data is added while the form is open (via an Append query).</p>
  
  <p>A quick explanation of the tables/forms/VBA &amp; SQL relevant to the problem:<br>
  I have three tables which record the teams within my department, the job roles available in the teams and the total number of positions available for each role.  </p>
  
  <p>The tables are:  </p>
  
  <ul>
  <li><strong>Teams</strong>: TeamID <em>(AutoNum, PK)</em>, TeamName <em>(Text)</em>, CostCode <em>(Text)</em></li>
  <li><strong>Roles</strong>: RoleID <em>(AutoNum, PK)</em>, RoleDesc <em>(Text)</em>, Abbrev <em>(Text)</em>  </li>
  <li><strong>Team_Composition</strong>: TeamID <em>(Num, PK)</em>, RoleID <em>(Num, PK)</em>, RoleCount <em>(Num)</em></li>
  </ul>
  
  <p>The form is as below with <em>TeamID</em> linking the Master/Child fields :<br>
  <a href="https://i.stack.imgur.com/56JNt.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/56JNt.png" alt="enter image description here"></a></p>
  
  <p>The <em>RecordSource</em> for the main form is to the <em>Teams</em> table.<br>
  The <em>RecordSource</em> for the subform is a query which allows the user to enter the required numbers for each role in each team in the <em>RoleCount</em> field:  </p>
  
  <pre><code>SELECT    Team_Composition.TeamID
          , Roles.RoleDesc
          , Roles.Abbrev
          , Team_Composition.RoleCount
  FROM    Team_Composition INNER JOIN Roles ON Team_Composition.RoleID = Roles.RoleID
  WHERE   Team_Composition.TeamID=[Forms]![Edit_Teams]![cmbTeamName]
  </code></pre>
  
  <p>The Team Name combo box on the main form gets it's data from the Teams table with the addition of <em>&lt; New Team ></em> as the first item in the list (the SingleRecord table is just that - a table with 1 field and 1 record so the SELECT will work):  </p>
  
  <pre><code>SELECT DISTINCT     0 AS TeamID
                      , '&lt;New Team&gt;' AS TeamName 
  FROM                SingleRecord  
  
  UNION ALL SELECT    TeamID
                      , TeamName 
  FROM                Teams 
  ORDER BY            TeamName
  </code></pre>
  
  <p>This all works very well when everything already exists on opening the form.  I can change the value in the combo box and VBA code fires to move to that record and display the linked data in the subform.  I can then add the totals for each team.
  <a href="https://i.stack.imgur.com/cTZPj.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/cTZPj.png" alt="enter image description here"></a></p>
  
  <p>The code that moves to the correct record is as below:  </p>
  
  <pre><code>'----------------------------------------------------------------------------------
  ' Procedure : cmbTeamName_AfterUpdate
  ' Author    : Darren Bartrup-Cook
  ' Date      : 12/06/2017
  ' Purpose   : Keeps the details on the form in sync with the team selected in the combo box.
  '             Ensures all teams have all roles available to them by updating the team_composition
  '             table with new roles whenever the team is selected.
  '-----------------------------------------------------------------------------------
  Private Sub cmbTeamName_AfterUpdate()
  
      'The first item in cmbTeamName is &lt;New Team&gt; which will not exist in the recordset.
      'To avoid FindFirst going to the wrong record an attempt is made to create a new record
      'allowing the form to filter to a non-existant record.
      If cmbTeamName = 0 Then
          DoCmd.GoToRecord , , acNewRec
      Else
          Dim rs As DAO.Recordset
          Set rs = Me.RecordsetClone
          rs.FindFirst "[TeamID]=" &amp; cmbTeamName
          If Not (rs.BOF And rs.EOF) Then
              Me.Recordset.Bookmark = rs.Bookmark
          End If
          rs.Close
          Set rs = Nothing
  
          If cmbTeamName &lt;&gt; 0 Then
              Update_TeamComposition cmbTeamName.Column(1)
          End If
  
      End If
  
  End Sub
  </code></pre>
  
  <p>The <code>Update_TeamComposition</code> procedure executes an SQL statement to ensure the team has an up to date list of roles available:  </p>
  
  <pre><code>Private Sub Update_TeamComposition(TeamName As String)
  
      With DoCmd
          .SetWarnings False
          .RunSQL "INSERT INTO Team_Composition(TeamID, RoleID) " &amp; _
                       "SELECT TeamID, RoleID " &amp; _
                       "FROM Teams, Roles " &amp; _
                       "WHERE TeamID = (SELECT TeamID FROM Teams WHERE TeamName='" &amp; TeamName &amp; "')"
          .SetWarnings True
      End With
  
  End Sub
  </code></pre>
  
  <p><strong>Now for the problem code (or at least where I think the problem is):</strong><br>
  When a new team is added to the combo-box it is inserted into the <em>Teams</em> table and the various roles are also added to the <em>Team_Composition</em> table.  This works - I can open the table and see the records in there, but the subform refuses to update and show the new records.  The database ID is showing 1.  The record count at the bottom of the form shows record <code>1 of 6</code> even though this is the 7th record I've added - the <em>Teams</em> table shows 7 records and the <em>Team_Composition</em> table shows that the roles have been added to Team ID 7.
  <a href="https://i.stack.imgur.com/VVZrZ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/VVZrZ.png" alt="enter image description here"></a></p>
  
  <p>The VBA to add a new team is below:  </p>
  
  <pre><code>Private Sub cmbTeamName_NotInList(NewData As String, Response As Integer)
      With DoCmd
          .SetWarnings False
          If cmbTeamName.OldValue = 0 Then
              'A new team needs adding to the Team table.
              .RunSQL "INSERT INTO Teams(TeamName) VALUES ('" &amp; NewData &amp; "')"
              Response = acDataErrAdded
              'The job roles for the team are inserted.
              Update_TeamComposition NewData
          Else
              .RunSQL "UPDATE Teams SET TeamName = '" &amp; NewData &amp; "'" &amp; _
                      "WHERE TeamID = " &amp; cmbTeamName.Column(0)
              Response = acDataErrAdded
          End If
          .SetWarnings True
      End With
  End Sub
  </code></pre>
  
  <p>I've tried adding code just before the <code>Else</code> statement to refresh the form - <code>Me.Refresh</code>, <code>Me.Requery</code>, <code>Me.Repaint</code>. </p>
  
  <p><code>Me.Requery</code>and <code>Me.Refresh</code> causes the NotInList code to run multiple times and eventually gives <code>run-time 2237 - The text you entered isn't an item in the list</code> (on the <code>Me.</code> line).
  <code>Me.Repaint</code> doesn't appear to do anything.</p>
  
  <p>I think I've included everything - does anyone know how I can get the subform to populate with roles when a new team is added? To me it looks like the table indexes aren't updated and the form doesn't recognise a new record has been created.  </p>
  
  <p><strong>Edit:</strong><br>
  After advice from @June7 I've updated my <code>NotInList</code> code to:</p>
  
  <pre><code>Private Sub cmbTeamName_NotInList(NewData As String, Response As Integer)
      With DoCmd
          .SetWarnings False
          If Me.cmbTeamName.OldValue = 0 Then
              'A new team needs adding to the Team table.
              .RunSQL "INSERT INTO Teams(TeamName) VALUES ('" &amp; NewData &amp; "')"
              Response = acDataErrAdded
              'The job roles for the team are inserted.
              Update_TeamComposition NewData
  
              'To stop the Requery from making NotInList fire multiple times
              'the combo box is moved to a team that does exist before the requery.
              'Then it can move to the new record.
              Me.cmbTeamName = Me.cmbTeamName.ItemData(0)
              Me.Requery
  
              Dim rs As DAO.Recordset
              Set rs = Me.RecordsetClone
              rs.FindFirst "[TeamName]='" &amp; NewData &amp; "'"
              Me.Recordset.Bookmark = rs.Bookmark
              rs.Close
              Set rs = Nothing
  
              Me.cmbTeamName.Requery
              Me.cmbTeamName = CLng(Me.txtTeamID)
  
          Else
              .RunSQL "UPDATE Teams SET TeamName = '" &amp; NewData &amp; "'" &amp; _
                      "WHERE TeamID = " &amp; Me.cmbTeamName.OldValue
              Response = acDataErrAdded
          End If
          .SetWarnings True
      End With
  End Sub
  </code></pre>
  
  <p>I've also updated the SQL for the subform by removing the <code>WHERE</code> clause allowing the form to make use of the Master/Child link.</p>