Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    No data in report (Access 2k)

    I have a VBA module, which does several things.

    One of its tasks is to cycle through several reports, printing them. I'm trying to find a way to not print them if there is no data.

    I know how to use the "OnNoData" Event on the report, but if I use that, it stops the module from running, cancelling all the other subsequent actions.

    Presumably, what I need is some form of "IF" statement in the vba module, but I can't think how to do it.

    Any suggestions


    Thanks

  2. #2
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Kenton, Delaware, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No data in report (Access 2k)

    One way I handle no data reports is using the nodata event to print a different report but one that has a text box on it indicating that there was no data for this report. I don't know if that would help but it will keep the printer going I think.

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No data in report (Access 2k)

    I'm not sure if that'll work. I was hoping for a solution rather than a work-round.

    I'll wait till I hear from anyone else before I try this.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: No data in report (Access 2k)

    What do you mean "it stops the module from running, cancelling all the other subsequent actions"? The reports NotData event doesn't stop code. Since you didn't print the code that tries to loop through the reports, there isn't any way to guess at what's wrong with it.
    Charlotte

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No data in report (Access 2k)

    Sorry, I didn't explain the problem fully. I used the EventCancel to halt printing with OnNoData. However this also halts the running of the VBA module that calls the report in the first place (see below).

    Hope this makes a bit more sense.






    Option Compare Database

    Function SendLetters()
    On Error GoTo SendLetters_Err

    Dim mydb As Database, myrec As Recordset
    Set mydb = CurrentDb

    '**********************************
    'open letter to new member
    '**********************************

    Dim strMessage As String
    DoCmd.OpenReport "rptLetNewMember", acNormal, "", ""

    strMessage = "Have all the Customer letters printed OK?"
    Response = MsgBox(strMessage, vbYesNo)
    If Response = vbNo Then
    MsgBox "Select pages to re-print"
    DoCmd.OpenReport "rptLetNewMember", acViewPreview
    Else
    End If


    '**********************************
    'open letter to bank to lodge any standing orders
    '**********************************

    DoCmd.OpenReport "rptLetLodgeSO", acNormal, "", ""
    strMessage = "Have all the Bank letters printed OK?"
    Response = MsgBox(strMessage, vbYesNo)
    If Response = vbNo Then
    MsgBox "Select pages to re-print"
    DoCmd.OpenReport "rptLetLodgeSO", acViewPreview
    Else
    End If

    '************************************************* ***************
    'Update Letters_Sent Indicator
    '************************************************* ***************

    Set myrec = mydb.OpenRecordset("Select tblPeople.Letters_Sent From tblpeople WHERE (((tblPeople.Letters_Sent)is null))")

    With myrec
    Do While Not .EOF
    .Edit
    !Letters_Sent = "Y"
    .Update
    .MoveNext
    Loop
    End With

    SendLetters_Exit:
    Exit Function

    SendLetters_Err:
    MsgBox Error$
    Resume SendLetters_Exit

    End Function

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: No data in report (Access 2k)

    EventCancel? Do you mean you set the Cancel argument of the NotInList event of the report to True to cancel printing? That has no effect on the code that called the the report except to raise an error that you have to trap and ignore. Since you haven't implemented any error handling in your routine, however, the error returned by the NotInList event cancelling the print job *will* abort your code because there is no handler to deal with the "error".

    You're probably getting a 2501 error, which should simply be trapped and followed by a resume next command to move to the next line after the openreport in your calling routine.
    Charlotte

  7. #7
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No data in report (Access 2k)

    That's exactly what I'm getting, (although I didn't have a clue that it was a 2501 error).

    On resume next has worked however.


    Thanks for the help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •