DLookup peculiarities

[Lou_Reed]

Private Sub cmdMailTicket_Click()
    On Error GoTo Err_cmdMailTicket_Click
    Dim stWhere As String       '-- Criteria for DLookup
    Dim varTo As Variant        '-- Address for SendObject
    Dim stText As String        '-- E-mail text
    Dim RecDate As Variant      '-- Rec date for e-mail text
    Dim stSubject As String     '-- Subject line of e-mail
    Dim stTicketID As String    '-- The ticket ID from form
    Dim stWho As String         '-- Reference to tblUsers
    Dim stHelpDesk As String    '-- Person who assigned ticket
    Dim strSQL As String        '-- Create SQL update statement
    Dim errLoop As Error

    '-- Combo of names to assign ticket to
    stWho = Me.cboAssignee
    stWhere = "tblUsers.strUserID = " & "'" & stWho & "'"
    '-- Looks up email address from TblUsers
    varTo = DLookup("[strEMail]", "tblUsers", stWhere)

    stSubject = ":: New Help Desk Ticket ::"

    stTicketID = Format(Me.txtTicketID, "00000")
    RecDate = Me.txtDateReceived
    '-- Helpdesk employee who assigns ticket
    strHelpDesk = Me.cboReceivedBy.Column(1)

    stText = "You have been assigned a new ticket." & Chr$(13) & Chr$(13) & _
             "Ticket number: " & stTicketID & Chr$(13) & _
             "This ticket has been assigned to you by: " & strHelpDesk & Chr$(13) & _
             "Received Date: " & RecDate & Chr$(13) & Chr$(13) & _
             "This is an automated message. Please do not respond to this e-mail."

    'Write the e-mail content for sending to assignee
    DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, -1

    'Set the update statement to disable command button
    'once e-mail is sent
    strSQL = "UPDATE tblHelpDeskTickets SET tblHelpDeskTickets.ysnTicketAssigned = -1 " & _
             "Where tblHelpDeskTickets.lngTicketID = " & Me.txtTicketID & ";"

    On Error GoTo Err_Execute
    CurrentDb.Execute strSQL, dbFailOnError
    On Error GoTo 0

    'Requery checkbox to show checked
    'after update statement has ran
    'and disable send mail command button
    Me.chkTicketAssigned.Requery
    Me.chkTicketAssigned.SetFocus
    Me.cmdMailTicket.Enabled = False

    Exit Sub
Err_Execute:

    ' Notify user of any errors that result from
    ' executing the query.
    If DBEngine.Errors.Count > 0 Then
        For Each errLoop In DBEngine.Errors
            MsgBox "Error number: " & errLoop.Number & vbCr & _
                   errLoop.Description
        Next errLoop
    End If

    Resume Next

Exit_cmdMailTicket_Click:
    Exit Sub

Err_cmdMailTicket_Click:
    MsgBox Err.Description
    Resume Exit_cmdMailTicket_Click

End Sub

I have some questions that I need to ask about the code shown above. I really want to narrow it down to just as small
part of the code.


I really want to discuss the following section of that code as shown below.

 '-- Combo of names to assign ticket to
    stWho = Me.cboAssignee
    stWhere = "tblUsers.strUserID = " & "'" & stWho & "'"
    '-- Looks up email address from TblUsers
    varTo = DLookup("[strEMail]", "tblUsers", stWhere)

First, what is the purpose of the line

stWhere = "tblUsers.strUserID = " & "'" & stWho & "'"

It seems that o the email address is already known from a field on the table. Why is this line needed.

Second the line:

arTo = DLookup("[strEMail]", "tblUsers", stWhere)

I realize and accept that that it is a DLookuo function. But why all the extra
quotes and brackets. The first entry has

("[strEMail]"

has quotes and brackets around it. This seems overkill. The second entry

"tblUsers"

has only quotes, while the third entry

stWhere

is just there no quotes or brackets. It seems It just seems overkill on the first two entrees, especially the first one. I
have seen entries on the DLookup function with neither brackets or quotes
so why all of this extra stuff?

Any help appreciated. Thanks in advance.

This code does compile and work so there are no errors in it.

Respectfully,

Lou Reed

[pbaldy]

Looking only at that code block, it appears that the user is known, their email address is not. The overall purpose of that bit is to get the email address associated with the user. Some people prefer to build the criteria separately, and then use it in the DLookup(). That's what's happening with:

stWhere = "tblUsers.strUserID = " & "'" & stWho & "'"

As to the quotes and brackets in:

arTo = DLookup("[strEMail]", "tblUsers", stWhere)

The brackets are not necessary in this case. They could be if there were spaces or symbols in the names. Whoever wrote this was inconsistent in their use, but generally they are not required. Each argument of the DLookup() must be in quotes. In this instance, strWhere is a variable so it doesn't have them, but they were included when it was built.