Turning filtered data in a sub form into a report

[chrisjames25]

Hi

I have a mainform and in the header of that form their is a command button to create a report.

WHat i have in the form is the header of the main form, then a subform within the detail of the form. THe subform is a continuous form made to look like a posh datasheet which is able to be filtered and sorted via drop downs or a search box.

I have the following code which is doing half the job.

DoCmd.OpenReport "report1", acViewReport, "Qry_Main_VarietyForm"

This takes into account anything that has been filtered by using the txt search box but it does not take into account anything that has been filtered using the drop down filters. I had the same problem with exporting data and you guys solved problem for me using the following:

  Dim strSql As String
  Dim TempQdf As QueryDef
  Dim db As DAO.Database
  Set db = CurrentDb
  
  strSql = "Select * from Qry_Main_VarietyForm"
  If Me.Frm_Subform.Form.Filter <> "" Then
   strSql = strSql & " WHERE " & Me.Frm_Subform.Form.Filter
  End If
  Set TempQdf = db.CreateQueryDef("ExportFiltereds", strSql)
  DoCmd.OutputTo acOutputQuery, TempQdf.Name, acFormatXLSX
  db.QueryDefs.Delete TempQdf.Name

HOwever i am unsure whether this is the correct approach in this case and if so how to tweak the code to make it work.

Any help massively appreciated.

 

[theDBguy]

Hi. Just a thought but what do you get with this one instead?

DoCmd.OpenReport "report1", acViewReport, , Me.Filter

 

[chrisjames25]

Hi. Annoyingly laptop flat to test your theory.

Quick one. Instead of me.filter should it be me.frm_subfom.form.filter

 

[theDBguy]

Hi. Annoyingly laptop flat to test your theory.

Quick one. Instead of me.filter should it be me.frm_subfom.form.filter

Hi. It could/should be, depending. Give it a try and let us know what happens. What I gave you is using the filter of the form where the button is located. If the filter is elsewhere, then yes, the code needs to be modified to fit. Cheers!

 

[chrisjames25]

Hi

Tested following code

DoCmd.OpenReport "report1", acViewReport, "Qry_Main_VarietyForm", Me.Frm_Subform.Form.Filter

And when i click on button it pops open an enter parameter dialog box. If i remove the

, Me.Frm_Subform.Form.Filter

from the code it runs normally.

Any ideas why this occurs

 

[June7]

I have never used FilterName. Don't know if it causes issue to use FilterName and WhereCondition arguments at the same time.

Does FilterName argument work?

If you want to provide db for analysis, follow instructions at bottom of my post.

 

[Gasman]

That is generally due to Access not recognising the variable.

Is the subform control really called Frm_Subform ?

Hi

Tested following code

DoCmd.OpenReport "report1", acViewReport, "Qry_Main_VarietyForm", Me.Frm_Subform.Form.Filter

And when i click on button it pops open an enter parameter dialog box. If i remove the

, Me.Frm_Subform.Form.Filter

from the code it runs normally.

Any ideas why this occurs

 

[chrisjames25]

Hi Gasman

The subform control is called Frm_Subform and the source object within is called Frm_Main_Variety

 

[chrisjames25]

COnfused in a way that the export data button works with following code but a parameter is being asked for when trying to populate a report

 Dim strSql As String
  Dim TempQdf As QueryDef
  Dim db As DAO.Database
  Set db = CurrentDb
  
  strSql = "Select * from Qry_Main_VarietyForm"
  If Me.Frm_Subform.Form.Filter <> "" Then
   strSql = strSql & " WHERE " & Me.Frm_Subform.Form.Filter
  End If
  Set TempQdf = db.CreateQueryDef("ExportFiltereds", strSql)
  DoCmd.OutputTo acOutputQuery, TempQdf.Name, acFormatXLSX
  db.QueryDefs.Delete TempQdf.Name

 

[Gasman]

It would help if you said what the name of the parameter being requested is.?

Also Debug.Print Me.Frm_Subform.Form.Filter to see what it actually contains, not what you think it might contain.

 

[chrisjames25]

HI

PLease see attached some screenshots of the underlying query build and the main form i am using.

To reiterate i can get the report to work correctly and open if i just use the textbox search function. But if i use the inbuilt filters i get the parameter requests attached.

 

[chrisjames25]

OK i have figured it out.

  DoCmd.OpenReport "Report1", acViewReport, , Me.Frm_Subform.Form.Filter

THis code works but i was making 1 error.

THe recordsource for the report was not the same named query as the query in the subform.

THe design in each was identical but it seems that unless the same query was used it wont work.

Now it does. Thanks for all your help

 

[chrisjames25]

Spoke too soon. THe filters are now applying but any sort orders i have selected are not transferring.

Any ideas on that?

 

[Minty]

Sort orders are determined in the report grouping and sort properties, not by the filter.

Not sure how you would get around that, without applying some VBA code to the report to examine the current forms sort order and alter the report properties.

Another route would be to use transfer spreadsheet to export the current forms recordset to xlsx, not sure how complex your report formatting is if you needed it in excel?

 

[theDBguy]

Spoke too soon. THe filters are now applying but any sort orders i have selected are not transferring.

Any ideas on that?

You could try passing the sort order through the OpenArgs parameter and then applying it in the Open event of the form.

 

[Gasman]

I was thinking you could pass the sort order in as openArgs?

This link seems to have the same idea.https://bytes.com/topic/access/answers/711146-change-report-sort-order-vba

If you are playing with the sort order I'd expect you will need to change a group levels to match?, if you have any.

 

[MajP]

If you do not close the calling form then you can pull both the sort and the filter from the calling form.

Private Sub Report_Load()
    Dim strFilter As String
    Dim strSort As String
    
    strFilter = Forms!YourFormName.SubformControlName.Form.Filter
    strSort = Forms!YourFormName.SubformControlName.Form.OrderBy
    Me.OrderBy = strSort
    Me.Filter = strFilter
    Me.FilterOn = True
    Me.OrderByOn = True
End Sub