Solved

Creating report from subform in Access 2003

Posted on 2009-04-03
3
531 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
  • 2
3 Comments
 
LVL 11

Expert Comment

by:CraigYellick
Comment Utility
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
Comment Utility
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 250 total points
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

743 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now