Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Output functionality in Access Modules (Access 2000 Win 2000 SR-1)

    I do a lot of work with asp's Response.Write function for opening a db recordset and writing out what I want for a particular query. Is there a way to do this natively in an Access report? I want to create a report that creates recordsets 'on the fly' and writes out info from that recordset as needed. For example, I have a query with information of local groups by Counties listed; in the report I want to be able to read thru and write out the mailing info on some one in a group if they are a 1st: a Coordinator, 2nd: a Chair, 3rd: Other. Some groups won't have coords, others will, and it would be easy to do this if I ran a query on each of these critera as needed while building the report. This is rather trivial in asp as you can open a recordset, output the results, close the recordset, open another one, output the results, close, etc as you go down the report (or build it).

    Can this be done in Access? I hope this makes sense...

    Basically I want to build a report from top to bottom and open and close recordsets and I go down the report. To put the question even more simply: how to create a report that contains output for more than one query... I suppose I could do subreports, but I wanted to know if there's a way to use the kind of Response.Write functionality from asp in Access.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Output functionality in Access Modules (Access 2000 Win 2000 SR-1)

    I don't think Access reports were designed for this.
    You might be able to do something by predefining lots of hidden labels, and using code in the On Open event of the report to make them visible and setting their captions to values from recordset(s) as needed. But it wouldn't be very flexible.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Output functionality in Access Modules (Access 2000 Win 2000 SR-1)

    I thought as much. too bad really as I'd LOVE to be able to do a 'Respone.Write' kind of thing natively in Access - some kind of way to actually use Debug.Print to write a report would be just about enough, IMHO. with this current project I looked at it and thought 'yeah, this would be easy if we made it into an .asp webpage.' which ain't what they want to do. So now I am working on an Access form and ignoring the whole Access Reports area as a place where bad things happen. I have never like Access reports and don't think I ever will. One of these days I'll have to dive into the whole area of creating and manipulating recordsets in VB code, which should be somewhat entertaining.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Output functionality in Access Modules (Access 2000 Win 2000 SR-1)

    Hi Steve,
    This sounds like a UNION query, where you select records based on multiple criteria - then you could base the report on that. Am I making it too simple?
    Wendell

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Output functionality in Access Modules (Access 2000 Win 2000 SR-1)

    probably you aren't but then again i've never investigated the arcana of SQL beyond the occasional make-table in Access. But there may be some complications in this project.

    I have a main group by, CATEGORIES, which is actually a list of one or more counties. A second grouping would CRCGType, with 3 possible values. Within this level there are 4 types of CRCGMembers; Coordinators (of which there are not many); Chairs (of which there should be one and only one for each CATERGORIES - CRCGType; Co-Chairs (of which there can be none, one or more); Other (of which there can be none, one or more). What I am trying to do is display a form or a report that shows all the pertinent information for a given CATEGORES-CRCGType specification. You can have a minimum of 1 row for a CATEGORY-CRCGType (that is, when there's just a chair) and potential many more rows depending on whether other records in the table (a coordinator, 2 or more co-chairs and 'others).

    Ideally, I want a report that steps thru the data and gives me the MemberType details all at once for each CATEGORES-CRCGType or, even simpler, just for each CATEGORIES with a secondary grouping on CRCGType. It would be really cool to be able to write "NO CHAIR!" when that occurs and "No Coordintor" when that occurs in the appropriate location in the report.

    Currently I have been playing around with a main form and 4 sub-forms with subqueries that filter the main form table based on the MemberType and set up the Child-Master links to CATEGORIES; CRCGType. What I get works sorta-kinda but there seem to be a lot of repetitious rows in the form datasheet. It seems that Access wants you to build a 'super SQL' statement with these sub-form/reports hanging off the main SQL statement, if that makes sense. In asp I could just set up a query as needed, output the results and go on. I know i'm comparing different technologies but the thing that interests me is being able, somehow, to do more than one query for a form or report, or even somehow just do the whole thing in a module and output it to a Word file, perhaps.

    If this sounds like a job for UNION then, cool! I can learn. any suggestions? when I try to construct a UNION query I get the SQL builder window...

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Output functionality in Access Modules (Access 2000 Win 2000 SR-1)

    The description you give here sounds like the standard grouping options available in a report. An Access report can be grouped on several levels, with group header and group footer sections to display summary information. you can hide repeating information by setting the "Hide Duplicates" property of a control to Yes.

    To get a feeling for this, take a look at the design of the reports in the Northwind sample database that comes with Access. You can also get ACC2000: Microsoft Access 2000 Sample Reports Available in Download Center - lots of examples of maniupulating reports in VBA.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Output functionality in Access Modules (Access 2000 Win 2000 SR-1)

    thanks - i'm actually working more on the form side of this project but you're right, the usual grouping stuff on a report will probably work. didn't think of that...

Posting Permissions

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