Close current form while opening another in MS Access

Question

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:

    DoCMD Close

and

    DoCMD OpenForm "frmOnError"

and they both work individually, but when I do the following:

    DoCMD OpenForm "frmOnError"
    DoCMD Close

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.

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.

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.

With this, I'm out of ideas.

Description of code:

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.

Finally, here are the segments of the code in the order that they should work:

frmLoop:

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 ('" & vFacility & "', '" & vWorkcell & "', '" & vStation & "', '" & vEventcode & "');"
DoCmd.RunSQL "INSERT INTO tblfaultdata (vchrFacilityPath, intFacilityID, intFaultCodeID, intWorkcell) VALUES ('" & vFacilityPath & "', '" & vFacilityID & "', '" & vFaultCodeID & "', '" & vWorkcell & "');"
DoCmd.RunSQL "INSERT INTO tblstate (vchrFacility, intWorkCell, intStn, intStateCode) VALUES ('" & vFacility & "', '" & vWorkcell & "', '" & vStation & "', '" & vStateCode & "');"
DoCmd.SetWarnings True

'INSERT INTO LOCAL TABLE

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO Local_tblevent (vchrFacility, intWorkCell, intStn, intEventCode) VALUES ('" & vFacility & "', '" & vWorkcell & "', '" & vStation & "', '" & vEventcode & "');"
DoCmd.RunSQL "INSERT INTO Local_tblfaultdata (vchrFacilityPath, intFacilityID, intFaultCodeID, intWorkcell) VALUES ('" & vFacilityPath & "', '" & vFacilityID & "', '" & vFaultCodeID & "', '" & vWorkcell & "');"
DoCmd.RunSQL "INSERT INTO Local_tblstate (vchrFacility, intWorkCell, intStn, intStateCode) VALUES ('" & vFacility & "', '" & vWorkcell & "', '" & vStation & "', '" & vStateCode & "');"
DoCmd.SetWarnings True

Exit_theSub:

  DoCmd.OpenForm "frmOnError"

  If CurrentProject.AllForms("frmOnError").IsLoaded And Forms!frmOnError.CurrentView <> acCurViewDesign Then
  DoCmd.Close
  End If

'ERROR HANDLER
ErrorHandler4:

If Err.Number <> 0 Then
  Select Case Err.Number
    Case 3265
      MsgBox "[" & conLINKED_TABLE & "] 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 "[" & conLINKED_TABLE & "] is not a valid, Linked Table", vbCritical, "Link Not Valid"
    Case Else
      MsgBox Err.Description & Err.Number, vbExclamation, "Error"
  End Select

Resume Exit_theSub

End If

End Sub

Then frmOnError:

Private Sub btnRetry_Click()

DoCmd.OpenForm "frmLoop"

End Sub

Private Sub Form_Timer()

  If CurrentProject.AllForms("frmOnError").IsLoaded And Forms!frmOnError.CurrentView <> acCurViewDesign Then
  DoCmd.Close
  End If

etc.

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.

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!

Edit: 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.

'INSERT INTO LINKED TABLE
CurrentDb.TableDefs(conLINKED_TABLE).RefreshLink

CurrentDb.Execute "INSERT INTO tblevent (vchrFacility, intWorkCell, intStn, intEventCode) VALUES ('" & vFacility & "', '" & vWorkcell & "', '" & vStation & "', '" & vEventcode & "');", dbFailOnError
CurrentDb.Execute "INSERT INTO tblfaultdata (vchrFacilityPath, intFacilityID, intFaultCodeID, intWorkcell) VALUES ('" & vFacilityPath & "', '" & vFacilityID & "', '" & vFaultCodeID & "', '" & vWorkcell & "');", dbFailOnError
CurrentDb.Execute "INSERT INTO tblstate (vchrFacility, intWorkCell, intStn, intStateCode) VALUES ('" & vFacility & "', '" & vWorkcell & "', '" & vStation & "', '" & vStateCode & "');", dbFailOnError

'INSERT INTO LOCAL TABLE

CurrentDb.Execute "INSERT INTO Local_tblevent (vchrFacility, intWorkCell, intStn, intEventCode) VALUES ('" & vFacility & "', '" & vWorkcell & "', '" & vStation & "', '" & vEventcode & "');", dbFailOnError
CurrentDb.Execute "INSERT INTO Local_tblfaultdata (vchrFacilityPath, intFacilityID, intFaultCodeID, intWorkcell) VALUES ('" & vFacilityPath & "', '" & vFacilityID & "', '" & vFaultCodeID & "', '" & vWorkcell & "');", dbFailOnError
CurrentDb.Execute "INSERT INTO Local_tblstate (vchrFacility, intWorkCell, intStn, intStateCode) VALUES ('" & vFacility & "', '" & vWorkcell & "', '" & vStation & "', '" & vStateCode & "');", dbFailOnError

Exit_theSub:

  DoCmd.Close

'ERROR HANDLER
ErrorHandler4:

If Err.Number <> 0 Then
  Select Case Err.Number
    Case 3265
      MsgBox "[" & conLINKED_TABLE & "] 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 "[" & conLINKED_TABLE & "] is not a valid, Linked Table", vbCritical, "Link Not Valid"
      DoCmd.OpenForm "frmOnError"
    Case Else
      MsgBox Err.Description & Err.Number, vbExclamation, "Error"
      DoCmd.OpenForm "frmOnError"
  End Select

Resume Exit_theSub

End If

End Sub

Answer 1

The DoCmd.Close statement can also be used to specify which form you want to close:

DoCmd.Close acForm, "frmFormName"

If you use that it should resolve this problem for you.

Purely as a side note, I recommend using CurrentDb.Execute "SQL Statement", dbFailOnError instead of DoCmd.RunSQL. This way you won't have to mess with the warnings and the dbFailOnError flag will return a helpful error code when the insert statement fails to work.