VBA + Union Query 1

[Melagan]

Greetings,

This is a combination VBA and Union Query issue that I'm dealing with, but the run-time error is SQL related so I figured this would be the best place to start. Here goes!

I have written some code at the module level to loop through a recordset which contains names and email addresses, and another function to send emails via CDO. The code is called from a form on a textbox control's OnChanged event.

Here are the functions:

Public Sub SendEmail(TheName As String, TheAddress As String)
Dim rcpt, sender As String
Dim objMessage As CDO.Message
Dim strSMTP As String
strSMTP = "mail.nat.com"
rcpt = Chr(34) & TheName & Chr(34) & "<" & TheAddress & ">"
sender = Chr(34) & "NATTrack" & Chr(34) & "<jhenry@nat.com>"
Set objMessage = CreateObject("CDO.Message")
    With objMessage
        .Subject = "Escrow " & gstrEscrowNum & " Cancelled."
        .From = sender
        .To = rcpt
        .HTMLBody = "<h1>This escrow has been cancelled in NATTrack.</h1>"
        .Configuration.Fields.Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")[/URL] = 2
        'Name or IP of remote SMTP server
        .Configuration.Fields.Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] = strSMTP
        'Server port
        .Configuration.Fields.Update
        .Send
    End With

End Sub

Public Sub SendMsg()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdfSQL As DAO.QueryDef
    Set db = CurrentDb
    Set qdfSQL = db.QueryDefs("qryEmailTo")
    Set rst = qdfSQL.OpenRecordset

    If Not rst.EOF Then
        Do While Not rst.EOF
            SendEmail rst("First_Last"), rst("E-Mail Address")
            rst.MoveNext
        Loop
    End If
Set rst = Nothing
Set qdfSQL = Nothing
Set db = Nothing

End Sub

Now, qryEmailTo: (this is where the problem seems to be)

SELECT u.First_Last, u.[E-mail Address]
FROM Escrows e INNER JOIN tblUsers u ON e.[EO Initials] = u.[Initials]
WHERE e.[Escrow Number] = "'" & gstrEscrowNum & "'"
UNION SELECT u.First_Last, u.[E-mail Address]
FROM Escrows e INNER JOIN tblUsers u ON e.[PDC_Rep] = u.[First_Last]
WHERE e.[Escrow Number] = "'" & gstrEscrowNum & "'"

gstrEscrowNum is a public variable that is populated with the above mentioned OnChange event as well. (Escrow Number is my table's primary key)

In an earlier stage while I was testing, I was not using a global variable, but rather pulling the value from the form itself and passing it into the query, like so:
WHERE e.[Escrow Number] = "'" Forms![Escrow Entry]![Escrow Number]
Both iterations, however, produced the same error.
Run-time error 3601 - Too few parameters. Expected 1.

Oddly enough, when I was passing the form value straight into the query and ran the query manually (with the form open), the union query produced expected results.

What gives?

~Melagan
______
"It's never too late to become what you might have been.

 

[JoeAtWork]

You can't put a variable into a saved query, you will need to make a public function that returns the variable, like so:

Public Function GetEscrowNumber() As String
   GetEscrowNumber = gstrEscrowNum
End Function

And in the query:

SELECT u.First_Last, u.[E-mail Address]
FROM Escrows e INNER JOIN tblUsers u ON e.[EO Initials] = u.[Initials]
WHERE e.[Escrow Number] = "'" & gstrEscrowNum & "'"
UNION SELECT u.First_Last, u.[E-mail Address]
FROM Escrows e INNER JOIN tblUsers u ON e.[PDC_Rep] = u.[First_Last]
WHERE e.[Escrow Number] = "'" & [COLOR=red]GetEscrowNumber()[/color] & "'"

 

[Melagan]

Nice. I created the function as suggested. The WHERE clauses in my UNION query are now as follows:
WHERE e.[Escrow Number] = GetEscrowNum()

Works perfectly now - thanks!


~Melagan
______
"It's never too late to become what you might have been.