Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Location
    Belfast, UK
    Posts
    87

    Unanswered: loop through field on table to create regional reports?

    Does anyone know how to loop through the a field when creating reports to give individual region reports.

    the code I'm currently using is:

    If reporttype = "RptClassSummaryRegionArea" Then

    strselect = "tblreview.IntReviewCaseID, tblClassification.StrClassification, tblRegion.StrRegion, tblArea.StrArea, tblreview.DateInput, tblcase.DistributionChannel"
    strfrom = " (tblRegion INNER JOIN (tblArea INNER JOIN tblsortcode_branch ON tblArea.IntArea = tblsortcode_branch.IntArea) ON tblRegion.IntRegion = tblsortcode_branch.IntRegion) INNER JOIN (tblClassification INNER JOIN (tblcase INNER JOIN tblreview ON tblcase.IntCase = tblreview.IntReviewCaseID) ON tblClassification.IntClassification = tblreview.IntClassification) ON tblsortcode_branch.BranchID = tblcase.BranchID"

    strsql = "select " & strselect & " from " & strfrom & " where " & strwhere & ";"

    qd.SQL = strsql

    stDocName = reporttype
    DoCmd.OpenReport stDocName, acViewDesign
    Reports!RptClassSummaryRegion.lblinput.Caption = strinput
    Reports!RptClassSummaryRegion.lblRegion.Caption = StrRegion
    Reports!RptClassSummaryRegion.LblDistChannel.Capti on = strdistribution
    DoCmd.OpenReport stDocName, acViewPreview
    End If

    This is ran from a form and there is a large section of code that creates the strwhere section of code.
    What I want is to be able to loop it for each region.
    tblregion contains,
    intregion = region number
    strregion = region name
    there are currently nine regions at the moment.

    Thanks in advance to all those offering the help.

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    I would suggest use a recordset and a parsed parameterized query.

    Create the full query and put [CurrIntRegion] in the where criterion of the IntRegion. Under Query pull down menu, choose Parameter and put CurrIntRegion as an integer (or whatever datatype it is).

    Create the recordset based on the query (Qry1)

    dim rs as recordset, qdf as querydef, db as database
    set db = currentdb()
    set qdf = db.querydefs(qry1)
    set rs = db.openrecordset("select distinct IntRegion from tblregion;")
    while not rs.eof

    set qdf![CurrIntRegion] = rs!intRegion

    ... do what you need to with the query like fill a table or run a report ...

    wend
    rs.close
    ...

Posting Permissions

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