Error 91 - With block or object variable not set.

[mdub]

Hi,

I am trying to run code to create an excel file based on what the user selects in a form's text box. When I try ro execute it, it gives me an error that says "91-object variable or with block variable not set."

I am assuming it is referring to the line of code that says:

"rsCheck.Open strSelect, gblDb, adOpenKeyset, adLockOptimistic"

When I change the code to

"With rsCheck
.Open
.ActiveConnection = gblDb
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
End With"

it gives me an error that says "3709 - The connection cannot be used to perform this operation. It is either closed or invalid in this context."
Any help is greatly appreciated!

Michael

Here is the code:

Dim appExcel As Excel.Application
Dim wbkExcel As Excel.Workbook
Dim wksExcel As Excel.Worksheet

Dim intRow As Integer
Dim strSelect As String
Dim strSQL As String
Dim myDB As Database

If gblDb.ConnectionString = "" Then
Call ConnectToECS
End If

Dim rsCheck As ADODB.Recordset

Set rsCheck = New ADODB.Recordset

If IsNull(txtPONum) = False Then
strSQL = strSQL & "([PONumber] = '" & [txtPONum] & "') And "
End If
If IsNull(txtTechCode1) = False Then
strSQL = strSQL & "([TechCode1] = " & Chr(34) & [txtTechCode1] & Chr(34) & ") And "
End If
If IsNull(txtTransDate) = False Then
strSQL = strSQL & "([TransDate] = # " & [txtTransDate] & " #) And "
End If
If IsNull(txtItem) = False Then
strSQL = strSQL & "([Item] = " & Chr(34) & [txtItem] & Chr(34) & ") And "
End If

'Clean SQL Statement
If Right(strSQL, 4) = "And " Then
strSQL = Left(strSQL, Len(strSQL) - 4)
End If
'Check for records
strSelect = "SELECT tblPOLine.*"
strSelect = strSelect & "FROM tblPOLine"

If strSQL <> "" Then
strSelect = strSelect & " WHERE " & "(" & strSQL & ")"
End If
'rsCheck.Open strSelect, gblDb, adOpenKeyset, adLockOptimistic

With rsCheck
.Open
.ActiveConnection = gblDb
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
End With

Set appExcel = CreateObject("Excel.Application")
Set wkbExcel = appExcel.Workbooks.Add
Set wksExcel = wkbExcel.Worksheets("Sheet1")
appExcel.Visible = False

CreateColumnHeaders wksExcel

intRow = 2

Do While Not rsCheck.EOF
wksExcel.Cells(intRow, 1).Value = rsCheck("ID")
wksExcel.Cells(intRow, 2).Value = rsCheck("PONumber")
wksExcel.Cells(intRow, 3).Value = rsCheck("TransDate")
wksExcel.Cells(intRow, 4).Value = rsCheck("TechCode1")
wksExcel.Cells(intRow, 5).Value = rsCheck("Item")
wksExcel.Cells(intRow, 6).Value = rsCheck("PODate")
wksExcel.Cells(intRow, 7).Value = rsCheck("LineNumber")
wksExcel.Cells(intRow, 8).Value = rsCheck("FSRNum")
wksExcel.Cells(intRow, 9).Value = rsCheck("TechCode2")
wksExcel.Cells(intRow, 10).Value = rsCheck("UnitCost")
wksExcel.Cells(intRow, 11).Value = rsCheck("QtyOrdered")
wksExcel.Cells(intRow, 12).Value = rsCheck("Company")
wksExcel.Cells(intRow, 13).Value = rsCheck("QtyRcvd")
wksExcel.Cells(intRow, 14).Value = rsCheck("PurchFrLoc")
wksExcel.Cells(intRow, 15).Value = rsCheck("UOM")
wksExcel.Cells(intRow, 16).Value = rsCheck("StockLocation")
wksExcel.Cells(intRow, 17).Value = rsCheck("MfgCode")
wksExcel.Cells(intRow, 18).Value = rsCheck("VendorName")
wksExcel.Cells(intRow, 19).Value = rsCheck("VendorPartNum")
wksExcel.Cells(intRow, 20).Value = rsCheck("Selected")
wksExcel.Cells(intRow, 21).Value = rsCheck("VendorCode")
wksExcel.Cells(intRow, 22).Value = rsCheck("MfgPartNum")
wksExcel.Cells(intRow, 23).Value = rsCheck("MfgName")
wksExcel.Cells(intRow, 24).Value = rsCheck("ItemDesc1")
wksExcel.Cells(intRow, 25).Value = rsCheck("ItemDesc2")

intRow = intRow + 1
rsCheck.MoveNext
Loop

wbkExcel.SaveAs "C:\Documents and Settings\CtDubiM\My Documents\Lawsonresults.xls"
appExcel.Quit

Set appExcel = Nothing
Set wkbExcel = Nothing
Set wksExcel = Nothing

rsCheck.Close
Set rsCheck = Nothing

 

[petehilljnr]

One thing I noticed:

strSelect = "SELECT tblPOLine.*"
strSelect = strSelect & "FROM tblPOLine"

Should have a space in it:
strSelect = strSelect & " FROM tblPOLine"

 

[boblarson]

Are you connecting to a completely outside database with the ADO code, or are you connecting to the current one? If the current one, just use

CurrentProject.Connection

as the connection in the recordset object.