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

i building class module ms access 2007 vba sql statement sendsave down file , send 2 different e-mails out. keep getting variable not defined field mwstatus in status_tbl. blind code , need help. had sql statement in module behind form , worked, slowing down form, moving sql statement. question is, why field mwstatus in status_tbl giving me error "variable not defined". first time putting sql statement in vba module , don't know if did right

public sub sendconfirm() on error goto err_sendconfirm_click  dim borrower string, loemail string, procemail string, clsemail string, caution string, lnumber long, thefile string, thename string  'sql statement processor , closer email dim strsql string dim strcmcid 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 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 want send e-mail set_up?" style = vbyesno title = "cancel set-up e-mail" response = msgbox(msg, style, title) if response = vbyes     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"   docmd.sendobject , , , "commercemortgage@commercebank.com", , , "new lock: " & borrower & ": " & lnumber, "a rate lock confirmation has been saved down server @ p:\mortgage\prodcenters\loan items (sw)\_ratelocks_and_changes word document same name , loan number subject line of email. please upload gdr.", -1 else     docmd.sendobject , , , "commercemortgage@commercebank.com", , , "term change" & ": " & borrower & ": " & lnumber, "a rate lock confirmation has been saved down server @ p:\mortgage\prodcenters\loan items (sw)\_ratelocks_and_changes word document same name , loan number subject line of email. please upload 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     caution = "stop terms finalized:" elseif ***mwstatus*** = "closing" , me!rateexpdate_txt >= date + 8     caution = "stop:" else     caution = "" end if if me!investorid_cbo = ""     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 

your table, status_tbl, includes field named mwstatus. , code builds select statement includes field.

however, code doesn't select statement. when line evaluated ...

elseif mwstatus = "closing" , me!rateexpdate_txt >= date + 8 

... access interprets mwstatus undeclared variable.

if want mwstatus refer field returned select query, open query openrecordset. can reference value of mwstatus in recordset's current row ...

yourrecordsetobjectvariablename.fields("mwstatus").value ' .value not strictly required here yourrecordsetobjectvariablename!mwstatus ' same value, more concise