Creating reports from user selected form data

[utahjzz98]

I am attempting to create a report based on what a user chooses from a varity of drop down and text boxes on a form. Does anybody have any idea on how I could do this?

 

[Geekmomz]

Are you actually trying to build the report dynamically or are you using the user's selections as criteria for the report? If you are trying to use the selections as criteria, the below code from one of my db's might be useful to you. There's probably a better way to do this, but this way definitely works.

Dim strFilter As String

'The line below ensures that we have at least one element in the criteria, 
'so that we don't get an error.  Any string-type field could be used, 
'or any field for which a constant criteria value is known (such as orderdate > #1/1/1980#).

strFilter = "([SalesRep] Like ""*"")"

If Forms!CustPOData!boxCustName <> &quot;&quot; Then
strFilter = strFilter & &quot; And &quot; & BuildCriteria(&quot;CustName&quot;, dbText, Forms!CustPOData!boxCustName)
End If
If Forms!CustPOData!boxCustPO <> &quot;&quot; Then
strFilter = strFilter & &quot; And &quot; & BuildCriteria(&quot;CPOID&quot;, dbText, Forms!CustPOData!boxCustPO)
End If
If Forms!CustPOData!boxWorkOrder <> &quot;&quot; Then
strFilter = strFilter & &quot; And &quot; & BuildCriteria(&quot;WorkOrderNum&quot;, dbText, Forms!CustPOData!boxWorkOrder)
End If
If Forms!CustPOData!boxRMA <> &quot;&quot; Then
strFilter = strFilter & &quot; And &quot; & BuildCriteria(&quot;RMANum&quot;, dbText, Forms!CustPOData!boxRMA)
End If
If Forms!CustPOData!boxProdID <> &quot;&quot; Then
strFilter = strFilter & &quot; And &quot; & BuildCriteria(&quot;ProdID&quot;, dbText, Forms!CustPOData!boxProdID)
End If
If Forms!CustPOData!boxManufPN <> &quot;&quot; Then
strFilter = strFilter & &quot; And &quot; & BuildCriteria(&quot;ManufPN&quot;, dbText, Forms!CustPOData!boxManufPN)
End If
If Forms!CustPOData!Date1 <> &quot;&quot; Then
strFilter = strFilter & &quot; And &quot; & BuildCriteria(&quot;OrderDate&quot;, dbDate, &quot;>=&quot; & Forms!CustPOData!Date1)
strFilter = strFilter & &quot; And &quot; & BuildCriteria(&quot;OrderDate&quot;, dbDate, &quot;<=&quot; & Forms!CustPOData!Date2)
End If

'Use the two lines below to open a query with these criteria
    DoCmd.OpenQuery &quot;queryname&quot;, acNormal, acReadOnly
    DoCmd.ApplyFilter , strFilter
'Or use the line below to open a report with these criteria.
'Comment out the unneeded line(s).
    DoCmd.OpenReport &quot;reportname&quot;, acViewPreview, , strFilter

HTH
Geekmomz

 

[utahjzz98]

That looks like what I was looking for, I will give it a try and let you know what happens. Thanks for the help!

 

[Geekmomz]

Great! Let me know if you have a problem trying to implement the code and I'll see if I can help you work it out.

Geekmomz

 

[utahjzz98]

Do I need to do anything special with the report in order for it to accept the parameters? I can run the code with no errors, but it doesn't seem to be using the filter on the report. It looks like it is still using the default datasource that the report was built with instead of overiding it with the filter.

Any ideas?

 

[utahjzz98]

bump to the top, still stuck =/

 

[Geekmomz]

You don't need to do anything special to the report in order for it to accept the parameters. Can you post the code here that you are using to call the report so that I can take a look at it?

 

[utahjzz98]

Private Sub cmdGetReport_Click()
Dim strFilter As String

strFilter = "([LenderName] Like ""*"&quot"

If Forms!frmReports!cboLenderName <> &quot;&quot; Then
strFilter = strFilter & &quot; and &quot; & BuildCriteria(&quot;Lender Name&quot;, dbText, Forms!frmReports!cboLenderName)
End If

DoCmd.OpenReport &quot;rptSuspended&quot;, acViewPreview,strFilter
End Sub

 

[Geekmomz]

OK, You actually only have one criterion to use, so you don't need to use BuildCriteria. That is for if you want to check if they have made selections from multiple boxes. The result of the code you actually have is that the records will be returned if LenderName matches the choice from the combo box or *, which it will every time unless it is null. Try this instead

Private Sub cmdGetReport_Click()
    Dim strFilter As String
    
    If Forms!frmReports!cboLenderName <> &quot;&quot; Then
        strFilter = &quot;[Lender Name] Like &quot; & Forms!frmReports!cboLenderName
    DoCmd.OpenReport &quot;rptSuspended&quot;, acViewPreview, strFilter

    Else
'Instead of opening the unfiltered report here, you could
'open a MsgBox telling the user that s/he needs to select 
'a lender name from the form.
    DoCmd.OpenReport &quot;rptSuspended&quot;, acViewPreview
    End If
    
End Sub