2. ASPRunnerPro General questions
  3. Reset specific fields' values if other specific field was changed
This topic is locked

Reset specific fields' values if other specific field was changed

 		1/17/2007 6:31:16 AM
ASPRunnerPro General questions
orit author

I have the document approval application in which each department suppose to mark whether they approve specific document or not.

I want that if updated file is uploaded instead of an old file that was in the "file" field (File/Image upload field), the approval fields will be initialized = reset = cleaned, so the approval round will start from the beginning.
How to do that?
Thanks a lot
J
 		Jane 1/17/2007

Hi,
you can do in using events.

Pull old value from the database and compare it with new value. Use Before record updated event for this purpose.

Here is a sample:

strOld = "select * from TableName where " & where

set rsOld = CreateObject("ADODB.Recordset")

rsOld.Open strOld, dbConnection

if rsOld("FieldName")=dict("FieldName") then

' if record wasn't changed

else

' if record was changed

dict("ApprovalField")=""

end if

rsOld.Close : set rsOld = Nothing
orit author 1/17/2007

Thanks a lot.
I have tried the following code:

strOld = "select * from release_approval where fileID = " & dict("fileID")

set rsOld = CreateObject("ADODB.Recordset")

rsOld.Open strOld, dbConnection

if rsOld("fileName")=dict("fileName") then

' if record wasn't changed

Exit Function

else

' if record was changed

dict("fileMApproval")=""

end if

rsOld.Close : set rsOld = Nothing


and I get the error: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'fileID ='.
Can you please advise what can be the problem? The field fileID is my key field but it is not presented in the list not in the edit pages. Is it problematic?
Since the field is a file upload field, Maybe the user will replace the existing file with a new file with the same name. In this case I'm assuming that the application will not consider it as update, right? If I'm right, Is there a way to discover if the user selected in the file upload field "Update" (In contrary to "Keep), and if he did - to reset the specific fields' values?
Thanks a lot
J
 		Jane 1/17/2007

If fileID field isn't on the EDIT and ADD page use where variable instead of dict("UserID").

strOld = "select * from release_approval where " & where


Regarding the second problem.

This task can be accomplished using some custom code. However we don't have a ready to go solution for this.
orit author 1/17/2007

Thanks a lot, The first solution works. Thanks
orit author 1/17/2007

Oops, I didn't notice that the fields are reseted after any update, not only if the fileName field was updated.
My code looks like:

strOld = "select * from release_approval where " & where

set rsOld = CreateObject("ADODB.Recordset")

rsOld.Open strOld, dbConnection

if rsOld("fileName")=dict("fileName") then

' if record wasn't changed

Exit Function

else

' if record was changed

dict("fileMApproval")=""

end if

rsOld.Close : set rsOld = Nothing


What am I missing?, Why does it reset the field anyway and not only if the specific field was changed?
Thanks
Sergey Kornilov admin 1/17/2007

Noboby will be able to answer this question but you.
You need to troubleshoot this code to see what is the SQL query, what data it returns, what sections of code are executed etc.
orit author 1/17/2007

What does the following code mean:

where " & where


Thanks
Sergey Kornilov admin 1/17/2007

This code adds WHERE clause to SQL query.