Automatically moving from record to record

[RDWilson2]

I have a form that is bound to a table. The table contains some information that lets me know if either or both of two processes have been executed for a given filename. I have progressively added more and more automation to this project but now I've drawn a blank on what I am trying to to add one more bit of automation.

At this point, if the first process has not been initiated for a given file, it is initiated. When the first process ends, it initiates the second process. What I am wanting to do is to move to the next record in the table upon the completion of the second process (assuming that there is a "Next Record").

I figure this has got to be possible and is probably really simple . . . but, as I have said, I have simply drawn a blank.

Right now, this application is in Access 97 but I anticipate having to convert it to/redevelope it in Access 2003.

 

[Zion7]

If I understand correctly, open recordset & loop thru. You said, the table holds records of the status , of which processes have been initiated.

Dim rec As ADODB.Recordset
Set rec = New ADODB.Recordset

rec.Open "tblName", acOpenDynaset, acLockOptimistic

Do Until rec.EOF

If rec!FieldName = "initiated" Then
.....

rec.MoveNext
Loop

rec.Close
Set rec = Nothing

Hope this helps, good luck!

 

[RDWilson2]

The problem is that, under certain circumstances (e.g. partial processing of the file) the user needs to open the same form and manually initiate a failed portion of the process. The form is currently bound to the table which is used to indicate whether each stage of the processing has been done and the file on which the processing has acted.

Essentially, at this time, there are two (2) buttons each of which initiates a portion of the processing. Button 1 initiates a preparation phase that then triggers an upload to a Teradata database and then the execution of a SQL script that exports results to the PC. Button 2 initiates a post-export processing (by means of a SQL Script executed using QueryMan) of the exported data in preparation for providing a results set (via ftp) to an ultimate end user. There are two checkboxes corresponding to the two buttons and whether or not the associated phase has completed.

I had used this design to automate a previously totally manual series of steps. I have now added some additional automation in the "On Current" event for the form that will kick off phase 1 if it has not been successfully completed and, if it has, will kick off phase two if that phase has not been successfully completed. What I am now trying to do is to trigger the form's access of the next logical record upon the completion of phase 2 (which will, in theory, then trigger the phase 1 processing for that next logical record).

Adding the suggested code would probably address the situation in a way, however, I am also running into a problem because control is returned to Access after the SQL Script is initiated in QueryMan but before the script finishes. Therefore, I rather suspect that putting the two phases in a loop would have simialar problems and would result in additional spool space problems.

This has evolved over a period of time and, although it might be an optimum solution to rewrite the whole process, theoretically this Access solution is going to be phased out shortly in lieu of a better solution.