How to resume a record set where a macro left off?

[MCTampa]

I have a VBA script that loops through our AS400 and changesfields based on a set of criteria loaded into a form.
There are certain times when our AS400 glitches and presentsa half screen of information. At thispoint, I want to back out of the screen and restart the process.However, if I have 5 records loaded in myform, it starts from the beginning.
I would like to have it resume the recordset where it leftoff, but I do not know how to write that.

Edit: I don’t knowwhy the website is fighting with me, but I can’t get my code in one box. Sorry.
I want to simply have the code resume the current rst atafter the “COM”, “PF2” command at the end.


Thanks

Here is my code:

Function StopSale()
Dim MySession As New AutSess
Dim SessionName As String
Dim FirstEntry, LastEntry As Integer
SessionName = "A"

[FONT=Calibri][SIZE=3][COLOR=#000000]MySession.SetConnectionByName (SessionName)
Set OIA = MySession.autECLOIA
Set PS = MySession.autECLPS[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]OIA.WaitForAppAvailable
OIA.WaitForInputReady[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim cmd1 As ADODB.Command
Set cmd1 = New ADODB.Command
cmd1.ActiveConnection = CurrentProject.Connection[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim rst2 As ADODB.Recordset
Set rst2 = New ADODB.Recordset[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]With rst[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]rst.Open "select Entry,Resort_Code,AS400SD,End_Date,Unit_Size,Flag,Return,Track_Code,Res_Line,Unit_Num from Log ", CurrentProject.Connection, adOpenStatic, adLockOptimistic
Do Until rst.EOF = True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Call sendToNa("PUT", "400", 21, 18, 0, 0) 'Go to Option 400
Call sendToNa("COM", "enter", 0, 0, 0, 0)
Call sendToNa("PUT", .Fields("Resort_Code"), 6, 43, 0, 0) 'Pass Resort ID
Call sendToNa("PUT", .Fields("AS400SD"), 8, 43, 0, 0) 'Pass Start Date
If .Fields("Track_Code") = "ALL" Then Call sendToNa("PUT", "*", 10, 43, 0, 0):  Call sendToNa("COM", "enter", 0, 0, 0, 0) _
Else: Call sendToNa("COM", "PF4", 0, 0, 0, 0) 'Prompt for Track Code selection
If .Fields("Track_Code") = "C" Then Call sendToNa("PUT", "X", 16, 37, 0, 0) _
Else: If .Fields("Track_Code") = "T" Then Call sendToNa("COM", "Roll Up", 0, 0, 0, 0): Call sendToNa("COM", "Roll Up", 0, 0, 0, 0): Call sendToNa("PUT", "X", 18, 37, 0, 0)
 Call sendToNa("COM", "enter", 0, 0, 0, 0)
 Call sendToNa("COM", "enter", 0, 0, 0, 0)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]
If .Fields("Unit_Size") = "9" And .Fields("Return") = "U" Then
'This macro returns all unit sizes[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Do
If PS.GetText(14, 68, 1) = " " And DateValue(PS.GetText(7, 51, 2) & " - " & PS.GetText(7, 53, 3) & " - " & PS.GetText(7, 56, 2)) <= .Fields("End_Date") Then Call sendToNa("COM", "PF2", 0, 0, 0, 0): Call sendToNa("COM", "PF2", 0, 0, 0, 0): rst.Requery[/COLOR][/SIZE][/FONT]

 

[welshgasman]

I seem to recall that the AS400 had no concept of checkpoints.? You backed up the data, and if it fell over, you restored the data and started again.?
So back up the tables first, if it fails, restore the tables.

Alternatively investigate Transactions in Access. Not something I have ever had to use though.
https://codekabinett.com/rdumps.php?Lang=2&targetDoc=how-to-access-transaction

Another way might be to update a field and check for that being updated before applying the process.?

 

[xenou]

I was also thinking in terms of the last statement above -- if your query can separate the "done" vs. "not done" records then you will always only be updating records that need to be updated and ignoring anything else.

 

[MCTampa]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Entry
[/TD]
[TD]Resort
[/TD]
[TD]Status
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]ABC
[/TD]
[TD]Done
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]DEF
[/TD]
[TD]To Be Done
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]GHI
[/TD]
[TD]To Be Done
[/TD]
[/TR]
</tbody>[/TABLE]


You guys gave me some great ideas! Thanks!
Here is what I’m stuck on:

Each time a user selects the criteria, they insert those values into a table, which is displayed on my form so the user can verify thatthe entries are correct for what they want to do. The VBA then loops through the form until the recordset isdone, applying the criteria in each column to the AS400. Here is what I think would work, but I’m not sure how toexecute it:

When the analyst writes a set of criteria, I am going tohave a status column which reads “To Be Done”.
When the macro finishes that record, before moving to thenext, it should change that status to “Done”.
Then in the event the macro has to exit and resume, itshould only resume on statuses marked “To Be Done”.
The table in my form is called Log and looks something like the above.

What I need to do is have the macro edit the CURRENT RECORDit is working on.
Meaning if it’s on row 1, change the Status from To Be Doneto Done when it finishes that line.
In my code, I have the following line to access thevariables within the table Log.

[FONT=Calibri][SIZE=3][COLOR=#000000]rst.Open "select Entry,Resort_Code,AS400SD,End_Date,Unit_Size,Flag,Return,Track_Code,Res_Line,Unit_Num from Log ", CurrentProject.Connection, adOpenStatic, adLockOptimisticDo Until rst.EOF = True[/COLOR][/SIZE][/FONT]

 

[welshgasman]

I would just have an empty field, else you need to update that with 'To Be Done' before you can even start. If anything is in that field, then ignore it on your SQL.

To update just use something like

rst.EDit
rst!STatus = "Done"
rst.Update

as the very last process before moving to the next record.?

Personally I'd just use a Boolean, unless you can have more than two options for Status.

HTH

 

[MCTampa]

Wslshgasman - I did exactly as you said. I added the following at the end of my macro

rst.Fields("Status").value = "Done"
rst.Update
rst.MoveNext

At the beginning I added

If rst.Fields("Status").Value = "Done" Then rst.MoveNext
If rst.EOF = True Then ExtFunction

However, no matter what I do, even if it is marked "Done", the code runs the SECOND record.
If I have three records, all marked "Done", it will loop through the second and exit.

What am I doing wrong?

 

[MCTampa]

Actually, upon further testing, my last statement is not accurate.
If the first record is blank and the 2nd and 3rd are marked "Done", the code will execute the first record (blank) and the third record (Done).
If the first three records are marked "Done", the code will skip the first record, execute the second (Done) and fourth (blank)
If the first four records are marked "Done", the code will skip the first record, execute the second and fourth (Done) and the fifth record (blank).

So, it seems to be executing every other line marked Done.

 

[welshgasman]

I would not even be selecting records with "Done". Filter them out in the recordset sql.
Then you know you just process all the records.

From what you describe you have an error in you logic moving records.
Without seeing all the code together it is nigh on impossible to say where you have gone wrong, so that logic might still cause problems even if you ignore Done in the recordset sql.?

 

[MCTampa]

That appears to work.

 If IsNull(.Fields("Status")) Then

 

[welshgasman]

You are still to me at least, committing a cardinal error.?
It might not make much difference with small datasets, but why bring in records you know you want to ignore? That was the whole point of this thread, was it not?

I always try and avoid any what to me is unecessary processing. This is just a simple example, but what if there was a lot of code required to avoid such records.?

However, what works for you, is the main thing.