Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filtered records on Reports (2K)

    I would like to know how to make my report (that utilizes all of the records from my table) just include the information from my main form once it has been filtered. I want the report just to contain the information from the records I have filtered for. I believe this is a simple process, but since I am self taught I have not discovered this solution yet.

    Thank you in advance for your help

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

    Re: Filtered records on Reports (2K)

    Open the report from the form using code like:

    DoCmd.OpenReport ReportName:="rptMyReport", View:=acViewPreview, WhereCondition:=Me.Filter

    This makes the report open with the same filter as the form currently uses.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtered records on Reports (2K)

    Thanks for the help

  4. #4
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtered records on Reports (2K)

    I finally found the time to try the solution suggested, but ran into a problem. When I run the report which is utilizing the active filter, I get a message box that wants me to provide a value for Lookup_CRID.CRName. On the form, I am using a Lookup for the value CRName, but don't understand why if the filtered information won't flow to the report.

    The report opens fine with all the data when it is not opened from the control on the form where the filter is applied.

    This apparently only occurs when I filter by form which includes a specific CRName

    Any help would be appreciated.

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

    Re: Filtered records on Reports (2K)

    This is going to be a long, and perhaps not very helpful answer.

    On your form, you apparently have a combo box.
    The combo box is bound to the field CRID of the record source of the form.
    The combo box displays the field CRName that is not in the record source of the form.
    If you filter on the contents of the combo box, Access creates a filter (essentially the same as a query) in which the record source of the form is joined with the row source of the combo box; this row source is given an alias Lookup_CRID. If you select Records -> Filter -> Advanced filter/sort..., you will see what I mean. I have attached a picture of a similar situation in one of my databases.

    The reason that the report asks for a parameter is that CRName is not included in the record source of the report.

    I ran into this situation some time ago and came up with a kludgy workaround. I'll describe it below. It's not too bad if you have one or two combo boxes on your form. But if you have lots of them, it's unpleasant. Hopefully one of the Access gurus on this forum will come up with a better solution - I'll be interested too! After the workaround, I'll try to explain the ideas behind it.

    <img src=/w3timages/blueline.gif width=33% height=2>

    Workaround:
    1. <LI>Create a query based on the record source of the form, or duplicate the record source.
      <LI>Add the row source of each bound combo box to the query. (They must be tables or saved queries for this to work.)
      <LI>Assign the appropriate alias Lookup_... to each of these.
      <LI>Create outer joins between the form record source and each of the additions, as in the filter picture.
      <LI>Save the query.
      <LI>Set the record source of the report to the new query.
      <LI>Create the following function in a standard module:

      Function Nip(fromString, whichString)
      Dim intPos As Integer, strTemp As String
      strTemp = fromString
      intPos = InStr(strTemp, whichString)
      While intPos
      strTemp = Left$(strTemp, intPos - 1) & Mid$(strTemp, intPos + Len(whichString))
      intPos = InStr(strTemp, whichString)
      Wend
      Nip = strTemp
      End Function

      <LI>Modify the instruction for opening the report as follows:

      DoCmd.OpenReport ReportName:="rptMyReport", View:=acViewPreview, _
      WhereCondition:=Nip(Me.Filter, "FormRecordSource")

      where FormRecordSource is the name of the record source of the form.
    I warned you it was kludgy!

    <img src=/w3timages/blueline.gif width=33% height=2>

    Explanation:

    My first idea was to include the "looked up" field in the record source of the form. But, in the first place this defeats the idea of having a lookup table, and in the second place, this made the record source in the example I used not-updatable. That was unacceptable.

    The second attempt was to create a query that is a duplicate of the record source of the form. I added the lookup table and set its alias to Lookup_... (where ... is the name of the lookup field). I saved this query and used it as the record source of the report.

    This didn't work either, since Access prepends the name of the record source of the form to the field names in the filter. But now, the form and report had differently named record sources. So Access asked for parameters again.

    So then, I used a function to remove the name of the form record source from the filter before passing it to the report. Finally, it worked!

    I can't guarantee that this will work in all situations. It worked for me in a relatively simple form with one combo box.
    Attached Images Attached Images

Posting Permissions

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