Nested Loop Not working

[upernikaw]

Hello,
I am attempting to create a nested loop (in Access 2003/VB) that will print a report for a set of user defined months inputed on a form and that will print out for every Client. So the first loop would increment the Client by 1, then the second loop would increment the Month by 1. So the first time around Client A for Month 1, then Client A for month 2 then Client B for Month 1 and so on. Below is what I have already, I can get the nested loop to work and increment the months, but am having a heck of a time incrementing the outer loop (Client). Below is my code so far. Thanks in advance for your help.

Private Sub Print_All_SB165_Click()
 
    Dim stDocName As String
    stDocName = "CFDMonthlyReportALLMONTH"
 
    Dim i As Integer
    i = 1
    Dim x As Integer
    x = 0
 
    Do
 
    Do While Forms!ReportChooser!AllMonth.ItemData(x) <> ""
        Forms!ReportChooser!Month = Forms!ReportChooser!AllMonth.ItemData(x)
        Forms!ReportChooser!Client_Box = Forms!ReportChooser!Client_All.ItemData(i)
 
        DoCmd.RunMacro "OpenQNOTE"
 
        DoCmd.OpenReport stDocName, acNormal
 
        DoCmd.RunMacro "CloseQNOTE"
 
        x = x + 1
    Loop
        i = i + 1
    Loop Until Forms!ReportChooser!Client_Box.ItemData(i) = ""
 
End Sub
 

[Killer42]

I wasn't going to reply since this isn't really my area. But since nobody else has put their oar in yet...

One thing which jumped out at me is that this line...

Forms!ReportChooser!Client_Box = Forms!ReportChooser!Client_All.ItemData(i)

appears to be moving a single element (i) to Client_Box, then this line...

Loop Until Forms!ReportChooser!Client_Box.ItemData(i) = ""

is testing element (i) from Client_Box. Something just doesn't seem to add up here.

I'll take this pure guess-work one step further - perhaps at the end of the outer loop, you should be testing Client_All.ItemData(i) instead of Client_Box.ItemData(i) ?

[upernikaw]

Thank you for your response. I completely understand what you are saying...the reason I set it up that way was because I could not figure out how to initialize the increment...I had to assign (i) to Client somehow to increment the data...I'm assuming there has to be a better way but I don't know what that is.

I wasn't going to reply since this isn't really my area. But since nobody else has put their oar in yet...

One thing which jumped out at me is that this line...

Forms!ReportChooser!Client_Box = Forms!ReportChooser!Client_All.ItemData(i)

appears to be moving a single element (i) to Client_Box, then this line...

Loop Until Forms!ReportChooser!Client_Box.ItemData(i) = ""

is testing element (i) from Client_Box. Something just doesn't seem to add up here.

I'll take this pure guess-work one step further - perhaps at the end of the outer loop, you should be testing Client_All.ItemData(i) instead of Client_Box.ItemData(i) ?

[Killer42]

Thank you for your response. I completely understand what you are saying...the reason I set it up that way was because I could not figure out how to initialize the increment...I had to assign (i) to Client somehow to increment the data...I'm assuming there has to be a better way but I don't know what that is.

Sorry I can't be of more help, but did you try the suggestion of testing Client_All.ItemData(i) instead of Client_Box.ItemData(i) in the Loop Until? In other words...

Loop Until Forms!ReportChooser!Client_All.ItemData(i) = ""

[upernikaw]

Thank you for trying...I appreciate it...I did try your suggestion but still could not get it to work...I think I'm going to take a day or two without looking at it and then go back to it to get some new perspective. Thanks again for your help.

Sorry I can't be of more help, but did you try the suggestion of testing Client_All.ItemData(i) instead of Client_Box.ItemData(i) in the Loop Until? In other words...

Loop Until Forms!ReportChooser!Client_All.ItemData(i) = ""

[Killer42]

Thank you for trying...I appreciate it...I did try your suggestion but still could not get it to work...I think I'm going to take a day or two without looking at it and then go back to it to get some new perspective. Thanks again for your help.

Let us know how it turns out.

[albertw]

Let us know how it turns out.

hi

'do while' is no proper statement

better take 2nd loop as while...wend

[Killer42]

