Agent that will update all docs in a database

I'm using the code below to connect to an AS/400 thru an odbc connection. I put this code into an agent and it works fine if I have the document open in edit mode. Is it possible to run the agent from a view and have it update all the documents in the database? When I select a document in a view and run the agent I get an error message "Object variable not set" Looking at this in the lotusscript debugger I can see that it bombs out on the Qry.SQL statement. What am I missing to get an agent to act on every document in the database?

Sub Initialize
      Dim A As String       '*LIMIT STATUS
      Dim H As String
      Dim O As String
      Dim P As String
      Dim S As String
      Dim T As String
      Dim Q As String
      Dim W As String
      A = "A"
      H = "H"
      O = "O"
      P = "P"
      S = "S"
      T = "T"
      Q = "Q"
      W = "W"
      Dim con As New ODBCConnection
      Dim qry As New ODBCQuery
      Dim res As New ODBCResultSet
      Dim WS As New notesUIWorkspace
      Dim uidoc As NotesUiDocument
      Dim doc As NotesDocument
      Set uidoc = ws.currentdocument
      'Set doc = uidoc.Document
If con.geterror<>DBstsSuccess Then
Messagebox con.GetExtendedErrorMessage,,"Could not make connection to AS/400 system"
            Exit Sub
      End If
      Set qry.Connection = Con
      Qry.SQL = "select * from SOFTTURND.TPRJTSK where TRSRC='"+uidoc.fieldgettext("resource")+"'" + "ORDER BY TPTY, TPROJ"
      If qry.geterror<>DBstsSuccess Then
            Messagebox qry.GetExtendedErrorMessage,,"SQL Error"
            Exit Sub
      End If
      Set Res.Query = Qry
      If res.GetError<>DBstsSuccess Then
            Messagebox res.GetExtendedErrorMessage,,"Result Error"
            Exit Sub
            If Not res.IsResultSetAvailable Then
                  Messagebox "No Values Found for your Query"
                  Dim Counter As Integer
                  Counter = 1
                        Call uidoc.FieldSetText("project_" + Cstr(Counter), "")
                        Call uidoc.FieldSetText("task_" + Cstr(Counter),  "")
                        Call uidoc.FieldSetText("description_" + Cstr(Counter), "")
                        Call uidoc.FieldSetText("requester_" + Cstr(Counter),  "")
                        Call uidoc.FieldSetText("priority_" + Cstr(Counter), "")
                        Call uidoc.FieldSetText("status_" + Cstr(Counter),  "")
                        Counter = Counter + 1                                    
                  Loop Until Counter = 49
                  Counter = 1                  
                        If Res.GetValue("TSTS") = S  Or Res.GetValue("TSTS") = H Or Res.GetValue("TSTS") = A Or Res.GetValue("TSTS") = O Or Res.GetValue("TSTS") = P Or Res.GetValue("TSTS") = T Or Res.GetValue("TSTS") = Q Or Res.GetValue("TSTS") = W Then
                              Call uidoc.FieldSetText("project_" + Cstr(Counter), Res.GetValue("TPROJ"))
                              Call uidoc.FieldSetText("task_" + Cstr(Counter),  Res.GetValue("TTASK"))
                              Call uidoc.FieldSetText("description_" + Cstr(Counter),  Res.GetValue("TDESC"))
                              Call uidoc.FieldSetText("requester_" + Cstr(Counter),  Res.GetValue("TRQSTR"))
                              Call uidoc.FieldSetText("priority_" + Cstr(Counter),  Res.GetValue("TPTY"))

Call uidoc.FieldSetText("status_" + Cstr(Counter),  Res.GetValue("TSTS"))
Counter = Counter + 1      
End If
Loop Until res.IsEndOfData Or Counter = 49
                  Counter = Counter - 1
Call uidoc.FieldSetText("count",Cstr(Counter))      
Call uidoc.FieldSetText("date" , Cstr(Date$))
      End If
      End If
End Sub
Who is Participating?
ArunkumarConnect With a Mentor Commented:
Hi Roosterup,

Include these lines to refer doc(The document you select from the view)

Dim ss As New notessession          
Dim db As notesdatabase
Set db = ss.CurrentDatabase
Set dc = db.UnprocessedDocuments
Set doc = dc.GetFirstDocument

Change all the
Call uidoc.FieldSetText("FieldName",Value) lines to read as

doc.FieldName = Value

MAKE Sure your lines for
Qry.SQL reads as follows

Qry.SQL = "select * from SOFTTURND.TPRJTSK where TRSRC = '"+ doc.resource(0) +"'" + "ORDER BY TPTY, TPROJ"

Also at the end of the script make sure you use the following to save the document

Call doc.Save(True,False)

Good Luck !


PS: These Code changes will work for only one document that is selected in the view since we are not looping through documents.
roosterupAuthor Commented:
I would actually like to run this agent on a schedule and have it update all the documents.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.