2. ASPRunnerPro General questions
  3. Conditional Formatting & After Edit Event
This topic is locked

Conditional Formatting & After Edit Event

 		4/24/2007 3:39:59 AM
ASPRunnerPro General questions
R
 rustygrooser author

I am on trial for ASP Runner Pro 4.1. Thank you for this great software I was able to upgrade our tracking database to a web-based database even without any knowledge of ASP!
I have two problems and I hope you guys can help me:

  1. My database is a tracking database - i.e. we use it to track incomplete jobs and target dates. In my access database, the target date was conditionally formatted to be highlighted if it is <NOW(). I want the same thing to happen here in my ASP generated program. I want the target dates value to be colored red if they are already due (<Now()). How can I do it?
  2. In my access database, after update on remarks field a VB script code will generate an e-mail to notify me of the update. Here in ASP, I was able to create a EditOnLoad event where an e-mail notification will be sent to me when a user will attempt to make an update and append the old records for my verification later on. This is ok. But I want it better if after saving the new data will be sent to me instead.
    Here's my code:
    [codebox]<%
    Sub EditOnLoad()
    ' Parameters:

    ' where - string with WHERE clause pointing to record to be edited

    '** Send email with old data record ****

    ' do not forget to setup email parameters like From, SMTP server etc

    ' on 'Security->User login settings' dialog
    set rsOld = CreateObject("ADODB.Recordset")

    set keys = CreateObject("Scripting.Dictionary")

    keys("FollowUpMecahnismID")=postvalue("editid1")
    email="myusername@my domain.com"

    message="An edit attempt is in progress for the following.»»»"

    subject="Warning! Record to be updated."
    ' modify the following SQL query to select fields you like to send

    sstrWhere=KeyWhere(keys,"")

    rsOld.Open "select * from " & AddTableWrappers(strOriginalTableName) & " where " & sstrWhere, dbConnection

    if not rsOld.eof then

    for i=0 to rsOld.Fields.Count-1

    if not IsBinaryType(rsOld.Fields(i).Type) then _

    message = message & rsOld.Fields(i).Name & " : " & rsOld(rsOld.Fields(i).Name) & vbcrlf

    next

    rsOld.Close

    end if

    set rsOld = Nothing
    sendmail email, subject, message
    End Sub
    %>[/codebox]<%
J
 		Jane 4/24/2007

Hi,
please see my answers below:

  1. to highlight fields proceed to the Visual Editor tab, double click on the field, select Custom on the "View as" settings dialog and add your code in it.

    Here is a sample:
    if strValue<Now() then

    strValue = "<font color=red>" & strValue & "</font>"

    end if


2. to send email with new data use Send email with new data action for the Before record updated event on the Events tab.
R
 		rustygrooser author 4/24/2007

Thank you for your quick reply.
No. 1 did fine. Great!
No. 2 needs more help. I do not how to configure "dict.keys" in this sense. What shall I put from my code above?
[codebox]Function BeforeEdit(dict, where)
' Parameters:

' dict - Scripting.Dictionary object.

' Each field on the Edit form represented as 'Field name'-'Field value' pair

' where - string with WHERE clause pointing to record to be edited
'** Send email with new data ****

' do not forget to setup email parameters like From, SMTP server etc

' on 'Security->User login settings' dialog
Dim keys

message =""
keys = dict.keys

For n = 0 To dict.Count-1

message = message & keys(n) & " : " & dict(keys(n)) & vbcrlf

Next
email="test@test.com"

subject="New data record"
sendmail email, subject, message
BeforeEdit = True
' set BeforeEdit to True if you like to proceed with editing this record

' set it to False in other case
End Function[/codebox]
J
 		Jane 4/24/2007

All entered values are stored in the dict collection. Just replace email, message and subject variables with your values in the sample code:

Function BeforeEdit(dict, where)

' Parameters:

' dict - Scripting.Dictionary object.

' Each field on the Edit form represented as 'Field name'-'Field value' pair

' where - string with WHERE clause pointing to record to be edited
'** Send email with new data ****

' do not forget to setup email parameters like From, SMTP server etc

' on 'Security->User login settings' dialog
Dim keys

message="An edit attempt is in progress for the following.»»»"
keys = dict.keys

For n = 0 To dict.Count-1

message = message & keys(n) & " : " & dict(keys(n)) & vbcrlf

Next
email="myusername@my domain.com"

subject="Warning! Record to be updated."
sendmail email, subject, message
BeforeEdit = True
' set BeforeEdit to True if you like to proceed with editing this record

' set it to False in other case
End Function
R
 		rustygrooser author 4/25/2007

Thanks! It works.
Now Ms. Jane, I have still some loopholes I need to refer:
The e-mail is working alright but it is only showing the updated fields. I have to include my record no and other required fields for references. How can I do this from my code above? And how about if I will just put the record number which is updated in the subject field. I appreciate always your quick response. You guys are doing great works!
J
 		Jane 4/25/2007

Hi,
to select values from database in the BeforeEdit event use following code:

str = "select * from " & strTableName & " where " & where

Set rsTemp = server.CreateObject("ADODB.Recordset")

rsTemp.open str, dbConnection
'add record ID to the subject

subject="Warning! Record # " & rsTemp("FieldID") & " to be updated."
rsTemp.Close : set rsTemp = Nothing



where FieldID is your actual field name.
R
 		rustygrooser author 4/25/2007

Thanks! Again its working.
Now, another thing. All updates are coming from my e-mail address as admin. How can I set updates from users to come from their respective e-mail?
Sergey Kornilov admin 4/25/2007

Try to add the following line right before you can Sendmail function:

cfrom = dict("UserEmailField")
R
 		rustygrooser author 4/26/2007

Try to add the following line right before you can Sendmail function:


cfrom = dict("UserEmailField")


It is not working.
J
 		Jane 4/26/2007

Did you replace UserEmailField with your actual field name where user email is stored?