Runtime error '91': Object variable or with block variable not set

[spyla]

I have two workbooks,

VBA code in book 1

Private Connect_String As String

Sub define_connect_string()
Connect_String = "DSN=dev;UID=clgXXX;PWD=XXXXX;DB=DEV"
End Sub

Function Get_Gas(RecordName As String)
Dim QueryText As String
Dim QueryText1 As String
Dim QueryText2 As String
Dim QueryText3 As String
Dim QueryText4 As String
Dim Conn1 As ADODB.Connection
Dim Cmd1 As ADODB.Command
Dim Rs1 As ADODB.Recordset
 
define_connect_string
 
   Set Conn1 = New ADODB.Connection
   Conn1.ConnectionString = Connect_String
   Conn1.Open
 
    Range("import_gas_range").ClearContents
     QueryText1 = "select "
     QueryText2 = Range("Gas_SQL_Fields_String").Value
     QueryText3 = " from fl_gas where id = "
     QueryText4 = "'" & RecordName & "'"
     QueryText = QueryText1 & QueryText2 & QueryText3 & QueryText4
 
    Cmd1.CommandText = QueryText
  Set Rs1 = Cmd1.Execute()
 
 
    Sheets("Database").Unprotect
        Range("D7").CopyFromRecordset Rs1
 
    Sheets("Database").Protect
      Application.StatusBar = ""
 
Rs1.Close
   Set Rs1 = Nothing
'Return TRUE if a record was returned
If Range("gas_count").Value = 0 Then
    Let Get_Gas = False
    Else: Let Get_Gas = True
End If
End Function

code in book 2

Sub oraclegetdata()
   Dim Conn1 As ADODB.Connection
   Dim Cmd1 As ADODB.Command
   Dim Rs1 As ADODB.Recordset
   Dim strTmp As String
   Dim sconnect As String
 
     sconnect = "DSN=dev;UID=clgxxx;PWD=XXXXXX;DB=DEV"
 
.
   Set Conn1 = New ADODB.Connection
   Conn1.ConnectionString = sconnect
   Conn1.Open
 
   Set Cmd1 = New ADODB.Command
   Cmd1.ActiveConnection = Conn1
 
        str1 = "SELECT AIR, BENZENE, CLIENT, CO2, CYCLOHEXANE, CYLINDER, DECANES, DODECANES, E_BENZENE, EICOSANES, ETHANE, H2S, HEPTADECANES, HEPTANES, HEXADECANES, HEXANES, HYDROGEN, I_BUTANE, I_PENTANE, ID" & Chr(13) & "" & Chr(10) & "FROM FL_GAS" & Chr(13) & "" & Chr(10) & "WHERE (ID ='"
    str2 = "794"
    Str3 = "')"
    strf = str1 + str2 + Str3
 
 
    Cmd1.CommandText = strf
  Set Rs1 = Cmd1.Execute()
  Range("A5").CopyFromRecordset Rs1
 
1 Rs1.Close
   Set Rs1 = Nothing
End Sub

code 2 runs fine but code 1 fails @

Cmd1.CommandText = strf

with error
Runtime error '91':

Object variable or with block variable not set

I have made sure that strf and QueryText are exactly same.

any help is appreciated, thanks

 

[Jaafar Tribak]

The line Cmd1.CommandText = strf is not anywhere in code1

 

[spyla]

sorry meant to say

Cmd1.CommandText = QueryText

running Off 2010 Prof Plus 64bit on Win 7 Ent 64 bit

 

[spyla]

bump

 

[spyla]

found the error

forgot the lines

Set Cmd1 = New ADODB.Command
   Cmd1.ActiveConnection = Conn1

after

Conn1.Open