[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Creating report from subform in Access 2003

Posted on 2009-04-03
3
Medium Priority
?
606 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: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).
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:
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
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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…

656 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