Solved VBA Function To Accept A Parameter And Return A Parameter (1 Viewer)

jo15765

Registered User.
Local time
Yesterday, 16:39
Joined
Jun 24, 2011
Messages
116
I'm sure this can be done, I just can't wrap my mind around doing it.

I want to pass in a sql string to a Function that will open a recordset and return the result of the recordset to the calling function.

I'm trying to do something like this

Code:
Sub Test()
  Dim sqlString As String
  sqlString = "Select xxx from yyyy where xxx is null;"
  GetRecordSetResults sqlString

  'I want to get the value of retVal from the function GetRecordSetResults
  If retVal >= 1 Then
    Debug.Print "Result is greater than or equal to one!"
 End If
End Sub

Public Function GetRecordSetResults (querySQL As String)
    Dim rs As DAO.Recordset
    
    Set rs = CurrentDb.OpenRecordset(querySQL)
    
    retVal = rst.Fields(0)
    
    rst.Close
    Set rst = Nothing
End Function
 

Ranman256

Well-known member
Local time
Yesterday, 19:39
Joined
Apr 9, 2015
Messages
3,957
dont use a recordset, use Dlookup , its faster, BUT,
to return the value, you must assign it to the function name at the end:

Code:
Public Function GetRecordSetResults (pvID)
GetRecordSetResults = dlookup("[ReturnFld]","query","[IDfld]=" & pvID)
End Function

or your way:

Code:
Public Function GetRecordSetResults (querySQL As String)
    Dim rs As DAO.Recordset
   
    Set rs = CurrentDb.OpenRecordset(querySQL)
   
    GetRecordSetResults = rst.Fields(0) & ""
   
    rst.Close
    Set rst = Nothing
End Function
 

jo15765

Registered User.
Local time
Yesterday, 16:39
Joined
Jun 24, 2011
Messages
116
The table that I'm looking at is always less than 1,500 rows so I am not conerned with speed at this point, but I will keep in mind DLookup is quicker.

I see you assigned the result to the function name, I didn't know you could do that! :)

My remaining question is, would I get the value in the calling Sub like this then?

Code:
Sub Test()
  Dim sqlString As String
  sqlString = "Select xxx from yyyy where xxx is null;"
  GetRecordSetResults sqlString

  'I want to get the value of retVal from the function GetRecordSetResults
  If GetRecordSetResults >= 1 Then
    Debug.Print "Result is greater than or equal to one!"
End If
End Sub

Public Function GetRecordSetResults (querySQL As String)
    Dim rs As DAO.Recordset
   
    Set rs = CurrentDb.OpenRecordset(querySQL)
   
    GetRecordSetResults = rst.Fields(0) & ""
   
    rst.Close
    Set rst = Nothing
End Function

I thought it would be something like
Code:
retVal =   GetRecordSetResults sqlString

but if I do that I get an immediate debug error of
Compile Error:
Expected: end of statement

on the variable I'm trying to pass to the function
 

Ranman256

Well-known member
Local time
Yesterday, 19:39
Joined
Apr 9, 2015
Messages
3,957
functions return values.
subs dont.
 

jo15765

Registered User.
Local time
Yesterday, 16:39
Joined
Jun 24, 2011
Messages
116
functions return values.
subs dont.

Insted of
Code:
Sub Test

It should be
Code:
Function Test

Is that what you are saying needs changing?\

Err...just kidding, I tested that theory and same error even if I place it in a function.
 

jo15765

Registered User.
Local time
Yesterday, 16:39
Joined
Jun 24, 2011
Messages
116
Okay, I figured it out but I don't understand it.

All I had to do was this
Code:
retVal = GetRecordSetResults(sqlString)

Why do the parens make a diff?
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 19:39
Joined
May 21, 2018
Messages
5,680
Why do the parens make a diff?
The Parentheses discussion is kind of involved.

1. If you want to call a function and not return any value then no parentheses
Msgbox "Hello World"
Message box is a function, but in this case you want nothing returned
or if you want a value returned
returnValue = msgbox("Do you want to continue",vbYesNo,"Some Title)
Msgbox is a function and returns either vbYes or vbNo

2. Since procedures do not return anything then no parentheses
SomeProcedureName SomeArgument1, SomeArgument2

3. If you use the Call Keyword you have to use parentheses
Call SomeProcedureName (SomeArgument1, SomeArgument2)
The Call keyword is never required, but left over from early versions of VB

4. Now here is the wacky one no one knows about. You can use it to pass something that normally is ByRef as ByVal.
See discussion

Procedures in VBA accept arguments by reference as default
If I call some procedure and do this
SomeProcedure X
X is passed by reference
SomeProcedure (X)
X is passed by value

This can get very confusing if you pass an access object

Public SomeProcedure(TxtBox as access.textBox)

If I call it correctly
SomeProcedure me.txtBox1
no error

If I mistakenly put ()
SomeProcedure (me.txtBox1)
I get an error. It tries to pass by val so instead of passing a reference to the textbox it passes the value of the text box and thus an error.
 

Users who are viewing this thread

Top Bottom