Candace Hagood
asked on
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.
ASKER
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.Colum n(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")("Mainsubfor m9").Form. Filter = CurrentFilter
'Turn on the Filter
Forms("FORM5")("Mainsubfor m9").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
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
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "[ProductName]='" & Me.ProductName.Value & "'"
End If
If Nz(Me.TypeofDocument.Colum
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]='"
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")("Mainsubfor
'Turn on the Filter
Forms("FORM5")("Mainsubfor
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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).