Multiple fields in a function

[sanderson]

I really seem to be struggling with the basics here.



I am trying tio run a sub with three variables, and as soon as I add more than one the string turns red and will not work.

SendMessage (FileSavePath, Forms!frmTimeCard.txtDate.Value, Forms!frmTimeCard.txtDate.Value)

In my function, I am feeding the data in, I think correctly...

Private Sub SendMessage(FileSavePath As String, SD As Date, ED As Date)
    Dim appOutLook As Outlook.Application
    Dim MailOutLook As Outlook.MailItem


    Dim txtempname As String
    ' txtempname = Nz(DLookup("name", "dbo_empbasic", "empid = '" & txtEmpID & "' "), "")
    ' txtempname = GetUserName(empid)
    
    Dim sql As String
    sql = "SELECT dbo_empbasic.name FROM dbo_empbasic WHERE dbo_empbasic.empid = '" & empid & "' "
    
    Dim rs As ADODB.recordset
    Set rs = New ADODB.recordset
    
    rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    rs.MoveFirst
    
    txtempname = rs.Fields("name")
    
    rs.Close
    Set rs = Nothing
    
    
    Debug.Print ("empid: " & empid)
    Debug.Print ("txtempname: " & txtempname)
    
    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)


    With MailOutLook
        .BodyFormat = olFormatRichText
        .To = "sanderson@metromachine.com"
        ''.cc = ""
        ''.bcc = ""
        .Subject = "Weekly Timecard for " & txtempname & "."
        .HTMLBody = "Attached is the timecard for " & txtempname & ", employee number " & empid & " for the week of " & CStr(SD) & " through " & CStr(ED) & "."
        .Attachments.Add (FileSavePath)
        .Send
        '.Display    'Used during testing without sending (Comment out .Send if using this line)
            
    End With
  
End Sub

Also, within this code, I am trying to run a function called GetUserName. I've actually tried a couple of ways to populate the variable 'txtempname', but it doesn't work. So, I have just sloppily copied the information into this sub, abandoning the modular approach to get it to work.

Private Function GetUserName(empid As String) As String
    Dim sql As String
    sql = "SELECT dbo_empbasic.name FROM dbo_empbasic WHERE dbo_empbasic.empid = '" & empid & "' "
    
    Dim rs As ADODB.recordset
    Set rs = New ADODB.recordset
    
    rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    rs.MoveFirst
    
    Dim fName As String
    fName = rs.Fields("name")
    
    Debug.Print ("fName: " & fName)


    rs.Close
    Set rs = Nothing
    
End Function

Perhaps I'm just not used to VBA anymore. I believe a return must be used in .NET, but in this it just returns the last variable defined.

Thanks for the help!

[ranman256]

you dont need the .value, and keep all exclamations in the path
Forms!frmTimeCard!txtDate

[sanderson]

Okay, but this does not solve the problem. The text in the editor shows up red as soon as I add more than one variable in the sub call.

[sanderson]

SendMessage (FileSavePath, Forms!frmTimeCard!txtDate, Forms!frmTimeCard!txtDate)

now returns the error "Expected ="

[DrGUI]

Remove the parens:

SendMessage FileSavePath, Forms!frmTimeCard!txtDate, Forms!frmTimeCard!txtDate

For your function, add the following before the end of the function:

GetUserName = fName