Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    May 2010
    Location
    Glasgow, Scotland
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hey guy and gals,
    Looking for a bit of help with Access, I'm running multiple reports and have all of those set up fine but I want to add the option to view either the full report (the only option just now) or to view a simple result form with just the amount of records returned for that report and was wondering how to go about this? I have a count at the bottom of the reports I have currently but can't work out how to link this to a text box in a simple summary form. I'm just learning Access/VBA just now and though I can pretty much follow it dummy instructions would be appreciated.


    Cheers

  2. #2
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Midwest, USA
    Posts
    108
    Thanks
    5
    Thanked 5 Times in 4 Posts
    Gary,

    How many reports are you talking about? I assume each report is based on a query. The only way I know of to get a count of records returned by a query is to run the query.


    Create a maketable query that uses the queries for every report, but would only return the record count for each report. Use the maketable query to make a summary table with the name of each report as the field name with the only data being the count of records from the query for that report. Once the table is made change the maketable query to an append query.

    Create your summary form based on your count table. Create a macro that drops the data from your summary count table (runs a drop data query), then runs the append query to repopulate your count table. Assign this query to the on open property of your form, so that it will run automatically when you open your form.

    The fields displayed on your form would be named for each individual report, but should contain the number of records returned for each report, based on your summary table. You could then add command buttons next to each report name that would actually open the report.


    If you have a lot of reports, this could take a long time to run.

  3. #3
    New Lounger
    Join Date
    May 2002
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If you're only running one report at a time, you can provide an option to show or hide the detail section of a report. Opting for the summary option, you would still display the report, but only the heading and footer (with the record count) would be displayed.

    I always have a separate form for running each report, where parameters can be entered. It could also have a combo box for selecting Detail or Summary e.g. named cboDetailOrSummary. When the report is run, it should check the value of the flag on the form and hide or show the report detail section as appropriate e.g.

    Private Sub Report_Open(Cancel As Integer)

    DoCmd.Maximize

    DoCmd.ShowToolbar "Printing"

    If Forms!frmGLHByProgArea!cboSummaryOrDetail = "S" Then
    Me.Detail.Visible = False
    Else
    Me.Detail.Visible = True
    End If

    End Sub

  4. #4
    Lounger
    Join Date
    May 2010
    Location
    Glasgow, Scotland
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm running around 6 or 7 diffrent reports though I'll probably add to that. Just now all the reports are run from the same query, I have a combo box to select what report type someone wants and based what report type the choose combinations of other combo baxes will be active or not(i.e. for every report type start date and end date will always be used but other options like regime etc will not be enabled and set to null, hope this makes sense) then the query runs using any criteria that is not null to populate the reports. I was hoping to run this query with the option of a full report or to open another form that will perhaps show the 2 dates used, any other criteria used and then just give a count of the records returned.
    I'll try out what you've said and see if I can get that to work.

    Cheers

Posting Permissions

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