hi
'do while' is no proper statement
better take 2nd loop as while...wend

When you say it's "no proper statement" is this a style thing, or what? It is certainly a valid statement, at least in VB6. Dunno about Access.

[albertw]

When you say it's "no proper statement" is this a style thing, or what? It is certainly a valid statement, at least in VB6. Dunno about Access.

hi

do while ... wants to have a value before calculation
in this case the value from the previous loop is used.
if you use 'while' or 'do' only, the new value is taken.

[Killer42]

do while ... wants to have a value before calculation
in this case the value from the previous loop is used. if you use 'while' or 'do' only, the new value is taken.

Interesting. I wasn't aware of the difference.

[upernikaw]

Thanks again for the help...I'm still having the same issue though...following is the new code (which I've tried about 10 different variations of):

Private Sub Print_All_SB165_Click()
 
 
    Dim stDocName As String
    stDocName = "CFDMonthlyReportALLMONTH"
 
    Dim i As Integer
    i = 1
    Dim x As Integer
    x = 0
 
        While Forms!reportchooser!AllMonth.ItemData(x) <> ""
        Forms!reportchooser!Month = Forms!reportchooser!AllMonth.ItemData(x)
 
 
        DoCmd.RunMacro "OpenQNOTE"
 
        DoCmd.OpenReport stDocName, acNormal
 
        DoCmd.RunMacro "CloseQNOTE"
 
        x = x + 1
    Wend
    Forms!reportchooser!Client_Box = Forms!reportchooser!Client_All.ItemData(i)
    i = i + 1
    Loop Until Forms!reportchooser!Client_All.ItemData(i) = ""
 
 
End Sub
 

hi

do while ... wants to have a value before calculation
in this case the value from the previous loop is used.
if you use 'while' or 'do' only, the new value is taken.

[upernikaw]

Just in case anyone cares...I figured it out...it was actually quite a simple fix...I didn't reset the variable in the inner loop...the code ends up like this:

Dim stDocName As String
    stDocName = "CFDMonthlyReportALLMONTH"
 
    Dim i As Integer
    i = 0
    x = 1
    Do While Forms!reportchooser!Client_All.ItemData(x) <> ""
        Forms!reportchooser!Client_Run = Forms!reportchooser!Client_All.ItemData(x)
    Do While Forms!reportchooser!AllMonth.ItemData(i) <> ""
        Forms!reportchooser!Month = Forms!reportchooser!AllMonth.ItemData(i)
 
        DoCmd.RunMacro "OpenQNOTE"
 
        DoCmd.OpenReport stDocName, acNormal
 
        DoCmd.RunMacro "CloseQNOTE"
 
        i = i + 1
    Loop
       x = x + 1
       i = 0
    Loop
 

Thanks again for the help...I'm still having the same issue though...following is the new code (which I've tried about 10 different variations of):

Private Sub Print_All_SB165_Click()
 
 
    Dim stDocName As String
    stDocName = "CFDMonthlyReportALLMONTH"
 
    Dim i As Integer
    i = 1
    Dim x As Integer
    x = 0
 
        While Forms!reportchooser!AllMonth.ItemData(x) <> ""
        Forms!reportchooser!Month = Forms!reportchooser!AllMonth.ItemData(x)
 
 
        DoCmd.RunMacro "OpenQNOTE"
 
        DoCmd.OpenReport stDocName, acNormal
 
        DoCmd.RunMacro "CloseQNOTE"
 
        x = x + 1
    Wend
    Forms!reportchooser!Client_Box = Forms!reportchooser!Client_All.ItemData(i)
    i = i + 1
    Loop Until Forms!reportchooser!Client_All.ItemData(i) = ""
 
 
End Sub
 

[Killer42]

Just in case anyone cares...I figured it out...it was actually quite a simple fix...I didn't reset the variable in the inner loop...the code ends up like this: ...

I care, and I expect others do. It's good to hear that you found a solution.

Speaking for myself, I just never have the time to chase up all the things I'd like to, so they tend to drop from memory. If you have an outstanding question, feel free to post reminders from time to time so it pops to the top of the list. Don't overdo it though, or you'll invoke the wrath of our dreaded moderators (shudder...) :)