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

 

[RoryA]

You haven't declared or initialised db.
Also, str is a VBA function so it's not a good name to use for a variable.

 

[tatendatiffany]

ohh ok thank you for the quick response can i ask another question??
can i add a function into a sub because i have placed the function in the sub and now i am getting the error "expected sub end" but is there. Could it be that i am trying to execute and illegial action ??

 

[RoryA]

You cannot declare any routine within another routine. You can however call one routine from the other (as you do with your Getrecordset function)

 

[tatendatiffany]

oh ok that makes sense so how would i call the function getrecordset
that i have created. do i simply add a call in my other sub??

 

[RoryA]

You already have one:

Cells(r, c - 5).Value = getrecordset(str)

 

[tatendatiffany]

so there were i am getting the objects required error i simply need to intialise the database connection that is all.
another question sorry. I have a login userform connection that a user logs in 1st. so i want to call that when a running through the subs so it doesnt log in again in the get results query. how would i do that??? on the line

Set rs = db.OpenRecordset

If MFcnn.State = 1 Then
    MFcnn.Close
End If
On Error GoTo fErr
With MFcnn
    .Provider = "IBMDADB2.DB2COPY1"
    .Mode = adReadWrite
    .ConnectionString = "Password=" & password & ";Persist Security Info=True;User ID=" & username & ";Data Source=" & DatabaseEnv & ";Mode=ReadWrite;"
    .Open
End With

 

[RoryA]

I have no idea what you mean I'm afraid.

 

[tatendatiffany]

ok sorry let me try explain more clearly.
So the issue i am facing is object is required which is the connection to the database i presume.
But above i already have a code that connects me to the database and is already connecting me to the database.

so how do i call the connection sub in the other subs without having to add another connection string
e.g. in the getresults function
how would i call the connection sub??

 

[RoryA]

Pass the connection as an argument to any sub/function that needs it, or declare it as a public variable and refer to it directly within each sub/function.