Nested Do Until rst.EOF = True VBA Help

[MCTampa]

Good afternoon,

I have a PCOMM macro inputting data into an IBM AS400.
In my database, I have two tables, one with my Offices (codes) and one with my Data to input.

When I simply run the data and manually specify my Office Code, it's not a problem.
However since I have 20 offices, here is what I'd like to do in layman's terms, then I'll post my code.

The macro first inputs the office code into AS400, then moves on to the data.
So I'd like it to input the first office code, then move on to the data and run through that series.
When the data is done, I'd like it to move on to the next code.

Using Do Until rst.EOF = True, is fine for my data, but I need that nested inside the office selection.

Here is what I'm working with:

Function CopyToOffices2()

cleanWindows

Dim MySession As New AutSess
Dim SessionName As String
Dim FirstEntry, LastEntry As Integer
SessionName = "A"

MySession.SetConnectionByName (SessionName)
Set OIA = MySession.autECLOIA
Set PS = MySession.autECLPS

OIA.WaitForAppAvailable
OIA.WaitForInputReady

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

Dim cmd1 As ADODB.Command
Set cmd1 = New ADODB.Command
cmd1.ActiveConnection = CurrentProject.Connection

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

With rst
rst.Open "select Office from Offices ", CurrentProject.Connection, adoopenstatic, adLockOptimistic
Do Until rst.EOF = True

  On Error Resume Next
  Call sendToNa("PUT", "332", 21, 18, 0, 0) 'Go to Option 400
  Call sendToNa("COM", "enter", 0, 0, 0, 0)
  Call sendToNa("PUT", "7", 21, 39, 0, 0)
  Call sendToNa("COM", "enter", 0, 0, 0, 0)
  Call sendToNa("PUT", .Fields("Office"), 9, 45, 0, 0)
  Call sendToNa("COM", "enter", 0, 0, 0, 0)
  Call sendToNa("COM", "PF6", 0, 0, 0, 0)
  
With rst

rst.Open "select Resort, SDATE, EDATE, SSDATE, SEDATE from Template ", CurrentProject.Connection, adOpenStatic, adLockOptimistic
Do Until rst.EOF = True

  Call sendToNa("PUT", .Fields("Resort"), 8, 4, 0, 0)
  Call sendToNa("PUT", .Fields("SDATE"), 8, 14, 0, 0)
  Call sendToNa("PUT", .Fields("EDATE"), 8, 22, 0, 0)
  Call sendToNa("PUT", .Fields("SSDATE"), 8, 36, 0, 0)
  Call sendToNa("PUT", .Fields("SEDATE"), 8, 44, 0, 0)
  rst.MoveNext
  Call sendToNa("PUT", .Fields("Resort"), 9, 4, 0, 0)
  Call sendToNa("PUT", .Fields("SDATE"), 9, 14, 0, 0)
  Call sendToNa("PUT", .Fields("EDATE"), 9, 22, 0, 0)
  Call sendToNa("PUT", .Fields("SSDATE"), 9, 36, 0, 0)
  Call sendToNa("PUT", .Fields("SEDATE"), 9, 44, 0, 0)
  Call sendToNa("COM", "enter", 0, 0, 0, 0)
  Call sendToNa("COM", "PF6", 0, 0, 0, 0)
  rst.MoveNext
  
Loop
rst.MoveNext

Loop
End With
RemoveBlanks

End Function

I'm getting an error and I'm pretty sure I'm not nesting the two Do Until rst.EOF = True correctly.

Thanks,
Mike

 

[MCTampa]

I tried changing the second rst reference to rst2 as that did not help.

 

[welshgasman]

I tried changing the second rst reference to rst2 as that did not help.

You are not even using rst2?

I would have thought the logic would have been along the lines of

Get Office office code (rstOffice) Start using names that mean something. So much easier to debug.
Get Office data (rstData) This I would have thought was all data for the particular office code in rstOffice field.? so use that as criteria for the second recordset.
Process rstData until EOF
Get next office code

Repeat until EOF rstOffice