Returning a value from a function

[sk8er1]

I was previously using access '97 now using 2003.
How can I pass the value from the function....

For instance I used to assign a return value to the function name. How do you do it in '2003.

Thanks

 

[SkipVought]

Hi,

Please post an example of the code that you used before.

Skip,

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

 

[FhantomX]

This worked for me:

Sub Test()
Dim strTest As String
strTest = FunctionTest
Debug.Print strTest
End Sub

Function FunctionTest()
FunctionTest = "The Value"
End Function

Simple but it worked.

 

[sk8er1]

Heres the code

Public Function GetTeamLead(strID As String) As String
Dim db As Database
Dim rs As Recordset
Dim sSQL As String

Set db = CurrentDb
sSQL = "Select * from [SCMC_SCMCIDTB] " 'Where clk_id = '" & strID & "'"
Set rs = db.OpenRecordset(sSQL)
With rs
If Not (.BOF And .EOF) Then
' test if id is a T team lead
Do While Not rs.EOF
If Trim(rs!clk_id) = strID Then

If rs![id_typ] = "T" Then
GetTeamLead = rs![id_typ]
Exit Do
Else
'look for supervisor
strID = rs!supervisor_ID ' dten
rs.FindFirst "clk_ID = '" & strID & "'"
End If

Else
.MoveNext
End If
Loop
End If

End With
Set db = Nothing

End Function

 

[SkipVought]

Why are you performing an ELSE if you do NOT return a value under those circumstances?

                If rs![id_typ] = "T" Then
                   GetTeamLead = rs![id_typ]
                   Exit Do
                 Else
                   'look for supervisor
                   strID = rs!supervisor_ID   ' dten
                   rs.FindFirst "clk_ID = '" & strID & "'"
                 End If

Skip,

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

 

[RoyVidar]

Not that I have the foggiest about which error you're getting (Type Mismatch? User defined type..., Method of member...), if you're getting any at all, but I'd try the following on the DAO declarations:

[tt]Dim db As DAO.Database
Dim rs As DAO.Recordset[/tt]

And ensure the Microsoft DAO 3.6 Object Library is checked (in VBE - Tools | References)

I'm also a bit unsure about the logic, here, the only time this function returns anything, is if clk_id = strid and id_typ = "T" - is that intended? If so, wouldn't something like the following do?

[tt]dim rs as dao.recordset
dim strSql as string
strSql = "select id_typ from SCMC_SCMCIDTB " & _
"where clk_id = '" & strid & _
"' and id_typ = 'T'"
set rs = currentdb.openrecordset(strSql)
if ((not rs.eof) and (not rs.bof)) then
myfuntionname = rs.fields("id_typ").value
end if[/tt]

BTW - are you sure your bof/eof testing is testing what you intend it to test?

Roy-Vidar