Sub or function not defined

[tatendatiffany]

i am getting this error on the line highlighted in bold any ideas why:

Sub getresults()
    Dim r As Integer                    'row counter
    Dim c As Integer                    'column counter
    Dim str As String                   'SQL string holder
    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
    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 = [B]getrecordset[/B](str) 'use a function to open the recordset and return the value to column 3
            End If
        Next r
    Next c                              'next column
End Sub

 

[Ruddles]

VBA is complaining that it doesn't know what getrecordset means. Did you copy the rest of the code from somewhere and omit to bring the code for the getrecordset function along with it?

 

[tatendatiffany]

ohh ok so basically i have set up the recordser for the code for the recordset that make sense. Can i ask if i want to do the recordset where would i place in the code. And i am slightly confused what am i calling the recordset to do ??

 

[Ruddles]

You appear to be looping through the cells AE2:AI39 and fetching something from the database depending on what's in each of those cells, then placing the results in Z2:AD39.

Is that what you're trying to do?

The problem is that you're using a function called getrecordset and VBA doesn't know where that function is: it's not a standard VBA function and the code for it isn't with your existing code.

Did you write the code you posted above? If so, why did you you use the word getrecordset and what did you expect it to do?

If you didn't write the code, who did? And where is the code for getrecordset? If you copied your code from somewhere else, you need to go back there and find the code for getrecordset: it's not a standard VBA function so you need to keep the code for it with your code at all times so VBA knows where to find it.

Your code should end up looking something like this:-

[COLOR=blue]Sub getresults()
[/COLOR]
[COLOR=blue]' existing code as above including the call to getrecordset[/COLOR]
[COLOR=blue][/COLOR] 
[COLOR=blue]End Sub [/COLOR]
 
[COLOR=red]Function getrecordset(str As String) As String[/COLOR]
[COLOR=red][/COLOR] 
[COLOR=red]' some code which sets up and executes an SQL query, maybe including something like[/COLOR][COLOR=#ff0000]:-[/COLOR]
[COLOR=#ff0000]'    sSQL="SELECT... FROM... WHERE..." & str[/COLOR]
[COLOR=#ff0000]'    rst.Open sSQL, cn...[/COLOR]
[COLOR=#ff0000]'    [/COLOR][COLOR=#ff0000]getrecordset=...[/COLOR]
[COLOR=#ff0000][/COLOR] 
[COLOR=red]End Function[/COLOR]

The blue bit is what you've already got and the red bit is what's missing.

The problem is no-one can write that code for you because it includes an SQL query which takes the value of str from getrecordset(str) and uses it in some way to extract data from your database. We can't know how it does that or what your database fields are called.

Are you following my explanation? Sometimes it's difficult to picth responses at the appropriate level.

 

[tatendatiffany]

Thank you very much for your detailed explaination. the code i used is part of a bigger code that i had to strip into smaller chunks and modify it to serve my specific purpose. I am going to go back and check if i have missed out some pieces that were important

Thanks again for the explanation it helped

 

[Ruddles]

If it's on the Web somewhere, post the URL if you get stuck.

 

[tatendatiffany]

hie again so i have gone back and identified what i was missing and i have started to work on the recordset. and here is how it looks so far: does it now make sense am i on the right path?? havent yet started debugging.

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

 

[tatendatiffany]

thanks for your help but i think i am going to start a new thread as i am facing a new issue

 

[Ruddles]

It looks like getresults calls getrecordset, passing the value of the variable str to it. You just need to make sure str contains a valid SQL query before you call getrecordset.