Function not working

[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()));

 

[Jack Leach]

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

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()));

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)

 

[Paolo]

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

 

[Stuart McCall]

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()));

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