title: VBA Error in Access: "The cammand or action 'RecordsGoToNew' isn't available now"
body: <p>I would like to apologize in advance. The form I am dealing with is very complicated with many moving parts so I am sorry it my explanations aren't clear.</p>
  
  <p>I have a form that contains many subforms. One particular subform, fAdminToolsDetails, has a subform of its own:sfAdminToolsYearly. Depending on the state of the overall form, different queries and tables will populate the forms with information. I designed the states with use of an enum . Depending on which enum value is passed into the sub the state will change and the appropriate record sources, control sources, and so on are updated. However, the previously mentioned form, sfAdminToolsYearly, does not update correctly. Its information still reflects that of the previous state until a user interacts with its parent: fAdminToolsDetails.</p>
  
  <p>I personally this this is fine. However, my client is concerned this might cause some confusion. In order to rectify this issue I attempted to tell the form to go to a new record, just to clear out all the data as well as create a new option or record addition. However, I receive the error in the title for all states except DEPARTMENT. </p>
  
  <p>I have been looking at this all day and can't seem to find the root cause of the error. Please look at my code below and see if you can help. The error is initially thrown on the very last line.</p>
  
  <p>Please let me know if there are any questions about the code. I will be happy to answer them.</p>
  
  <p>Thank You</p>
  
  <pre><code>Private Function changeState(ByVal state As EnumState, ByVal CrrntId As Long)
  
  Dim prevSub As String
  Dim level As String
  Dim lvlSub As String
  Dim prevLvl As String
  Dim fYrIndex As Integer
  Dim crrntDeptIndex As Integer
  Dim crrntDivIndex As Integer
  Dim crrntSDivIndex As Integer
  Dim crrntSSDivIndex As Integer
  Dim crrntSSSDivIndex As Integer
  
  Me![crrntDept].SetFocus
  crrntDeptIndex = Me![crrntDept].ListIndex
  
  Me![crrntDiv].SetFocus
  crrntDivIndex = Me![crrntDiv].ListIndex
  
  Me![crrntSDiv].SetFocus
  crrntSDivIndex = Me![crrntSDiv].ListIndex
  
  Me![crrntSSDiv].SetFocus
  crrntSSDivIndex = Me![crrntSSDiv].ListIndex
  
  Me![crrntSSSDiv].SetFocus
  crrntSSSDivIndex = Me![crrntSSSDiv].ListIndex
  
  crrntState = state
  
  Dim selectStatement As String
  
  Select Case state
  
      Case DEPARTMENT
  
          level = "Department"
          lvlSub = "Dept"
          prevLvl = vbNullString
          prevSub = vbNullString
  
  
  
  
  
      Case DIVISION
  
          level = "Division"
          lvlSub = "Div"
          prevSub = "Dept"
          prevLvl = "Department"
  
          Me.fAdminToolsDetails.Form.sfAdminToolsYearly![sectionNumber].ControlSource = "DivYearly_SectionNum"
          Me.fAdminToolsDetails.Form.sfAdminToolsYearly![SectionNumberLabel].Caption = "Division Number"
          Me.fAdminToolsDetails.Form.sfAdminToolsYearly![tbNumbersPages].Visible = True
  
  
          If CrrntId = -1 Then
              CrrntId = Me!Dept_ID
  
          End If
  
      Case SUBDIVISION
              level = "SubDivision"
              lvlSub = "SubDiv"
              prevSub = "Div"
              prevLvl = "Division"
  
              Me.fAdminToolsDetails.Form.sfAdminToolsYearly![sectionNumber].ControlSource = "SubDivYearly_ParaNum"
              Me.fAdminToolsDetails.Form.sfAdminToolsYearly![SectionNumberLabel].Caption = "Subdivision Letter"
  
              If CrrntId = -1 Then
                   CrrntId = Me!Div_Id
  
              End If
  
       Case SUBSUBDIVISION
              level = "SubSubDivision"
              lvlSub = "SubSubDiv"
              prevSub = "SubDiv"
              prevLvl = "SubDivision"
  
              Me.fAdminToolsDetails.Form.sfAdminToolsYearly![sectionNumber].ControlSource = "SubSubDivYearly_SubParaNum"
              Me.fAdminToolsDetails.Form.sfAdminToolsYearly![SectionNumberLabel].Caption = "SubSubdivision Number"
  
              If CrrntId = -1 Then
                   CrrntId = Me!SubDiv_ID
  
              End If
  
      Case SUBSUBSUBDIVISION
              level = "SubSubSubDivision"
              lvlSub = "SubSubSubDiv"
              prevSub = "SubSubDiv"
              prevLvl = "SubSubDivision"
  
              Me.fAdminToolsDetails.Form.sfAdminToolsYearly![sectionNumber].ControlSource = "SubSubSubDivYearly_SubSectionNum"
              Me.fAdminToolsDetails.Form.sfAdminToolsYearly![SectionNumberLabel].Caption = "SubSubSubdivision Letter"
  
              If CrrntId = -1 Then
                   CrrntId = Me!SubSubDiv_ID
  
              End If
  
  End Select
  
  Me![lvlSub].ControlSource = "='" &amp; lvlSub &amp; "'"
  
  Me.sfListOfLvlData.LinkChildFields = vbNullString
  Me.sfListOfLvlData.LinkMasterFields = vbNullString
  
  Me.fAdminToolsDetails.LinkMasterFields = vbNullString
  Me.fAdminToolsDetails.LinkChildFields = vbNullString
  
  If state &lt;&gt; DEPARTMENT Then
  
      Me.RecordSource = "SELECT DISTINCT " &amp; prevLvl &amp; "." &amp; prevSub &amp; "_ID FROM " &amp; prevLvl &amp; " WHERE " &amp; prevLvl &amp; "." &amp; prevSub &amp; "_ID = " &amp; CrrntId
      Me.sfListOfLvlData.Form.RecordSource = "SELECT DISTINCT " &amp; level &amp; "." &amp; lvlSub &amp; "_ID, " &amp; level &amp; "." &amp; lvlSub &amp; "_Short_Name, " &amp; level &amp; "Yearly." &amp; prevSub &amp; "_ID FROM " &amp; level &amp; " " &amp; _
      "INNER JOIN " &amp; level &amp; "Yearly ON " &amp; level &amp; "." &amp; lvlSub &amp; "_ID=" &amp; level &amp; "Yearly." &amp; lvlSub &amp; "_ID" &amp; " ORDER BY " &amp; level &amp; "." &amp; lvlSub &amp; "_Short_Name"
      Me.sfListOfLvlData.LinkChildFields = prevSub &amp; "_ID"
      Me.sfListOfLvlData.LinkMasterFields = prevSub &amp; "_ID"
  Else
  
  
      Me.sfListOfLvlData.Form.RecordSource = "SELECT " &amp; level &amp; "." &amp; lvlSub &amp; "_ID, " &amp; level &amp; "." &amp; lvlSub &amp; "_Short_Name FROM " &amp; level &amp; " ORDER BY " &amp; lvlSub &amp; "_Short_Name"
      Me.sfListOfLvlData.LinkChildFields = vbNullString
      Me.RecordSource = level
      Me.sfListOfLvlData.LinkMasterFields = vbNullString
  
  End If
  
  
  
  
  Me![Title].ControlSource = "='" &amp; level &amp; "'"
  Me.sfListOfLvlData.Form![ShortName].ControlSource = lvlSub &amp; "_Short_Name"
  Me![parentId].ControlSource = prevSub &amp; "_ID"
  
  Me.fAdminToolsDetails.Form.sfAdminToolsYearly![Yearly_Year].SetFocus
  
  If Not state = DIVISION Then
  
      Me.fAdminToolsDetails.Form.sfAdminToolsYearly![tbNumbersPages].Visible = False
  
  End If
  
  
  If Not state = DEPARTMENT Then
  
  
  
  
  
  
  
      Me.fAdminToolsDetails![COFRSCode].Visible = False
      Me.fAdminToolsDetails![functArea].Visible = False
      Me.fAdminToolsDetails![chbOperatingBudget].Visible = False
      Me.fAdminToolsDetails![chbCapitalBudget].Visible = False
  
      Me.fAdminToolsDetails.Form.sfAdminToolsYearly![sectionNumber].Visible = True
      Me.fAdminToolsDetails.Form.sfAdminToolsYearly![tabDirectors].Visible = False
      Me.fAdminToolsDetails.Form.sfAdminToolsYearly![Parent].Visible = True
      Me.fAdminToolsDetails.Form.sfAdminToolsYearly![Parent].RowSource = "SELECT " &amp; prevLvl &amp; "." &amp; prevSub &amp; "_Short_Name, " &amp; prevLvl &amp; "." &amp; prevSub &amp; "_ID " &amp; _
                                                                              "FROM " &amp; prevLvl &amp; " " &amp; _
                                                                              "ORDER BY " &amp; prevLvl &amp; "." &amp; prevSub &amp; "_Short_Name"
      Me.fAdminToolsDetails.Form.sfAdminToolsYearly![Parent].ControlSource = prevSub &amp; "_ID"
  
  
  
  
  Else
  
      Me.fAdminToolsDetails![COFRSCode].Visible = True
      Me.fAdminToolsDetails![functArea].Visible = True
      Me.fAdminToolsDetails![chbOperatingBudget].Visible = True
      Me.fAdminToolsDetails![chbCapitalBudget].Visible = True
  
      Me.fAdminToolsDetails.Form.sfAdminToolsYearly![tabDirectors].Visible = True
      Me.fAdminToolsDetails.Form.sfAdminToolsYearly![Parent].Visible = False
      Me.fAdminToolsDetails.Form.sfAdminToolsYearly![sectionNumber].Visible = False
  
  End If
  Me.mainID.ControlSource = "=[sfListOfLvlData]![" &amp; lvlSub &amp; "_ID]"
  
  Me.fAdminToolsDetails.Form.RecordSource = level
  Me.fAdminToolsDetails.LinkMasterFields = "mainID"
  Me.fAdminToolsDetails.LinkChildFields = lvlSub &amp; "_ID"
  Me.fAdminToolsDetails![Name].ControlSource = lvlSub &amp; "_Name"
  Me.fAdminToolsDetails![Short_Name].ControlSource = lvlSub &amp; "_Short_Name"
  Me.fAdminToolsDetails![Cd].ControlSource = lvlSub &amp; "_Cd"
  Me.fAdminToolsDetails![Active].ControlSource = lvlSub &amp; "_Active"
   Me.fAdminToolsDetails![Id].ControlSource = lvlSub &amp; "_ID"
  
  Me.fAdminToolsDetails![lstItemByYear].RowSource = "SELECT " &amp; level &amp; "Yearly." &amp; lvlSub &amp; "Yearly_Id, " &amp; level &amp; "Yearly." &amp; lvlSub &amp; "Yearly_Year" &amp; _
                                                      " FROM " &amp; level &amp; "Yearly" &amp; _
                                                      " WHERE (((" &amp; level &amp; "Yearly." &amp; lvlSub &amp; "_ID) = [Forms]![testAdminForm]![mainID])) " &amp; _
                                                      "ORDER BY " &amp; level &amp; "Yearly." &amp; lvlSub &amp; "Yearly_Year"
  
  
  Me.fAdminToolsDetails.Form.sfAdminToolsYearly.Form.RecordSource = level &amp; "Yearly"
  Me.fAdminToolsDetails.Form.sfAdminToolsYearly.LinkMasterFields = "Yearly_Id"
  Me.fAdminToolsDetails.Form.sfAdminToolsYearly.LinkChildFields = lvlSub &amp; "Yearly_ID"
  
  Me.fAdminToolsDetails.Form.sfAdminToolsYearly![Yearly_Id].ControlSource = lvlSub &amp;          "Yearly_ID"
  Me.fAdminToolsDetails.Form.sfAdminToolsYearly![Parent_Id].ControlSource = lvlSub &amp; "_ID"
  Me.fAdminToolsDetails.Form.sfAdminToolsYearly![Yearly_Year].ControlSource = lvlSub &amp; "Yearly_Year"
  Me.fAdminToolsDetails.Form.sfAdminToolsYearly![Yearly_Order].ControlSource = lvlSub &amp; "Yearly_Order"
  Me.fAdminToolsDetails.Form.sfAdminToolsYearly![Yearly_Name].ControlSource = lvlSub &amp; "Yearly_Name"
  Me.fAdminToolsDetails.Form.sfAdminToolsYearly![Yearly_FDesc].ControlSource = lvlSub &amp; "Yearly_FDesc"
  Me.fAdminToolsDetails.Form.sfAdminToolsYearly![Yearly_SDesc].ControlSource = lvlSub &amp; "Yearly_SDesc"
  
  
  Me![crrntDept].SetFocus
  Me.crrntDept = Me.crrntDept.ItemData(crrntDeptIndex)
  
  Me![crrntDiv].SetFocus
  Me.crrntDiv = Me.crrntDiv.ItemData(crrntDivIndex)
  
  Me![crrntSDiv].SetFocus
  Me.crrntSDiv = Me.crrntSDiv.ItemData(crrntSDivIndex)
  
  Me![crrntSSDiv].SetFocus
  Me.crrntSSDiv = Me.crrntSSDiv.ItemData(crrntSSDivIndex)
  
  Me.fAdminToolsDetails.Form.sfAdminToolsYearly.SetFocus
  Me.fAdminToolsDetails.Form.sfAdminToolsYearly.Form.AllowAdditions = True
  ' Me.fAdminToolsDetails.Form.sfAdminToolsYearly.Form!Yearly_Order.SetFocus
  
  DoCmd.RunCommand acCmdRecordsGoToNew
  </code></pre>
  
  <p>End Function</p>