question

Hasimoto-8049 avatar image
0 Votes"
Hasimoto-8049 asked emizhang-msft edited

#Value! Error in a vba function to return a recordset

I wrote the below vba function aiming to return a SQL record set into a cell. I get #Value error when I use this function. It should get two arguments, namely two cells, and simply return Yes or No. I checked the SQL query and it works fine when written as a subroutine. So pardon for replacing some sql commands with *

 Public Function TrRe(No1 As String, No2 As String) As ADODB.Recordset
 Dim cn As ADODB.Connection
 Dim rs As ADODB.Recordset
 Dim SourceText As String
    
 Set cn = New ADODB.Connection
 cn.ConnectionString = _
     "Provider=MSOLEDBSQL;Server=****;Database=*****;Integrated Security=SSPI"
 cn.Open
 Set rs = New ADODB.Recordset
 SourceText = " SELECT  " & _
             "   CASE  " & _
             "   WHEN ***** LIKE 'TEST%' OR *****=1 THEN 'Yes' " & _
             "   ELSE 'No " & _
             "   END " & _
             "  FROM *** "
             " WHERE ****=" & No1 & _
             "   AND ****=" & No2  
 rs.ActiveConnection = cn
 rs.Source = SourceText
 rs.CursorType = adOpenForwardOnly
 rs.LockType = adLockReadOnly
 rs.Open
 Set TrRe= rs
 rs.Close
 cn.Close
 Set cn = Nothing
 End Function

The query always returns a 1x1 recordset and this is Yes or No. b) The code should be written as a Function since I want to let the user to choose the inputs.
I would appreciate your comments/helps.

sql-server-generaloffice-vba-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered
              "   ELSE 'No " & _
              "   END " & _
              "  FROM *** "
              " WHERE ****=" & No1 & _
              "   AND ****=" & No2

Is that really the code? There are at least 5 apostrophe's missing, one after " ELSE 'No " and around the parameters No1 + No2 in WHERE clause; the parameters are strings.
Use the debugger to get the content of variable SourceText to see if you have valid SQL statement.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Hasimoto-8049 commented

It is probably incorrect to return a closed recordset. If you want to return the ‘Yes’ or ‘No’, then change the return type:

 Public Function TrRe(No1 As String, No2 As String) As String
    . . .
    TrRe = rs(0)
    . . .
 End Function


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TrRe = rs(0) works, even when the "rs.Close" is there. Why is Set "TrRe= rs" incorrect?

0 Votes 0 ·