SQL Select statement in a Class module need table field in If Statement

I am building a Class Module with MS Access 2007 with a VBA SQL Statement to sendsave down a file and send two different e-mails out. I keep getting Variable not defined for the field MWStatus in the Status_Tbl. I am blind to all the code now and need help. I had the SQL statement in the module behind the form and it worked, but was slowing down the form, so I am moving the SQL statement. My question is, why is the Field MWStatus in the Status_Tbl giving me the error "Variable not defined". This is my first time putting the SQL statement in VBA Module and I don't know if I did it right

Public Sub SendConfirm()
On Error GoTo Err_SendConfirm_Click

Dim Borrower As String, LOEmail As String, ProcEmail As String, ClsEmail As String, Caution As String, LNumber As Long, TheFile As String, TheName As String

'SQL Statement to get Processor and Closer email
Dim strSQL As String
Dim strCMCID As String
strCMCID = Me!CMCID_Txt.Value
strSQL = "SELECT Commitments_Tbl.CMCID, Status_Tbl.MWStatus, DBUsers_Tbl.EMail, DBUsers_Tbl_1.EMail " & _
"FROM ((Commitments_Tbl LEFT JOIN Status_Tbl ON Commitments_Tbl.LoanNumber = Status_Tbl.LoanNumber) LEFT JOIN DBUsers_Tbl AS DBUsers_Tbl_1 ON Status_Tbl.Processor = DBUsers_Tbl_1.MWName) LEFT JOIN DBUsers_Tbl ON Status_Tbl.Closer = DBUsers_Tbl.MWName " & _
"WHERE Commitments_Tbl.CMCID)= ' " & strCMCID & " ' ; "

'Message Box
Dim Msg, Style, Title, Response

LOEmail = Me!OrigID_Cbo.Column(3)
Borrower = Me!BorrNameL_Txt
LNumber = Nz(Me!LoanNumber_Txt, 0)

Msg = "Do you want to send an e-mail to Set_up?"
Style = vbYesNo
Title = "Cancel Set-Up E-Mail"
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
    GoTo line3
Else
    GoTo line4
End If

line3:
TheName = "" & Borrower & " " & LNumber & ""
TheFile = "P:\mortgage\prodcenters\LOAN ITEMS (SW)\_RateLocks_and_Changes\" & TheName & ".rtf"

DoCmd.OutputTo acOutputReport, "Confirmation_Email2", acFormatRTF, TheFile, False

If Nz(Me!InvestorID_Cbo, "Blank") = "Blank" Then
  DoCmd.SendObject , , , "CommerceMortgage@CommerceBank.com", , , "New Lock: " & Borrower & ": " & LNumber, "A rate lock confirmation has been saved down to the server at P:\mortgage\prodcenters\LOAN ITEMS (SW)\_RateLocks_and_Changes as a word document with the same name and loan number as that is the subject line of this email. Please upload it into the GDR.", -1
Else
    DoCmd.SendObject , , , "CommerceMortgage@CommerceBank.com", , , "Term Change" & ": " & Borrower & ": " & LNumber, "A rate lock confirmation has been saved down to the server at P:\mortgage\prodcenters\LOAN ITEMS (SW)\_RateLocks_and_Changes as a word document with the same name and loan number as that is the subject line of this email. Please upload it into the GDR.", True
End If

line4:
    ClsEmail = Nz([DBUsers_Tbl_1.EMail], "John.Vanesler@CommerceBank.com")
    ProcEmail = Nz([DBUsers_Tbl.EMail], "John.Vanesler@CommerceBank.com")
If Me!RateExpDate_Txt <= Date + 8 Then
    Caution = "STOP Terms Finalized:"
ElseIf ***MWStatus*** = "Closing" And Me!RateExpDate_Txt >= Date + 8 Then
    Caution = "STOP:"
Else
    Caution = ""
End If
If Me!InvestorID_Cbo = "" Then
    DoCmd.SendObject acSendReport, "Confirmation_Email", "SnapshotFormat(*.snp)", LOEmail, ProcEmail & ";" & ClsEmail, , Caution & "New Lock: " & Borrower & ": " & LNumber, , True
Else
    DoCmd.SendObject acSendReport, "Confirmation_Email", "SnapshotFormat(*.snp)", LOEmail, ProcEmail & ";" & ClsEmail, , Caution & "  " & "Term Change" & ": " & Borrower & ": " & LNumber, , True
End If

Exit_SendConfirm_Click:
    Exit Sub

Err_SendConfirm_Click:
    MsgBox Err.Description
    Resume Exit_SendConfirm_Click

End Sub

Answers


Your table, Status_Tbl, includes a field named MWStatus. And your code builds a SELECT statement which includes that field.

However, the code doesn't do anything with that SELECT statement. So when this line is evaluated ...

ElseIf MWStatus = "Closing" And Me!RateExpDate_Txt >= Date + 8 Then

... Access interprets MWStatus to be an undeclared variable.

If you want MWStatus to refer to a field returned by the SELECT query, open the query with OpenRecordset. Then you can reference the value of MWStatus in the recordset's current row ...

YourRecordsetObjectVariableName.Fields("MWStatus").Value ' .Value not strictly required here
YourRecordsetObjectVariableName!MWStatus ' same value, but more concise

I assume you are getting this error when trying to execute strSQL (which is somewhere outside this Sub). It looks like this error is showing because the field MWStatus does not exist in table Status_Tbl.


Need Your Help

RPC server is unavailable when connecting to WMI on a remote computer

c# wmi

A server had to be re-built after Hard drive crash..

avoid each row stored in multiple times into mysql database

php jquery mysql ajax pdo

Here i'm trying to validate and insert the registration details into database using ajax concept. I've tried below coding. Below coding successfully inserted the details into database and then it s...