title: Close current form while opening another in MS Access
body: <p>I need to have one form open another, and immediately close. Please note that my forms have an onTimer event procedure that happens every two seconds. I am aware of the following commands:</p>
  
  <pre><code>    DoCMD Close
  </code></pre>
  
  <p>and </p>
  
  <pre><code>    DoCMD OpenForm "frmOnError"
  </code></pre>
  
  <p>and they both work individually, but when I do the following:</p>
  
  <pre><code>    DoCMD OpenForm "frmOnError"
      DoCMD Close
  </code></pre>
  
  <p>Then the form just flashes, and does not close. The form it is opening is not itself, it is the name of the form I want opened. Again, if I comment out either of those lines, it does what the remaining line says. </p>
  
  <p>Next, I tried to use a DoCMD OpenForm on the first form, and I had an if statement in the other form that would close the first if it was open. This worked chaotically as both forms had the same command, and whichever one would beat the other to the command would execute it. This meant that I had no way of telling which form will close. </p>
  
  <p>My final variation was to use a DoCMD OpenForm command in a button_clicked sub, and lower in the code, in the Form_Timer section, I had the DoCMD close command. Instead of closing the other form once it loaded, it would close itself once the other form loaded. This was more stable, but still chaotic.</p>
  
  <p>With this, I'm out of ideas. </p>
  
  <p>Description of code:</p>
  
  <p>The point of the code is to close the first form, which depends on linked tables and errors out when the connection is lost. This would open the second form which does not rely on linked connections and continues to work. Then, the new form would have a button that closes it and attempts to load the first form again. Both of the forms rely on 2-sec timers to simulate constant random data from PLCs, and will continue to require the timers indefinitely. </p>
  
  <p>Finally, here are the segments of the code in the order that they should work:</p>
  
  <p>frmLoop:</p>
  
  <pre><code>Public Sub DoSQL4(vFacility As String, vWorkcell As Integer, vStation As Integer, vEventcode As Integer, vFacilityPath, vFacilityID, vFaultCodeID, vStateCode As Integer)
  
  On Error GoTo ErrorHandler4
  
  Const conLINKED_TABLE As String = "tblevent"
  
  'INSERT INTO LINKED TABLE
  CurrentDb.TableDefs(conLINKED_TABLE).RefreshLink
  
  DoCmd.SetWarnings False
  DoCmd.RunSQL "INSERT INTO tblevent (vchrFacility, intWorkCell, intStn, intEventCode) VALUES ('" &amp; vFacility &amp; "', '" &amp; vWorkcell &amp; "', '" &amp; vStation &amp; "', '" &amp; vEventcode &amp; "');"
  DoCmd.RunSQL "INSERT INTO tblfaultdata (vchrFacilityPath, intFacilityID, intFaultCodeID, intWorkcell) VALUES ('" &amp; vFacilityPath &amp; "', '" &amp; vFacilityID &amp; "', '" &amp; vFaultCodeID &amp; "', '" &amp; vWorkcell &amp; "');"
  DoCmd.RunSQL "INSERT INTO tblstate (vchrFacility, intWorkCell, intStn, intStateCode) VALUES ('" &amp; vFacility &amp; "', '" &amp; vWorkcell &amp; "', '" &amp; vStation &amp; "', '" &amp; vStateCode &amp; "');"
  DoCmd.SetWarnings True
  
  'INSERT INTO LOCAL TABLE
  
  DoCmd.SetWarnings False
  DoCmd.RunSQL "INSERT INTO Local_tblevent (vchrFacility, intWorkCell, intStn, intEventCode) VALUES ('" &amp; vFacility &amp; "', '" &amp; vWorkcell &amp; "', '" &amp; vStation &amp; "', '" &amp; vEventcode &amp; "');"
  DoCmd.RunSQL "INSERT INTO Local_tblfaultdata (vchrFacilityPath, intFacilityID, intFaultCodeID, intWorkcell) VALUES ('" &amp; vFacilityPath &amp; "', '" &amp; vFacilityID &amp; "', '" &amp; vFaultCodeID &amp; "', '" &amp; vWorkcell &amp; "');"
  DoCmd.RunSQL "INSERT INTO Local_tblstate (vchrFacility, intWorkCell, intStn, intStateCode) VALUES ('" &amp; vFacility &amp; "', '" &amp; vWorkcell &amp; "', '" &amp; vStation &amp; "', '" &amp; vStateCode &amp; "');"
  DoCmd.SetWarnings True
  
  Exit_theSub:
  
    DoCmd.OpenForm "frmOnError"
  
    If CurrentProject.AllForms("frmOnError").IsLoaded And Forms!frmOnError.CurrentView &lt;&gt; acCurViewDesign Then
    DoCmd.Close
    End If
  
  'ERROR HANDLER
  ErrorHandler4:
  
  If Err.Number &lt;&gt; 0 Then
    Select Case Err.Number
      Case 3265
        MsgBox "[" &amp; conLINKED_TABLE &amp; "] does not exist as either an Internal or Linked Table", _
               vbCritical, "Table Missing"
      Case 3011, 3024     'Linked Table does not exist or DB Path not valid
        MsgBox "[" &amp; conLINKED_TABLE &amp; "] is not a valid, Linked Table", vbCritical, "Link Not Valid"
      Case Else
        MsgBox Err.Description &amp; Err.Number, vbExclamation, "Error"
    End Select
  
  Resume Exit_theSub
  
  End If
  
  End Sub
  </code></pre>
  
  <p>Then frmOnError:</p>
  
  <pre><code>Private Sub btnRetry_Click()
  
  DoCmd.OpenForm "frmLoop"
  
  End Sub
  
  Private Sub Form_Timer()
  
    If CurrentProject.AllForms("frmOnError").IsLoaded And Forms!frmOnError.CurrentView &lt;&gt; acCurViewDesign Then
    DoCmd.Close
    End If
  </code></pre>
  
  <p>etc.</p>
  
  <p>They basically bounce back and fourth. As soon as one is closed by the error handler, it opens the other form, then closes itself. If the button is pressed on the other form, it opens the first, then closes itself. </p>
  
  <p>That is the theory. Something in my code is horribly wrong, and I was hoping one of you guys could spot it and point it out. Thanks!</p>
  
  <p><strong>Edit:</strong> I updated my code in response to a user's answer, and replaced DoCmd.RunSql with CurrentDb.Execute, dbfailonerror. Now my form closes right away when it is opened, and returns no error.</p>
  
  <pre><code>'INSERT INTO LINKED TABLE
  CurrentDb.TableDefs(conLINKED_TABLE).RefreshLink
  
  CurrentDb.Execute "INSERT INTO tblevent (vchrFacility, intWorkCell, intStn, intEventCode) VALUES ('" &amp; vFacility &amp; "', '" &amp; vWorkcell &amp; "', '" &amp; vStation &amp; "', '" &amp; vEventcode &amp; "');", dbFailOnError
  CurrentDb.Execute "INSERT INTO tblfaultdata (vchrFacilityPath, intFacilityID, intFaultCodeID, intWorkcell) VALUES ('" &amp; vFacilityPath &amp; "', '" &amp; vFacilityID &amp; "', '" &amp; vFaultCodeID &amp; "', '" &amp; vWorkcell &amp; "');", dbFailOnError
  CurrentDb.Execute "INSERT INTO tblstate (vchrFacility, intWorkCell, intStn, intStateCode) VALUES ('" &amp; vFacility &amp; "', '" &amp; vWorkcell &amp; "', '" &amp; vStation &amp; "', '" &amp; vStateCode &amp; "');", dbFailOnError
  
  'INSERT INTO LOCAL TABLE
  
  CurrentDb.Execute "INSERT INTO Local_tblevent (vchrFacility, intWorkCell, intStn, intEventCode) VALUES ('" &amp; vFacility &amp; "', '" &amp; vWorkcell &amp; "', '" &amp; vStation &amp; "', '" &amp; vEventcode &amp; "');", dbFailOnError
  CurrentDb.Execute "INSERT INTO Local_tblfaultdata (vchrFacilityPath, intFacilityID, intFaultCodeID, intWorkcell) VALUES ('" &amp; vFacilityPath &amp; "', '" &amp; vFacilityID &amp; "', '" &amp; vFaultCodeID &amp; "', '" &amp; vWorkcell &amp; "');", dbFailOnError
  CurrentDb.Execute "INSERT INTO Local_tblstate (vchrFacility, intWorkCell, intStn, intStateCode) VALUES ('" &amp; vFacility &amp; "', '" &amp; vWorkcell &amp; "', '" &amp; vStation &amp; "', '" &amp; vStateCode &amp; "');", dbFailOnError
  
  Exit_theSub:
  
    DoCmd.Close
  
  'ERROR HANDLER
  ErrorHandler4:
  
  If Err.Number &lt;&gt; 0 Then
    Select Case Err.Number
      Case 3265
        MsgBox "[" &amp; conLINKED_TABLE &amp; "] does not exist as either an Internal or Linked Table", _
               vbCritical, "Table Missing"
               DoCmd.OpenForm "frmOnError"
      Case 3011, 3024     'Linked Table does not exist or DB Path not valid
        MsgBox "[" &amp; conLINKED_TABLE &amp; "] is not a valid, Linked Table", vbCritical, "Link Not Valid"
        DoCmd.OpenForm "frmOnError"
      Case Else
        MsgBox Err.Description &amp; Err.Number, vbExclamation, "Error"
        DoCmd.OpenForm "frmOnError"
    End Select
  
  Resume Exit_theSub
  
  End If
  </code></pre>
  
  <p>End Sub</p>