?
Solved

Creating report from subform in Access 2003

Posted on 2009-04-03
3
Medium Priority
?
592 Views
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.
0
Comment
Question by:LadyHagood
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 11

Expert Comment

by:CraigYellick
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).
0
 

Author Comment

by:LadyHagood
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_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
 
LVL 11

Accepted Solution

by:
CraigYellick 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
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

801 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