Nested Do Until rst.EOF = True VBA Help

MCTampa

Board Regular
Joined
Apr 14, 2016
Messages
66
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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

MCTampa

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

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
838
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,099
Messages
5,509,232
Members
408,716
Latest member
GreedySheedy

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top