Canceled Previous Operation - Error (1 Viewer)

[forms_are_nightmares]

I believe this is just a coding issue and that I'm overlooking something very basic.

Scenario: I have an After Update function on a text box on a form that triggers an email. The VB code pulls information contained on the form and either puts it in the subject line, the TO field, and in the body of the email.

Problem: I want to add addresses to the CC field. These addresses come from 2 different tables. When the After Update executes, I get a Canceled Previous Operation error.

Here's the code (after all the declarations). It fails on the varCC line

response = MsgBox(msg, button, title)
If response = vbYes Then

staccount = Forms!frm_priority_worksheet.AName
stStore = Forms!frm_priority_worksheet.Store
ststate = Forms!frm_priority_worksheet.State
stWho = Forms!frm_priority_worksheet.AName
stWhere = "tbl_approver.account = " & "'" & stWho & "'"

varCC = DLookup("Approver", "tbl_approver", stWhere) And DLookup("Fmgr", "tbl_fmgr", ststate)



stSubject = "Closed Sourcing Request: " & staccount & " / Store: " & stStore & " / State: " & ststate


RecDate = Forms!frm_priority_worksheet.RDate
Spec = Forms!frm_priority_worksheet.PSpecialist
stText = "This sourcing request has been closed." & Chr$(13) & _
Chr$(13) & "Requested Date: " & RecDate & Chr$(13) & _
Chr$(13) & "Procurement Specialist: " & Spec & " "


DoCmd.SendObject , , acFormatTXT, varTo, varCC, , stSubject, stText, -1

Any help is greatly appreciated. All other code works properly

 

[boblarson]

If you want to concatenate the two DLookups you will need to use an AMPERSAND (&) and not the word AND. If that isn't what you want then we need better info because it makes no sense as is:

varCC = DLookup("Approver", "tbl_approver", stWhere) And DLookup("Fmgr", "tbl_fmgr", ststate)

needs to be

varCC = DLookup("Approver", "tbl_approver", stWhere) & DLookup("Fmgr", "tbl_fmgr", ststate)

 

[forms_are_nightmares]

The & didn't work. What I'm trying to do is pull email addresses from two tables based on criteria in the form.

In tbl_approver, there are addresses based on client names and in tbl_mgr, there are addresses based on state.

The prevailing idea is to pull the appropriate email address from each table and insert those into the CC line of the email.

For eaxmple:

pull test@email.com from tbl_approver (based on the Dlookup)
pull test2@email.net from tbl_fmgr (based on a separate Dlookup)

and then insert both of those addresses on the CC line.

Hopefully, this adds some clarity.

 

[boblarson]

Then that line would need to be:

[SIZE=1][COLOR=#ff0000]varCC = Nz(DLookup("Approver", "tbl_approver", stWhere),"")[/COLOR][/SIZE]
[SIZE=1][COLOR=#ff0000][/COLOR][/SIZE] 
[SIZE=1][COLOR=#ff0000]If varCC <> "" Then[/COLOR][/SIZE]
[SIZE=1][COLOR=#ff0000]varCC = varCC &  ";" & Nz([/COLOR][COLOR=#ff0000]DLookup("Fmgr", "tbl_fmgr", ststate),"")[/COLOR][/SIZE]
[SIZE=1][COLOR=#ff0000]End If[/COLOR][/SIZE]
[SIZE=1][COLOR=#ff0000]

[/COLOR][/SIZE]

 

[forms_are_nightmares]

I've inserted the code and still recieve the error.

Here's the code (with your suggestion)

msg = "Are you sure you want to close the request?"
button = vbYesNo + vbDefaultButton2
title = "Close Request"
response = MsgBox(msg, button, title)
If response = vbYes Then

staccount = Forms!frm_priority_worksheet.AName
stStore = Forms!frm_priority_worksheet.Store
ststate = Forms!frm_priority_worksheet.State
stWho = Forms!frm_priority_worksheet.AName
stWhere = "tbl_approver.account = " & "'" & stWho & "'"

'varCC = DLookup("Approver", "tbl_approver", stWhere) & DLookup("Fmgr", "tbl_fmgr", ststate)

varCC = Nz(DLookup("Approver", "tbl_approver", stWhere), "")
If varCC <> "" Then
varCC = varCC & ";" & Nz(DLookup("Fmgr", "tbl_fmgr", ststate), "")
End If

stSubject = "Closed Sourcing Request: " & staccount & " / Store: " & stStore & " / State: " & ststate


RecDate = Forms!frm_priority_worksheet.RDate
Spec = Forms!frm_priority_worksheet.PSpecialist
stText = "This sourcing request has been closed." & Chr$(13) & _
Chr$(13) & "Requested Date: " & RecDate & Chr$(13) & _
Chr$(13) & "Procurement Specialist: " & Spec & " "


DoCmd.SendObject , , acFormatTXT, varTo, varCC, , stSubject, stText, -1

ElseIf response = vbNo Then
Me.Undo

End If

 

[ghudson]

I would test and see what value the dlookups are pulling in. My guess that is where your problem is. Add these to your code and the results will print in the immediate window of the vba editor.

debug.print "Approver = " & Nz(DLookup("Approver", "tbl_approver", stWhere), "")

debug.print "Fmgr= " & Nz(DLookup("Fmgr", "tbl_fmgr", ststate), "")

 

[boblarson]

I would test and see what value the dlookups are pulling in. My guess that is where your problem is. Add these to your code and the results will print in the immediate window of the vba editor.

debug.print "Approver = " & Nz(DLookup("Approver", "tbl_approver", stWhere), "")
 
debug.print "Fmgr= " & Nz(DLookup("Fmgr", "tbl_fmgr", ststate), "")

Well, I can see immediately without anything else that the WHERE clause part of the dlookups is malformed for Dlookup.

 

[forms_are_nightmares]

I think I found out the issue, the error occurs on this line:

Nz(DLookup("Fmgr", "tbl_fmgr", ststate), "")

I tested it by itself and it failed. I tested the other one and it worked fine. So I need to look at the above code and see if it's correct

 

[forms_are_nightmares]

Figured it out....

varCC = Nz(DLookup("Fmgr", "tbl_fmgr", ststate), "") should be

varCC = Nz(DLookup("Fmgr", "tbl_fmgr", State), "")

thanks for your help