Condition Based Email with Linked Table Fields

This topic is locked

Condition Based Email with Linked Table Fields	2/10/2009 5:09:24 PM
ASPRunnerPro General questions
jtksmith author <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=10883&image=1&table=forumtopics' class='bbc_emoticon' alt=':blink:' /> Greetings, ASPRunnerPro Forum. This is my first posted question. I have a two part problem that I really need some help figuring out. I guess you could say I've figured out the first part but the second part has me stumped. I have three tables: Table 1 (ReportStatus) tracks report statuses, Table 2 (Projects) contains all the projects, and Table 3 (Clients) contains the names of clients. I use a query to display the name of the Client on the Report Status list since Clients and Report Statuses are linked through the Projects. When a report status is changed to "Closed" through an edit I would like to generate an email containing the name of the Client, the Project, and the updated status of "Closed". I have figured out how to send the conditional based email using the information in this thread: Sending a conditional email using events.asp, Email by creating a BeforeEdit function and it works fine. The only problem is that I can only include fields from within the ReportStatus table. I have also figured out how to bring fields from another table into an email by using the information here: Sending Email with data entered in another table, but I could only get it to work if I used it as an AfterEdit event. I guess in simple terms, I am trying to combine the two into one. I want to send a condition based email that includes information from a linked table. Here is a sample of the event that works to send the first email: set rsOld = dal.ReportStatus.Query(where,"") if rsOld("ReportStatus")<>dict("ReportStatus") and dict("ReportStatus")="Closed" then email="me@mycompany.com" subject="Report Closed" message = message & vbcrlf & "Report Status: " & vbcrlf & dict("ReportStatus") & vbcrlf message = message & vbcrlf & "Closed Date: " & vbcrlf & dict("DateClosed") & vbcrlf message = message & vbcrlf & "Notes: " & vbcrlf & dict("Notes") & vbcrlf sendmail email, subject, message else end if rsOld.Close:set rsOld = Nothing The peice I can't get to work is including the "Client Name" and the "Project Name" in this email. Including the following code in the event `message = message & vbcrlf & "Client: " & vbcrlf & dict("Client") & vbcrlf message = message & vbcrlf & "Project: " & vbcrlf & dict("Project") & vbcrlf` results in an error on the edit page. The error read "<<< Record was NOT edited >>> Item cannot be found in the collection corresponding to the requested name or ordinal." I still get the email but it doesn't include the last two pieces of information. I'm sure the experts out there know how to do this but I certainly wouldn't put myself out there as any kind of expert. Thanks in advance!

Mark as Solved Mark as not solved

3 replies


J	Jane 2/13/2009
	Hi, please see my sample below: if oldvalues("ReportStatus")<>dict("ReportStatus") and dict("ReportStatus")="Closed" then email="me@mycompany.com" subject="Report Closed" message = message & vbcrlf & "Report Status: " & vbcrlf & dict("ReportStatus") & vbcrlf message = message & vbcrlf & "Closed Date: " & vbcrlf & dict("DateClosed") & vbcrlf message = message & vbcrlf & "Notes: " & vbcrlf & dict("Notes") & vbcrlf str = "select Client, Project from TableA where RecordID_A=" & dict("RecordID_B") Set rstmp = server.CreateObject("ADODB.Recordset") rstmp.open str,dbConnection message = message & vbcrlf & "Client: " & vbcrlf & rstmp("Client") & vbcrlf message = message & vbcrlf & "Project: " & vbcrlf & rstmp("Project") & vbcrlf rstmp.close set rstmp=nothing sendmail email, subject, message end if


	jtksmith author 2/15/2009
	Thank you Jane, your response was helpful. For some reason it didin't want to work exactly that way and I kept getting a SQL query error about a Syntax Error and a missing operator. Sorry, I didn't save the error. Your response did inspire me so I played around with it and tried something slightly different. Since I already had a "Save old data record in another table" event on the same form, I tried moving the "rsFields.close: set rsFielgs=nothing" below my send email event and changed and it worked. Here is my code now: [codebox]if oldvalues("ReportStatus")<>dict("ReportStatus") and dict("ReportStatus")="Closed" then email="me@mycompany.com" subject="Report Closed" message = message & vbcrlf & "Report Status: " & vbcrlf & dict("ReportStatus") & vbcrlf message = message & vbcrlf & "Closed Date: " & vbcrlf & dict("DateClosed") & vbcrlf message = message & vbcrlf & "Notes: " & vbcrlf & dict("Notes") & vbcrlf str = "select Client, Project from TableA where RecordID_A=" & dict("RecordID_B") Set rstmp = server.CreateObject("ADODB.Recordset") rstmp.open str,dbConnection message = message & vbcrlf & "Client: " & vbcrlf & rstmp("Client") & vbcrlf message = message & vbcrlf & "Project: " & vbcrlf & rstmp("Project") & vbcrlf rstmp.close set rstmp=nothing sendmail email, subject, message end if rsFields.close: set rsFielgs=nothing End Function ' BeforeEdit[/codebox] I suppose this isn't the cleanest programming but it's working. I wonder why it wasn't working the other way. I'm also curious why the example events have "set rsFielgs=nothing" instead of "set rsFields=nothing". Is it just a typo? It doesn't seem to affect functionality. Thanks again for your help and inspiration.


	jtksmith author 2/15/2009
	Jane, Sorry, my previous post had one small difference from my working code. In order to get it to work, I had to change the "dict" to "rsFields" in the "select" statement to get it to work. Here is what I really did: [codebox]if oldvalues("ReportStatus")<>dict("ReportStatus") and dict("ReportStatus")="Closed" then email="me@mycompany.com" subject="Report Closed" message = message & vbcrlf & "Report Status: " & vbcrlf & dict("ReportStatus") & vbcrlf message = message & vbcrlf & "Closed Date: " & vbcrlf & dict("DateClosed") & vbcrlf message = message & vbcrlf & "Notes: " & vbcrlf & dict("Notes") & vbcrlf str = "select Client, Project from TableA where RecordID_A=" & ("RecordID_B") Set rstmp = server.CreateObject("ADODB.Recordset") rstmp.open str,dbConnection message = message & vbcrlf & "Client: " & vbcrlf & rstmp("Client") & vbcrlf message = message & vbcrlf & "Project: " & vbcrlf & rstmp("Project") & vbcrlf rstmp.close set rstmp=nothing sendmail email, subject, message end if [color=#FF0000]rsFields.close: set rsFielgs=nothing[b] End Function ' BeforeEdit[/codebox] Thanks again, Jeremy

3 replies

Mark as Solved Reply