[b] Returning results from functions - HELP!!!!! [/b] 2

[scoobey]

I have a form which executes a function on startup to see if a user exists. My probelm is that I can't return the result from the function back to the calling sub! I can output the result from the function, but it doesn't transfer this value back for evaluation! Please help, i'm sure it's something simple?

Private Sub Form_Timer()

Dim var_GetUserName
var_GetUserName = Environ("UserName")

Dim var_SQL 'SQL statement to be run
Dim var_SQL_Result 'SQL Result
var_SQL = "SELECT UserID AS rst_Result FROM tbl_Users WHERE UserID = """ & var_GetUserName & """ "
var_SQL_Result = RunSQLStatementInVBA(var_SQL)

Me.TimerInterval = 0
End Sub



Public Function RunSQLStatementInVBA(var_SQL)

Dim rst As DAO.Recordset
Dim var_SQLResult
Set rst = CurrentDb.OpenRecordset(var_SQL)
var_SQLResult = rst!rst_Result
rst.Close
Set rst = Nothing

End Function

 

[Crowley16]

you've not actually returned the value in your function...

you need a RunSQLStatementInVBA = var_SQLResult in your function, after you set it, and before the end of the function...

--------------------
Procrastinate Now!

 

[SkipVought]

scoobey,

What is quite often done with this type of function is 1) to define the return result as Boolean and 2) to perform a TEST within the function to determine if it ran to completion or not. If it ran to completion, then set the function to TRUE, else False.

Public Function RunSQLStatementInVBA(var_SQL)
.....
   If Condition Then
      RunSQLStatementInVBA = True
   Else
      RunSQLStatementInVBA = False
   End if
End Function

Then in your calling routine, test the condition of the function to perform error trapping.

Skip,
[sub]
[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie![/sub]

 

[scoobey]

Thanks very much guys - I knew it was something simple!

 

[PHV]

You may also consider the DLookUp function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886