Returning the results of a public function

[Zoptooniek]

Hi All,

I'm not a great developer or anything, I just do what I know and what I've picked up frm forums so I have a question here which i'm hoping can be answered very easily by you experts!

Basically I have a Case Statement that I have written as a Public Function within it's own Module.

EG of My Public Function:

Public Function GetUserInfo()
Dim Username As String
Dim DisplayName As String
Dim Department As String

Username = Environ("username")

Select Case Username
Case "Adam"
DisplayName = "Adam Smith"
Department = "Claims"
End Select
End Function

The full case statement has more cases within it.

What I am trying to achieve is to return the DisplayName & Department results and populate some fields on a form when it loads.

The only thing that I'm stuck on is how to return these values back to my code in the OnLoad Sub on my form.

The reason that I want this case statement as a Public Function is because I will be using it for several different fields and do not wish to keep having to write this long Case Statement for each field on my form.

Any help would be greatly appreceated!

 

[suburbanpsyco]

Unless you are going for an encapsulated solution, you could declare the variables outside the function as a quick fix. Then just call the function on form load. After that, just set the values of the text fields = whichever variable. if youre looking for a return feature. Other than that, you may have some difficulty returning multiple values from one function. To return one value, just set the function = whatever value.

 

[Steve R.]

The general purpose of a function is to return one value. So when you call the function the general syntax is something similar to below.

From your form:

FunctionResult=MyFunction(SomeValuetobeComputed)

From your module

Public Function MyFunction(SomeValuetobeComputed As Variant) As Integer
   *****
   Some Computational Code
   *****  
   MyFunction = ComputedResult
End Function

Another approach is to use a subroutine procedure.

 

[boblarson]

IMPORTANT! Do NOT FOLLOW THE PATH YOU ARE ON!!!!

Do NOT hardcode usernames, and such in code. You should have this information in a TABLE and then retrieve it instead. Maintenance of code should not be necessary should a user leave or new user be added.

 

[Zoptooniek]

Thanks for the replies guys.

Bob, I do actually have all this information in a table already, how do you suggest I pull this information back to my field in the form when the form loads?

 

[boblarson]

Thanks for the replies guys.

Bob, I do actually have all this information in a table already, how do you suggest I pull this information back to my field in the form when the form loads?

If the username is their network login and then the DisplayName and department is what you want to see then:

Dim rst As DAO.Recordset
Dim strSQL As String
 
strSQL = "Select DisplayName, Department FROM TableNameHere WHERE Username = " & Chr(34) & VBA.Environ("username") & Chr(34)
 
Set rst = CurrentDb.OpenRecordset(strSQL)
If rst.RecordCount > 0 Then
   Me.txtDisplayName = rst!DisplayName
   Me.txtDepartment = rst!Department
End If
rst.Close
Set rst = Nothing

 

[Zoptooniek]

Spot on!!!
Thanks Bob!