Creating report from subform in Access 2003

Posted on 2009-04-03
Medium Priority
Last Modified: 2012-08-13
I have search form(actually a filter)  that uses 4 comboboxes to filter.  The result of the filter appears in a subform(datasheet view)  that is also located on the same form.  I need to attach a button to the form that will allow the user to run a report based on the filtered results appearing in the subform.  Thanks in advance.
Question by:LadyHagood
  • 2
LVL 11

Expert Comment

by:Craig Yellick
ID: 24064983
Are you using an actual form filter or are you dynamically constructing a SELECT statement's WHERE clause based on the four combo boxes?

If you are using a form filter you can accomplish this very easily via:

   DoCmd.OpenReport "TheReport", wherecondition:=Me.Filter

If you are constructing the statement dynamically you can use the same sort of technique but will have to be able to isolate the WHERE clause (without the WHERE).

Author Comment

ID: 24092062
I am dynamically filtering.  Below please see my code.  I am new to access.  So as much detail as you could give would be great. Thank you so much.

Private Function Filesearch()
   On Error GoTo Error_FileSearch
   Dim FilterClause As String, D As Long
   D = Me.DirectionGrp.Value
   If Nz(Me.Company.Column(0), 0) > 0 Then
      If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
      FilterClause = FilterClause & "[Company]='" & Me.Company.Value & "'"
   End If
   If Nz(Me.ProductName.Column(0), 0) > 0 Then
      If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
      FilterClause = FilterClause & "[ProductName]='" & Me.ProductName.Value & "'"
   End If
   If Nz(Me.TypeofDocument.Column(0), 0) > 0 Then
      If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
      FilterClause = FilterClause & "[TypeofDocument]='" & Me.TypeofDocument.Value & "'"
   End If
   If Nz(Me.Category.Column(0), 0) > 0 Then
      If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
      FilterClause = FilterClause & "[Therapeutic_category]='" & Me.Category.Value & "'"
   End If
   'Fill this Form wide variable so that it can be used for
   'the Report.
   CurrentFilter = FilterClause: FilterClause = ""
   'Place our created Filter Criteria into the Filter property of SubForm.
   Forms("FORM5")("Mainsubform9").Form.Filter = CurrentFilter
   'Turn on the Filter
   Forms("FORM5")("Mainsubform9").Form.FilterOn = True
   Exit Function
   MsgBox "StockSearch Function Error" & vbCr & vbCr & _
          Err.Number & " - " & Err.Description, vbExclamation, _
          "Stock Search Error"
   Resume Exit_FileSearch
End Function
LVL 11

Accepted Solution

Craig Yellick earned 1000 total points
ID: 24092091
You've done all the hard work already!  Assuming the the CurrentFilter variable is defined at the class level and is available to all code in  the form, your report can be opened using the same filter condition:

 DoCmd.OpenReport "TheReport", wherecondition:=CurrentFilter

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Implementing simple internal controls in the Microsoft Access application.
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question