Nested Do Until rst.EOF = True VBA Help

MCTampa

Board Regular
Joined
Apr 14, 2016
Messages
65
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:
VBA Code:
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
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

MCTampa

Board Regular
Joined
Apr 14, 2016
Messages
65
I tried changing the second rst reference to rst2 as that did not help.
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
820
I tried changing the second rst reference to rst2 as that did not help.
You are not even using rst2? :unsure:

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
 

Watch MrExcel Video

Forum statistics

Threads
1,101,935
Messages
5,483,781
Members
407,410
Latest member
catherinejoy

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top