How to create a report with filtered results from a form

[WilliamGG]

Hello,

I have searched for about a week on how to get this done and I keep drawing blanks. I want to use the results of a filtered form to export out to a report that can be printed.

Here is what I have built thus far.

frmSCBSearchRecords - is a key word search form that has a sub form embedded in it to show the results of the key word.
sfrmCustomerList - is the subform listed above mentioned form.
qryFormResults - is a query that pulls the data from the search list but is only showing the first record in that filtered group instead of all the records that fit that criteria; and that SQL looks like:

SELECT tblMasterSCBDB.[LC No], tblMasterSCBDB.Beneficiary, tblMasterSCBDB.[Vendor No], tblMasterSCBDB.[Bill No], tblMasterSCBDB.[Transaction Date], tblMasterSCBDB.[Bill Amount], tblMasterSCBDB.[PO No], tblMasterSCBDB.[Invoice No], tblMasterSCBDB.[PO Drawn]

FROM tblMasterSCBDB

WHERE (((tblMasterSCBDB.[LC No])=Forms!frmSCBSearchRecords!SubCustomerList.Form![LC No]) And ((tblMasterSCBDB.Beneficiary)=Forms!frmSCBSearchRe cords!SubCustomerList.Form!Beneficiary) And ((tblMasterSCBDB.[Vendor No])=Forms!frmSCBSearchRecords!SubCustomerList.Form![Vendor No]) And ((tblMasterSCBDB.[Bill No])=Forms!frmSCBSearchRecords!SubCustomerList.Form![Bill No]) And ((tblMasterSCBDB.[Transaction Date])=Forms!frmSCBSearchRecords!SubCustomerList.Form![Transaction Date]) And ((tblMasterSCBDB.[Bill Amount])=Forms!frmSCBSearchRecords!SubCustomerList.Form![Bill Amount]) And ((tblMasterSCBDB.[PO No])=Forms!frmSCBSearchRecords!SubCustomerList.Form![PO No]) And ((tblMasterSCBDB.[Invoice No])=Forms!frmSCBSearchRecords!SubCustomerList.Form![Invoice No]) And ((tblMasterSCBDB.[PO Drawn])=Forms!frmSCBSearchRecords!SubCustomerList.Form![PO Drawn]));

On the frmSCBSearchRecords form I have a "Create Report" button with a simple action code:



Private Sub btnCreateReport_Click()
DoCmd.OpenReport "rptNewReport", acViewPreview, ,
End Sub

What I need help on is having all the selected records from the key word search export to a report that can be printed instead of just the first record in the group. Any help would be appreciated.

[ranman256]

Test all controls for a possible filter then build the where clause.
apply the where to the form
and the report

if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value

if IsNull(cboState) then
  me.filterOn = false
else
       'remove 1st And
    sWhere= mid(sWhere,4)

     'just use the filter

  me.filter = sWhere
  me.filterOn = true


    'and / or apply the where to the report

  docmd.openreport  "rMeReport",acViewPreview ,,sWhere
endif

[WilliamGG]

Thank you for the speedy response. I am a neophyte on this so I had a co-worker look at the query and we changed all the "AND" to "OR" and it yielded every record. So my next thought was; should I use a combo of AND and OR statements to narrow it down?

Looking at the code you provided I am lost on and what each thing is asking. What information do you need from me that can provide better clarity on a resolve? I can mock up a data base similar to the one I am working on for sharing but I can't share the actual data base because of the financial information contained in it.

Thanks.

[ranman256]

if you are searching in 1 single field it is: OR
if you are shearching in multiple fields it is: AND

[WilliamGG]

Here s a copy of the database. I noticed when I dumped the material the import is adding blank rows of data for some reason. I hope this will help.

https://www.sendspace.com/file/2k14is