https://forums.autodesk.com/t5/vba-forum/this-routine-crashes-every-other-time/m-p/915846#M58302 Hi I hope someone can help me out, because I'm pulling my hair out. Every other time is run it crashes out at the highlighted line (>>) with a Runtime error: Microsoft ODBC Excel driver Invalid SQL statement, expects Delete, Insert etc... the ListBox1.Value returned is "". What is going on? Is it something to do withe the connection not closing properly? I have a copy of the routine that I saved a week ago, which uses the same code & works just fine??? TIA Dave F. Option Explicit Dim rs As ADODB.Recordset Dim dbConnection As ADODB.Connection Dim tarray As Variant Dim StlType As String Sub UserForm_Initialize() Dim dbConnectionString As String Dim RegData As String ListBox1.List() = Array("ub", "uc", "shs", "rhs", "chs", "rsae", "rsau", "pfc", "ubp") dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DBQ=c:\dwgs\fx15\fxstructsect.xls" Set dbConnection = New ADODB.Connection dbConnection.Open dbConnectionString ' open the database connection If GetSetting("fxsteel", "baa_bh", 1) = "baa" Then OptButBAA.Value = True Else OptButBH.Value = True End If ListBox1.ListIndex = 0 ' ub - goes to listbox1_change End Sub Sub listbox1_Change() Dim r As Long >> Set rs = dbConnection.Execute("[" & ListBox1.Value & "]") tarray = rs.GetRows CboSection.Enabled = True CboPlan.Enabled = True CboElevation.Enabled = True Select Case ListBox1.Value Case "rsae", "rsau", "pfc" FraFront_Back.OptButFront.Enabled = True FraFront_Back.OptButBack.Enabled = True Case Else FraFront_Back.OptButFront.Enabled = False FraFront_Back.OptButBack.Enabled = False End Select With ListBox2 .Clear For r = 0 To UBound(tarray, 2) .AddItem tarray(0, r) ' col, row ? Next r .ListIndex = -1 ' no item selected End With End Sub Private Sub CboElevation_Click() If ListBox2.ListIndex = -1 Then MsgBox ("No size highlighted") Else Me.Hide rs.Close dbConnection.Close ' close the database connection DrawElev tarray ' Unload Me End If End Sub Sub CboSection_Click() If ListBox2.ListIndex = -1 Then MsgBox ("No size highlighted") Else Me.Hide rs.Close dbConnection.Close ' close the database connection ' THIS GO IN TO OWN SUB FOR ALL CLICKS (& ABOVE)? If OptButBH.Value = True Then SaveSetting "fxsteel", "baa_bh", 1, "bh" Else SaveSetting "fxsteel", "baa_bh", 1, "baa" End If DrawSect tarray ' Unload Me End If End Sub Sub CboPlan_Click() If ListBox2.ListIndex = -1 Then MsgBox ("No size highlighted") Else Me.Hide rs.Close dbConnection.Close ' close the database connection DrawPlan tarray ' Unload Me End If End Sub Sub ListBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean) Me.Hide rs.Close dbConnection.Close ' close the database connection DrawSect tarray ' Unload Me End Sub Sub CboCancel_Click() Me.Hide rs.Close dbConnection.Close ' close the database connection ' Unload Me ' is this the same as unload frmsteel in the module? End Sub Mon, 22 Dec 2003 18:56:15 GMT Anonymous 2003-12-22T18:56:15Z https://forums.autodesk.com/t5/vba-forum/this-routine-crashes-every-other-time/m-p/915846#M58302 Hi I hope someone can help me out, because I'm pulling my hair out. Every other time is run it crashes out at the highlighted line (>>) with a Runtime error: Microsoft ODBC Excel driver Invalid SQL statement, expects Delete, Insert etc... the ListBox1.Value returned is "". What is going on? Is it something to do withe the connection not closing properly? I have a copy of the routine that I saved a week ago, which uses the same code & works just fine??? TIA Dave F. Option Explicit Dim rs As ADODB.Recordset Dim dbConnection As ADODB.Connection Dim tarray As Variant Dim StlType As String Sub UserForm_Initialize() Dim dbConnectionString As String Dim RegData As String ListBox1.List() = Array("ub", "uc", "shs", "rhs", "chs", "rsae", "rsau", "pfc", "ubp") dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DBQ=c:\dwgs\fx15\fxstructsect.xls" Set dbConnection = New ADODB.Connection dbConnection.Open dbConnectionString ' open the database connection If GetSetting("fxsteel", "baa_bh", 1) = "baa" Then OptButBAA.Value = True Else OptButBH.Value = True End If ListBox1.ListIndex = 0 ' ub - goes to listbox1_change End Sub Sub listbox1_Change() Dim r As Long >> Set rs = dbConnection.Execute("[" & ListBox1.Value & "]") tarray = rs.GetRows CboSection.Enabled = True CboPlan.Enabled = True CboElevation.Enabled = True Select Case ListBox1.Value Case "rsae", "rsau", "pfc" FraFront_Back.OptButFront.Enabled = True FraFront_Back.OptButBack.Enabled = True Case Else FraFront_Back.OptButFront.Enabled = False FraFront_Back.OptButBack.Enabled = False End Select With ListBox2 .Clear For r = 0 To UBound(tarray, 2) .AddItem tarray(0, r) ' col, row ? Next r .ListIndex = -1 ' no item selected End With End Sub Private Sub CboElevation_Click() If ListBox2.ListIndex = -1 Then MsgBox ("No size highlighted") Else Me.Hide rs.Close dbConnection.Close ' close the database connection DrawElev tarray ' Unload Me End If End Sub Sub CboSection_Click() If ListBox2.ListIndex = -1 Then MsgBox ("No size highlighted") Else Me.Hide rs.Close dbConnection.Close ' close the database connection ' THIS GO IN TO OWN SUB FOR ALL CLICKS (& ABOVE)? If OptButBH.Value = True Then SaveSetting "fxsteel", "baa_bh", 1, "bh" Else SaveSetting "fxsteel", "baa_bh", 1, "baa" End If DrawSect tarray ' Unload Me End If End Sub Sub CboPlan_Click() If ListBox2.ListIndex = -1 Then MsgBox ("No size highlighted") Else Me.Hide rs.Close dbConnection.Close ' close the database connection DrawPlan tarray ' Unload Me End If End Sub Sub ListBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean) Me.Hide rs.Close dbConnection.Close ' close the database connection DrawSect tarray ' Unload Me End Sub Sub CboCancel_Click() Me.Hide rs.Close dbConnection.Close ' close the database connection ' Unload Me ' is this the same as unload frmsteel in the module? End Sub Mon, 22 Dec 2003 18:56:15 GMT https://forums.autodesk.com/t5/vba-forum/this-routine-crashes-every-other-time/m-p/915846#M58302 Anonymous 2003-12-22T18:56:15Z