CODEWITHSUNDEEP
ms-accessvba
shiv chhabra
shiv chhabra

Reputation: 39

Unable to use function's Variable in a sub in MS-Access

I have the below VBA code on my ms-access form

Option Compare Database
Option Explicit

Public Function GetUserName() As String
    Dim obj1 As Object
    Dim GetEmpID As String
    Set obj1 = CreateObject("WScript.Network")
    GetUserName = DLookup("[BAFUser]", "BAF_User", "[BRID] = '" & obj1.UserName & "'")
    GetEmpID = DLookup("[EmpID]", "BAF_User", "[BRID] = '" & obj1.UserName & "'")
    Set obj1 = Nothing
End Function

Private Sub Form_Load()
    Me.EmpName.Value = GetUserName
    Me.EmpID.Value = GetEmpID
End Sub

I am able to get the value using GetUserName in my Load() sub but unable to get the value using GetEmpID.

What can be the possible reason? And how can i fix it?

Thanks in advance.

Upvotes: 1

Views: 68

Answers (2)

Chris
Chris

Reputation: 254

You could always use the ByRef in your function.

Option Compare Database
Option Explicit

Public Function GetUserName(ByRef GetEmpID as string) as string
dim obj1 as object

Set obj1 = CreateObject("WScript.Network")

GetUserName = DLookup("[BAFUser]", "BAF_User", "[BRID] = '" & obj1.UserName & "'")
GetEmpID = DLookup("[EmpID]", "BAF_User", "[BRID] = '" & obj1.UserName & "'")

If not obj1 is nothing then
    Set obj1 = Nothing
End If
End Function

Private Sub Form_Load()
Dim GetEmpID as string

GetEmpID = "None"

Me.EmpName.value = GetUserName(GetEmpID)
Me.EmpID.Value = GetEmpID
End Sub

The ByRef passes the address that is used to store the variable in memory. Once the address is passed instead of just the value, the any changes that happen to the address will be made to the variable across the project.

Please see website below for more detailed description of ByVal and ByRef: http://www.cpearson.com/excel/byrefbyval.aspx

Upvotes: 1

Jeremy
Jeremy

Reputation: 4838

Option 1: Create module level variables, which you set in the function (now a sub, since it is returning no value)

 Option Compare Database
 Option Explicit

 Dim UserName as String
 Dim EmpID as String

 Public Sub GetValues()
  Dim obj1 As Object

  Set obj1 = CreateObject("WScript.Network")
  UserName = DLookup("[BAFUser]", "BAF_User", "[BRID] = '" & obj1.UserName & "'")
  EmpID = DLookup("[EmpID]", "BAF_User", "[BRID] = '" & obj1.UserName & "'")
  Set obj1 = Nothing
 End Sub

Private Sub Form_Load()
    Call GetValues() 'Invoke the function to set values
    Me.EmpName.Value = UserName 'retrieve values from module level variables
    Me.EmpID.Value = EmpID
 End Sub

Option 2: Create ByRef variables.

 Option Compare Database
 Option Explicit

 Public Sub GetValues(ByRef UserName as String, ByRef EmpID as String) 
  Dim obj1 As Object

  Set obj1 = CreateObject("WScript.Network")
  UserName = DLookup("[BAFUser]", "BAF_User", "[BRID] = '" & obj1.UserName & "'")
  EmpID = DLookup("[EmpID]", "BAF_User", "[BRID] = '" & obj1.UserName & "'")
  Set obj1 = Nothing
 End Sub

Private Sub Form_Load()
   Dim UserName as String
   Dim EmpID as String
    Call GetValues(UserName, EmpId) 'Invoke the function to set values of local variables.
    Me.EmpName.Value = UserName 'retrieve values from function scoped variables.
    Me.EmpID.Value = EmpID
 End Sub

Warning: typing from memory. minor corrections may be needed.

Upvotes: 2

Related Questions