Why won't this form close, Access 2000 VBA 2

[vamoose]

Using this exact code to update a SQL database. Everything works fine until I try to close this frm_coils_add_new form using the DoCmd.Close command. The error states: "This action can't be carried out while processing a form or report event." No matter what I try to do it will not close automatically but I can close it using the X in the top right hand corner if I remove the DoCmd.Close command. The Text Box SC is the last data entry point on the form and when it is exited the below code runs. Any ideas ?

Private Sub SC_Exit(Cancel As Integer)
Dim stDocName, stLinkCriteria As String: Entered = Now()
Dim CN As ADODB.Connection: Dim rs As ADODB.Recordset
SQLa = "Insert Into CoilSpec.dbo.coilspec (Coil,StartLeft,TT,TPL,BDim,RPM,[Cross]," & _
"Pick1,Pick2,Pick3,Pick4,Pick5,Pick6,Pick7,Pick8,Pick9,Pick10,SpecialComments,Entered) values" & _
"(" & cmbWCoilNo & "," & Toggle112 & "," & TT & "," & TPL & "," & BDim & "," & RPM & "," & [Cross] & _
"," & P1 & "," & P2 & "," & P3 & "," & P4 & "," & P5 & "," & P6 & "," & P7 & "," & P8 & "," & P9 & _
"," & P10 & ", '" & SC & "' , '" & Entered & "' );"
Set CN = New ADODB.Connection: CN.Open "driver={SQL Server};" & _
"server=172.16.6.172;database=coilspec;uid=dtuttle;password=mpmadmin"
Set rs = CN.Execute(SQLa, , adCmdText)
CN.Close: Set rs = Nothing: Set CN = Nothing
MsgBox "Coil entry successfully saved"
DoCmd.Close

End Sub

 

[ThomasLafferty]

Pass control of your close event to a globally stored procedure. That way the last thing your event did was surrender control and has no more events to process.

Public Sub frmClose()
     DoCmd.Close acForm, "[!]YourFormNameHere[/!]", acSavePrompt
End Sub

Add a call to your new procedure like this (in red)

Private Sub SC_Exit(Cancel As Integer)
Dim stDocName, stLinkCriteria As String: Entered = Now()
    Dim CN As ADODB.Connection: Dim rs As ADODB.Recordset
    SQLa = "Insert Into CoilSpec.dbo.coilspec (Coil,StartLeft,TT,TPL,BDim,RPM,[Cross]," & _
        "Pick1,Pick2,Pick3,Pick4,Pick5,Pick6,Pick7,Pick8,Pick9,Pick10,SpecialComments,Entered) values" & _
        "(" & cmbWCoilNo & "," & Toggle112 & "," & TT & "," & TPL & "," & BDim & "," & RPM & "," & [Cross] & _
        "," & P1 & "," & P2 & "," & P3 & "," & P4 & "," & P5 & "," & P6 & "," & P7 & "," & P8 & "," & P9 & _
        "," & P10 & ", '" & SC & "' , '" & Entered & "' );"
    Set CN = New ADODB.Connection: CN.Open "driver={SQL Server};" & _
        "server=172.16.6.172;database=coilspec;uid=dtuttle;password=mpmadmin"
    Set rs = CN.Execute(SQLa, , adCmdText)
    CN.Close: Set rs = Nothing: Set CN = Nothing
    MsgBox "Coil entry successfully saved"
    [!]frmClose[/!]
       
End Sub

Born once die twice; born twice die once.

 

[vamoose]

I did what you suggested and it still produces the same results. I don't understand what it is trying to process, everything appears to be completed and the form just won't close.

 

[ThomasLafferty]

Hmmm...How is this event called?

Private Sub SC_Exit(Cancel As Integer)

Is there another form event which is running in the background?

Tom

Born once die twice; born twice die once.

 

[vamoose]

Also, possiblly noteworthy. I had to remove the statement rs.close because it produces the error: "Operation is not allowed when the object is closed." How did this get closed, I did not tell it to close, or is this typically closed after doing an Insert Into statement ? Thanks to all.

 

[vamoose]

The event is triggered by tabbing out of or pressing enter out of a text box called SC. Yes there is another form in the background.

 

[ThomasLafferty]

Can you elaborate on this a bit more?

The Text Box SC is the last data entry point on the form and [!]when it is exited[/!] the below code runs[/code]

What does the [!]it[/!] refer to?

Tom

Born once die twice; born twice die once.

 

[vamoose]

The it = the text box itself.
Private Sub SC_Exit(Cancel As Integer) is the text box SC "On Exit property" set on the form.
There are 20 data entry points on the form "frm_coils_add_new. The last and final data entry point is a text box called SC. The user either enters some text here or simply tabs out of this text box. By Exiting the SC text box triggers the event Private Sub SC_Exit(Cancel As Integer) to run it's code and the Insert Into SQL statement. There is a form running in the background but it doesn't have to be there.

 

[ThomasLafferty]

Try commenting off all code but the frmClose call. Does the form close? If so, then the trouble may be in the code preceding the frmClose call.

Tom

Born once die twice; born twice die once.

 

[JerryKlmns]

Using the Execute method on a Connection object executes whatever query you pass to the method in the CommandText argument on the specified connection. If the CommandText argument specifies a row-returning query, any results that the execution generates are stored in a new Recordset object. If the command is not intended to return results (for example, an SQL UPDATE query) the provider returns Nothing as long as the option adExecuteNoRecords is specified; otherwise Execute returns a closed Recordset.

I think this clears things. Oh, and you don't need the recordset at all!

You could try,
Cn.BeginTrans
Cn.Execute SQLa, , adCmdText + adExecuteNoRecords
Cn.CommitTrans