Function not working


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

Advertisements

J

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)
 
P

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
 
Ad

Advertisements

S

Stuart McCall

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top