How to do a function in VBA

[sanderson]

I think this is a silly question, because I think I did it right. However, the variable txtDept returns null. strDept returns a correct value. All I want to do is run the function and assign its returned value to a variable.



Thanks!

Private Sub txtLaborHours_AfterUpdate()
    Dim txtEmpID As String
    txtEmpID = Forms!frmTimeCard.txtEmpID.Value
    
    Dim txtDept As String
    txtDept = GetDeptStatus(txtEmpID)
    Debug.Print ("txtDept: " & txtDept)
    
    txtNotes.SetFocus
    Select Case txtDept
        Case "ME", "EE", "AS", "MA", "HYD"
            If Len(txtNotes.Text) = 0 And cboBillable.Value = "I" Then
                txtLaborHours.Value = Null
                txtNotes.SetFocus
                Me.Refresh
                MsgBox "You must enter Notes on non-billable activity before Time. "
       
            End If
        
    End Select
       
End Sub

Private Function GetDeptStatus(strEmpID As String) As String
    ' Added 9/22/15
    ' This sub gets the Shop Supervisor Status from Vantage to determine which controls to show or hide
    
    ' Dimension variables
    Dim sqlGetDeptStatus As String
    Dim rsGetDeptStatus As adodb.recordset
    
    ' Set variables
    Set rsGetDeptStatus = New adodb.recordset
    
    Debug.Print ("strEmpID: " & strEmpID)
        
    ' Create Query
    sqlGetDeptStatus = "SELECT dbo_empbasic.jcdept " & _
                             "FROM dbo_empbasic " & _
                             "WHERE dbo_empbasic.empid = '" & strEmpID & "' "
                    
    Debug.Print ("sqlGetDeptStatus: " & sqlGetDeptStatus)
                    
    ' Open the recordset and populate the form
    rsGetDeptStatus.Open sqlGetDeptStatus, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    
    Dim strDept As String
    
    If Not rsGetDeptStatus.EOF Then
        strDept = rsGetDeptStatus.Fields("jcdept")
        
    Else
        strDept = ""
        
    End If
       
    rsGetDeptStatus.Close
    Set rsGetDeptStatus = Nothing
    
    strDept = strDept
    Debug.Print ("strDept: " & strDept)
    
End Function

[ItsMe]

What is the following for? Is this the value you are trying to assign to your function?

    strDept = strDept
    Debug.Print ("strDept: " & strDept)

[June7]

Example of a custom function and calling that function to set a variable.

Function MyFun()
MyFun = "Test"
End Function

x = MyFun()

The following makes no sense:
strDept = strDept

Your function lacks the line that sets the function value to pass back to the calling procedure.

GetDeptStatus = strDept

Do you really need a custom function? Could intrinsic DLookup() accomplish?

DLookup("jcdept", "dbo_empbasic", "empid = '" & Me.EmpID & "'")

Is EmpID a text type field? If it is number type, then remove the apostrophe delimiters.

[sanderson]

I am fine with removing strDept = strDept because...
strDept = rsGetDeptStatus.Fields("jcdept")

I added this at the end so see if it would help, because I thought possibly the last item defined would make it work.

[June7]

Did you try either of the changes I suggested?

[ItsMe]

I am fine with removing strDept = strDept because...
strDept = rsGetDeptStatus.Fields("jcdept")

I added this at the end so see if it would help, because I thought possibly the last item defined would make it work.

OK, check out the following revision and see if it makes sense. You will still want to change some additional things. However, this should work as is.

    ' Added 9/22/15
    ' This sub gets the Shop Supervisor Status from Vantage to determine which controls to show or hide
    
    ' Dimension variables
    Dim sqlGetDeptStatus As String
    Dim rsGetDeptStatus As adodb.Recordset
    
    ' Set variables
    Set rsGetDeptStatus = New adodb.Recordset
    GetDeptStatus = ""
    
    Debug.Print ("strEmpID: " & strEmpID)
        
    ' Create Query
    sqlGetDeptStatus = "SELECT dbo_empbasic.jcdept " & _
                             "FROM dbo_empbasic " & _
                             "WHERE dbo_empbasic.empid = '" & strEmpID & "' "
                    
    Debug.Print ("sqlGetDeptStatus: " & sqlGetDeptStatus)
                    
    ' Open the recordset and populate the form
    rsGetDeptStatus.Open sqlGetDeptStatus, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    
    Dim strDept As String
    
    If Not rsGetDeptStatus.EOF Then
'        strDept = rsGetDeptStatus.Fields("jcdept")
        GetDeptStatus = rsGetDeptStatus.Fields("jcdept")
        rsGetDeptStatus.Close
        
'    Else
'        strDept = ""
        
    End If
       
'    rsGetDeptStatus.Close
    Set rsGetDeptStatus = Nothing
    
'    strDept = strDept
'    Debug.Print ("strDept: " & strDept)
     Debug.Print ("GetDeptStatus: " & GetDeptStatus)

[sanderson]

Thanks. Brings back memories before the .NET days.

[ItsMe]

Yah there is not 'Return' with VBA so you use the name of the function. I would recommend matching the data types as best as possible. In other words, you explicitly declare some variables as strings. If a string is assigned a number it will be a string. If a field is of type Number and you use a string variable as an assignment to the field, Access will automatically cast the string to number (if possible). I do not know what you are using for Unique Identifiers but I would consider type Long as a variable declaration.

[June7]

Did you see post 3?