[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 611
  • Last Modified:

Creating report from subform in Access 2003

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.
0
LadyHagood
Asked:
LadyHagood
  • 2
1 Solution
 
Craig YellickDatabase ArchitectCommented:
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).
0
 
LadyHagoodAuthor Commented:
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_FileSearch:
   Exit Function
   
Error_FileSearch:
   MsgBox "StockSearch Function Error" & vbCr & vbCr & _
          Err.Number & " - " & Err.Description, vbExclamation, _
          "Stock Search Error"
   Resume Exit_FileSearch
End Function
0
 
Craig YellickDatabase ArchitectCommented:
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
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now