HELP "Object Required"

[tatendatiffany]

Hi,
could some one help me i am facing a problem on my code "object required" but i have specified in the function what could i have done wrong: the line higlighted in bold is where i am getting the error but i thought i had specified in the function the recordset??

 Function getrecordset(str) As String
    'create recordset object
     Dim rs As ADODB.Recordset
     Set rs = New ADODB.Recordset
     With rs
    .ActiveConnection = cn
    'using the str variable open the recordset
    .Source = str
    .Open
     End With
    'if recordset NOT NULL
     If Not (rs.BOF And rs.EOF) Then
    getrecordset = rs(0)
        'getrecordset = recordset
    'else
        'getrecordset = "No Result"
    getrecordset = vbNullString
    'end if
                                'close and destroy recordset object
     Set rs = Nothing
End Function
Sub getresults()
    Dim r As Integer                    'row counter
    Dim c As Integer                    'column counter
    Dim str As String
    Dim rs As Recordset
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    With cn
       .ConnectionString = "Password=" & password & ";Persist Security Info=True;User ID=" & username & ";Data Source=" & DatabaseEnv & ";Mode=ReadWrite;"   'Assumption based on your connection string being valid
       .Provider = "IBMDADB2.DB2COPY1"      'assumption based on your provider being correct
       ' .Open                       'if the above are true then this will open DB
    'End With
    
   [B]Set rs = Db.OpenRecordset
[/B]    For c = 31 To 35                 'columns AE to AI
        For r = 2 To 39                             'start with first row
            If Cells(r, c).Value <> "" Then            'is there a value in the row?
                str = Cells(r, c).Value                 'this is the SQL string we need for the recordset
                Cells(r, c - 5).Value = getrecordset(str) 'use a function to open the recordset and return the value to column 3
            End If
        Next r
    Next c                              'next column
End With
End Sub

 

[tatendatiffany]

so my connectionstring is declared in a in a public function called DBConnection. how about would declare this in my get results sub??

 

[RoryA]

You need to declare the Connection object rather than connection string as public (cn in your examples) and then refer to cn in your routines. Note: do not declare cn anywhere else - i.e. remove any other 'Dim cn...' statements

 

[tatendatiffany]

one last question so i have done what you have suggested and it seems to be working but i have one lat question. so should i delete these lines from my sub

 Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    With cn
       .ConnectionString = "Password=" & password & ";Persist Security Info=True;User ID=" & username & ";Data Source=" & DatabaseEnv & ";Mode=ReadWrite;"
       .Provider = "IBMDADB2.DB2COPY1"
       ' .Open
    'End With

and just have

Set rs = MFcnn.OpenRecordset

 

[RoryA]

No, since OpenRecordset is not a method of a Connection. You need something like:

set rs = new adodb.recordset
rs.open strSQL, MFcnn

where strSQL is your SQL string.

 

[tatendatiffany]

but my sqlstrings are in the excel sheets and are called in this part of the code

 For c = 31 To 35                
        For r = 2 To 39                          
            If Cells(r, c).Value <> "" Then          
                str = Cells(r, c).Value                  for the recordset
                Cells(r, c - 5).Value = getrecordset(str) 'use a function to open the recordset and return the value to column 3
            End If
        Next r
    Next c                            
End With
End Sub

 

[RoryA]

Then you need to create the recordset in the GetRecordset function using the sql string that is passed to it.

 

[tatendatiffany]

see this is the part i am trully failing to grasp because i thought my sql in the sheets are the recordsets and the "for loop" i just mentioned is the method of going the recordsets.
so now let me get this clearly i need to do an recordset("select from ") that is telling excel to go into the sheet and run those sqls but arent i doing that already with the for loop

 

[RoryA]

It is the getrecordset function that has to create a recordset based on the specified SQL string passed to it, therefore that is the place to do it.

 

[tatendatiffany]

but i dont want the sql to be hardcoded that why i had it on the excel sheet. so then how do i proceed from here i'm so confused right now
so in my code

getrecordset(str)

opens the recordsets them being (str)
so do i need to write a string for str that being " go into excel sheets then run theses sql in the cells??

 

[RoryA]

No. Your loop currently picks up the string from the cells and passes it to the Getrecordset function. It is in that function that you need to create the recordset.