Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I produce a report which prints member names with the number of lines generated & sequentially numbered based on the number of records. Also built into the detail onprint is the generation of vertical lines so that the report looks like a table.

    What I need to be able to do is to create an extra 5 blank lines at the end but have them sequentially numbered and line drawn as well.

    So, the question is, how do I make the query think that there's an extra five blank records at the end?

    Thanks in advance for any help with this,
    Cheers,
    Andy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You may be able to use a union query to add 5 blank records.
    Or use code similar to that in ACC2000: How to Print a Constant Number of Lines Per Group.

    If you want detailed help, please attach a stripped down and zipped copy of the database.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='785889' date='23-Jul-2009 05:35']You may be able to use a union query to add 5 blank records.
    Or use code similar to that in ACC2000: How to Print a Constant Number of Lines Per Group.

    If you want detailed help, please attach a stripped down and zipped copy of the database.[/quote]
    Hans,

    Thanks for your input & suggestions.

    Suggestion #1 re Union queries: Didn't try it because I really do not know how to use Unions.

    Suggestion # 2 re MS Knowledgebase: I believe I followed all of the suggestions and came up with rpt Z

    My original report is rpt A where I realized I needed to be able to add lines at the end. I specified 5 in my suggestion, but I assume it could be anything I wanted based on module coding.

    Thanks again in advance for any help with this scaled back version of the database,
    Attached Files Attached Files
    Cheers,
    Andy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You had copied the code, but not followed the rest of the instructions.

    In the attached version, I have modified Rpt A to display 5 extra lines. The number can be changed by modifying the declaration

    Const ExtraLines = 5

    in the code module.

    I have added a text box TotGrp to the group header with control source =Count(*). This counts the number of records in the group. The Visible property of this text box has been set to No, since it's only needed in the code.

    I have changed the text box Text479 to a label whose caption is set in the code. This was necessary to continue increasing the number past the last record.
    Attached Files Attached Files

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='785937' date='23-Jul-2009 11:03']You had copied the code, but not followed the rest of the instructions.

    In the attached version, I have modified Rpt A to display 5 extra lines. The number can be changed by modifying the declaration

    Const ExtraLines = 5

    in the code module.

    I have added a text box TotGrp to the group header with control source =Count(*). This counts the number of records in the group. The Visible property of this text box has been set to No, since it's only needed in the code.

    I have changed the text box Text479 to a label whose caption is set in the code. This was necessary to continue increasing the number past the last record.[/quote]
    Hans,

    Thank you as always for the quick response. Based on how I tackled the example of copying the code, but not reading the rest of the instructions, it's just as well I'm not an airline pilot. Oops, I forgot to lower the landing wheels.

    I appreciate your ongoing education,
    Cheers,
    Andy

  6. #6
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='785937' date='23-Jul-2009 11:03']You had copied the code, but not followed the rest of the instructions.

    In the attached version, I have modified Rpt A to display 5 extra lines. The number can be changed by modifying the declaration

    Const ExtraLines = 5

    in the code module.

    I have added a text box TotGrp to the group header with control source =Count(*). This counts the number of records in the group. The Visible property of this text box has been set to No, since it's only needed in the code.

    I have changed the text box Text479 to a label whose caption is set in the code. This was necessary to continue increasing the number past the last record.[/quote]

    Hi Hans,

    Sorry to resurrect this recently flogged horse. The situation is that the code that you provided me worked perfectly:


    Function PrintLines(R As Report, TotGrp)
    Dim i As Integer
    TotCount = TotCount + 1
    R!Text479.Caption = TotCount & "." ' This is where the line numbering is set in the report
    If TotCount = TotGrp Then
    R.NextRecord = False
    ElseIf TotCount > TotGrp And TotCount < TotGrp + ExtraLines Then
    R.NextRecord = False
    R![Text483].Visible = False
    For i = 636 To 651
    R.Controls("Text" & i).Visible = True
    Next i
    End If
    End Function


    EXCEPT in the situation where there is no data available and I get a:

    Run-Time Error '2427'

    You entered an expression with no value

    and it highlights the:

    If TotCount = TotGrp Then

    line.

    I'd appreciate any suggestions,
    Cheers,
    Andy

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'd cancel the report if there are no data using the On No Data event of the report:

    Code:
    Private Sub Report_NoData(Cancel As Integer)
      MsBox "There are no data to display", vbInformation
      Cancel = True
    End Sub
    This, in turn, will cause error 2501 if you open the report from a form using DoCmd.OpenReport. To suppress this, use an error handler:

    Code:
    Private Sub cmdReport_Click()
      On Error GoTo ErrHandler
      DoCmd.OpenReport "rpt A", acViewPreview
      Exit Sub
    
    ErrHandler:
      If Err = 2501 Then
    	' Report was canceled - ignore
      Else
    	MsgBox Err.Description, vbExclamation
      End If
    End Sub

  8. #8
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='790693' date='26-Aug-2009 01:59']I'd cancel the report if there are no data using the On No Data event of the report:

    Code:
    Private Sub Report_NoData(Cancel As Integer)
      MsBox "There are no data to display", vbInformation
      Cancel = True
    End Sub
    This, in turn, will cause error 2501 if you open the report from a form using DoCmd.OpenReport. To suppress this, use an error handler:

    Code:
    Private Sub cmdReport_Click()
      On Error GoTo ErrHandler
      DoCmd.OpenReport "rpt A", acViewPreview
      Exit Sub
    
    ErrHandler:
      If Err = 2501 Then
    	' Report was canceled - ignore
      Else
    	MsgBox Err.Description, vbExclamation
      End If
    End Sub
    [/quote]

    Hans,

    Thanks as always. Worked perfectly the second time after I changed MsBox to MsgBox. I was looking in the wrong forest for the trees. I incorrectly assumed that the code would have gone into the modLineCounter module. Which was dumb considering I usually do the NoData thing in most of my reports.

    Thanks again,
    Cheers,
    Andy

Posting Permissions

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