Function not working

  • Thread starter Thread starter SAP2
  • Start date Start date
S

SAP2

Hello,
I have this little function that I am trying to get a value for:

Function SignName() As String

Dim dbs As Database
Dim rst As DAO.Recordset
Dim strName As String

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("qrySignature", dbOpenDynaset)
strName = rst!SName

End Function

When I call the function it is empty, but when I use:

Function SignName() As String

Dim dbs As Database
Dim rst As DAO.Recordset
Dim strName As String

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("qrySignature", dbOpenDynaset)
MsgBox rst!SName

End Function

and call the function it works.

The SQL for the query is:
SELECT tbl_User.Name AS SName
FROM tbl_User
WHERE (((tbl_User.UserName)=fOSUserName()));
 
Generally when you open a recordset you need to navigate to a record before
you can reference anything from it... e.g. MoveFirst or FindFirst, etc.

Aside from that, Name is a reserved work and should never be used as a field
or control (or anything else) identifier
SELECT tbl_User.Name AS SName
Click to expand...


But, based on what I'm seeing that you're trying to do, I would think a
DLookup would be sufficient without having to write another function for it...
SELECT tbl_User.Name AS SName
FROM tbl_User
WHERE (((tbl_User.UserName)=fOSUserName()));
Click to expand...

MsgBox DLookup("fldName", "tblUser", _
"UserName = """ & fOSUserName() & """")


hth

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
 
Hi SAP2,
actually you have to assign the return value of a function and in your
function you didn't do that so it's normal that it doesn't return nothing.
Modify your function in this way

Function SignName() As String

Dim dbs As Database
Dim rst As DAO.Recordset
Dim strName As String

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("qrySignature", dbOpenDynaset)
SignName = rst!SName 'with this instruction you assign a return value to the
function

End Function

HTH Paolo
 
SAP2 said:
Hello,
I have this little function that I am trying to get a value for:

Function SignName() As String

Dim dbs As Database
Dim rst As DAO.Recordset
Dim strName As String

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("qrySignature", dbOpenDynaset)
strName = rst!SName

End Function

When I call the function it is empty, but when I use:

Function SignName() As String

Dim dbs As Database
Dim rst As DAO.Recordset
Dim strName As String

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("qrySignature", dbOpenDynaset)
MsgBox rst!SName

End Function

and call the function it works.

The SQL for the query is:
SELECT tbl_User.Name AS SName
FROM tbl_User
WHERE (((tbl_User.UserName)=fOSUserName()));
Click to expand...

In order to return a value from your function to the calling code, you must
assign the result to the name of the function, like this:

SignName = rst!SName
 
Back
Top