HELP "Object Required"

tatendatiffany

Board Regular
Joined
Mar 27, 2011
Messages
103
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??:confused::confused:

Code:
 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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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.
 
Upvote 0
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 ??
 
Upvote 0
You cannot declare any routine within another routine. You can however call one routine from the other (as you do with your Getrecordset function)
 
Upvote 0
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??
 
Upvote 0
You already have one:
Rich (BB code):
Cells(r, c - 5).Value = getrecordset(str)
 
Upvote 0
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
Code:
Set rs = db.OpenRecordset

Code:
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
 
Upvote 0
I have no idea what you mean I'm afraid.
 
Upvote 0
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??
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top