Do While Not EOF problem

[ssthornton]

Sub cmdPrintAllReports_Click()
On Error GoTo Err_cmdPrintAllReports_Click
    Dim stDocName As String
    Dim intNumberRecords As Integer
    Dim stDailyReport As String
    Dim db As Database
    Dim rs As Recordset
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("tblCustomers")
    Select Case ogPrintReports
     Case Is = 1 'Statements
    
    ' Do While Not (rs.EOF)
    
        Me.txtCriteria = rs!CustomerID
        intNumberRecords = DCount("OrderID", "tblInvoices", _
        "[CustomerID] = Form!lstSelectClient And [Date] >= Form!BeginningDate And [Date] <= Form!EndingDate And [Paid] = 0")
        
             If intNumberRecords = 0 Then
                Beep
                MsgBox "There are no unpaid transactions for " & Me.txtCriteria, _
                vbExclamation, conAppName
             Else
                stDocName = "rptStatements"
                DoCmd.OpenReport stDocName, acNormal
             End If
             
     '   rs.MoveNext
     '  Loop
        
       rs.Close
       Exit Sub

I am trying to use this block of code to print statements for my clients. The code accepts a date range and uses the DCOUNT function to evaluate if the customer has any unpaid invoices. It works correctly with the looping disabled. I either get the message box or the correct report is generated. When I enable the loop, it appears intNumberRecords is evaluated as 0 for all customers - that is the message box appears for each client whether they have outstanding invoices or not
Any ideas appreciated

[June7]

Is 'Form' the name of the form?

I don't see how the DCount can be working. The references to the form controls are not concatenated as variables should be. As it is, the WHERE CONDITION will compile with the literal string 'Form!lstSelectClient', not the value of Form!lstSelectClient. If it is working that is a syntax I have never encountered before.

The code should have a compile error because the Select Case does not have an End Select or did you just cut off the procedure in the post? I am not understanding how the code will print multiple reports.

Do you want to provide project for analysis? Folllow instructions at bottom of my post.

[ssthornton]

Yes I have cut of the procedure so the End Select does not show. The form I am working off of has a calendar control I use to pass dates to the code (I have also omitted code where I test the dates for error conditions) a list box (lstSelectClient) and an unbound text box (txtCriteria ) Following is code i use to print a single report

Sub cmdPrintReport_Click()
On Error GoTo Err_cmdPrintReport_Click
    Dim stDocName As String
    Dim stDailyReport As String
    Dim blnOK As Boolean
    Dim intNumberRecords As Integer
       
    If IsNull(BeginningDate) Then
        Beep
        MsgBox "You Must Select a Beginning Date", _
                vbExclamation, conAppName
        DoCmd.CancelEvent
        SetDate.Caption = "Set Beginning Date"
        SelectDate.SetFocus
        Exit Sub
    End If
    
    If IsNull(EndingDate) Then
        Beep
        MsgBox "You Must Select an Ending Date", _
                vbExclamation, conAppName
        DoCmd.CancelEvent
        SetDate.Caption = "Set Ending Date"
        SelectDate.SetFocus
        Exit Sub
    End If
    
    If EndingDate < BeginningDate Then
        Beep
        MsgBox "The Ending Date Must Be Later Than Beginning Date", _
                vbExclamation, conAppName
        DoCmd.CancelEvent
        SetDate.Caption = "Set Ending Date"
        SelectDate.SetFocus
        Exit Sub
    End If
    
    Select Case ogPrintReports
    
    Case Is = 1
    If IsNull(lstSelectClient) Then
        Beep
        MsgBox "You Must Select a Client Name", _
                vbExclamation, conAppName
        DoCmd.CancelEvent
        lstSelectClient.SetFocus
        Exit Sub
    End If
    
    intNumberRecords = DCount("OrderID", "tblInvoices", _
        "[CustomerID] = Form!lstSelectClient And [Date] >= Form!BeginningDate And [Date] <= Form!EndingDate And [Paid] = 0")
        
    If intNumberRecords = 0 Then
        Beep
        MsgBox "There are no unpaid transactions for " & lstSelectClient, _
                vbExclamation, conAppName
        Exit Sub
    Else
         Me.txtCriteria = lstSelectClient
            stDocName = "rptStatements"
            DoCmd.OpenReport stDocName, acNormal
            DoCmd.Maximize
            Forms!frmPrintReports.Visible = False
            DoCmd.ShowToolbar "tlbrSemimonthly", acToolbarYes
        Exit Sub
    End If
    
    Case Is = 2
    
        If IsNull(lstSelectClient) Then
            Beep
            MsgBox "You Must Select a Client Name", _
                    vbExclamation, conAppName
            DoCmd.CancelEvent
            lstSelectClient.SetFocus
            Exit Sub
        End If
        
        stDailyReport = DLookup("[DailyReport]", "[tblCustomers]", _
                                "[CustomerId] = Form!lstSelectClient")
        intNumberRecords = DCount("OrderID", "tblInvoices", _
        "[CustomerID] = Form!lstSelectClient And [Date] >= Form!BeginningDate And [Date] <= Form!EndingDate")
            If intNumberRecords = 0 Then
                Beep
                blnOK = Confirm("There were no transactions for this Client. Print AnyWay?")
                If blnOK Then
                    stDocName = "rptSemiMonthly(NoIndTrans)"
                    DoCmd.OpenReport stDocName, acPreview
                    Exit Sub
                End If
            End If
            
        If stDailyReport = "No" Then
            Me.txtCriteria = lstSelectClient
            stDocName = "rptSemiMonthly"
            DoCmd.OpenReport stDocName, acNormal
            Exit Sub
        Else
            Beep
            blnOK = Confirm("This Client Doesn't usually get Semi Monthly Report. Print AnyWay?")
            
            If blnOK Then
                Me.txtCriteria = lstSelectClient
                stDocName = "rptSemiMonthly"
                DoCmd.OpenReport stDocName, acNormal
                Exit Sub
            End If
        End If
    
     Case Is = 3
        stDocName = "rptStmtSummary"
        DoCmd.OpenReport stDocName, acNormal
    
    Case Is = 4 ' Billing Summary
        stDocName = "rptBillingSummary"
        DoCmd.OpenReport stDocName, acNormal
        
   End Select
Exit_cmdPrintReport_Click:
    Exit Sub
Err_cmdPrintReport_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrintReport_Click
    
End Sub

I can use this code to print one of four reports. I am trying to adapt the code to be able to print statements for all the customers with one click. This project started as access 97 and has worked its way up This code is functional if not elegant as I am a novice programmer. I used the recordset method to do some looping elsewhere in the project and was succesful.
As stated earlier, without the looping, the code evaluates the number of outstanding invoices for the first client as we are at .BOF but when I enable the loop it does loop through each client but appears to return 0 for intNumberRecords.
Unfortunately all the data is quite confidential for my business/clients
Thanks for your quick response

[June7]

Don't need real data, as instructions state, make copy and remove confidential data (names, phones, addresses, emails), just leave something (dummy records okay) to test with.

Or you can just try this change to the DCount and see what happens:

intNumberRecords = DCount("OrderID", "tblInvoices", _
"[CustomerID] = " & Me.lstSelectClient & " And [Date] BETWEEN #" & Me.BeginningDate & "# AND #" & Me.EndingDate & "# And [Paid] = 0")

[ssthornton]

Thanks your suggestion led me to the solution. I used the following format to determine [CustomerID] in the DCOUNT: Forms!frmPrintReports.lstSelectClient and voila
Thanks again!