Report in unbound Form needs dynamic Recordsource and Filter

[The RAH]

Hello all!
What I need:
Buttons underneath (or even on top) a report that do not scroll away, regardless of the size of the report.
The report is only for viewing its contents on screen, no possible printing involved.
This report also has a dynamic record source and a dynamic filter that both need to be set on loading/opening.

Research:
I've been looking into this quite a bit and it seems a massive undertaking to have the buttons float. And several sources recommend not to do this in any case.
Tried a few of the solutions given and cannot get it to work either. So this seems not the way to go.
The only other thing I can think of to make this work is a form (has footer) with the report as a subreport in the detail. This way the footer stays where it is with the buttons on it. And the report can be just scrolled and viewed.
This works fine, until you count in the fact that it needs a dynamic record source AND a dynamic filter. Either of those work separate, but both together makes Access shiver apparently...

What I have now, including the problem with this:
Made a form called [ReportViewer] with a subreport on it that houses the report we want to view.
The footer of the form has the buttons in it. See screen below here.

So far so good, because this seems to do what I need.
It seems to though, because as soon as you add the dynamic record source and dynamic filter part, it will not work.
Been searching and trying solutions and cannot get the source and filter to work together.
This is the code I have now on the Report load:

Private Sub Report_Load()
    Dim strSQL As String
  
    'Set the Record Source to match the department.
    strSQL = "select * from " & CurrentDepartment & " where [DateTime] Between #" & Format(DateFromText, "mm/dd/yyyy") & "# And #" & Format(DateTillText, "mm/dd/yyyy") & "#"
  
    Me.RecordSource = strSQL
  
End Sub

My questions:
1. Is there a solution I'm missing that can have the buttons float in a report? Like I said above here; so they stay in place like you have with them in a form in the header or footer?
2. With the current solution the record source and filter together are not working. Am I missing something simple in the code, or is this not possible?
Again some sources say this is possible and others say it is not. The current "Me.RecordSource = strSQL" code is from one of the access forums (might even been from here), which states that it should work.

 

[CJ_London]

A report is not a form and cannot be made dynamic in the way you want. You can do things like change the sub report source to display a different report and use the subform link properties to display a different set of data - but this is not a true filter

Opening as a report (ie not in a subform control) you can use the parameters to use a different query as a recordsource and apply filters

In either case the sorting is part of the report object and any sorting of the recordsource query is ignored

If the recordsource is a query, you could use vba to change the querydef before opening the report

 

[arnelgp]

I maybe wrong, but, you can't change Recordsource/Recordset of a Report once it is already Loaded.
your best option is to use subform for your report and just mimic the design.

 

[Gasman]

You could base your report on a querydef. Then just change the SQL as needed?

 

[arnelgp]

You could base your report on a querydef. Then just change the SQL as needed?

I tried it but it does not Refresh the report with the new "filter"

 

[CJ_London]

I tried it but it does not Refresh the report with the new "filter"

per post#2

If the recordsource is a query, you could use vba to change the querydef before opening the report

the action would need to be in the button on the main form

subform.sourceobject=""
querydef.SQL="SELECT....."
subform.sourceobject=report.myreport

 

[arnelgp]

that means to Re-open the report.
you can remove the:

subform.sourceobject=""

to minimize the flicker.

 

[Gasman]

I tried it but it does not Refresh the report with the new "filter"

No, I meant, just open with the same query but change the query SQL before opening report.
If they want another filter, close and reopen.

Only because O/P is using something that it was not designed to do.

A form as read only would be better IMO.

 

[The RAH]

Thanks for the replies folks.

So am I understanding you all correctly that the best way would be:
Make a query where I can apply the dynamic filter to the content.
Then open the report with the query as source.

My experience with queries is very little.
I assume you can make 1 query and alter the filter every time before it goes on to be a report?

 

[The RAH]

No, I meant, just open with the same query but change the query SQL before opening report.
If they want another filter, close and reopen.

Only because O/P is using something that it was not designed to do.

A form as read only would be better IMO.

I always paint myself in a corner with wanting to do stuff with Access that it is not suppose to do haha.

If it can be done another way, I'm willing to try as well.
How do you mean as read only form?

 

[Gasman]

I always paint myself in a corner with wanting to do stuff with Access that it is not suppose to do haha.

If it can be done another way, I'm willing to try as well.
How do you mean as read only form?

You can have a form that is set as read only. no edits, no additions, no deletes.
That way you can then apply dynamic filters for whatever criteria you want.

Here is one of mine, actually to correct data.

 

[Gasman]

Thanks for the replies folks.

So am I understanding you all correctly that the best way would be:
Make a query where I can apply the dynamic filter to the content.
Then open the report with the query as source.

My experience with queries is very little.
I assume you can make 1 query and alter the filter every time before it goes on to be a report?

A filter can be applied in the OpenReport command, but you appear to want to change the domain source?

 

[CJ_London]

How do you mean as read only form?

easiest way is in the form design, data tab change the recordset type to snapshot - that will only make the data read only but any unbound controls can still be changed (such as one used for the user to indicate what they want to filter)

I assume you can make 1 query and alter the filter every time before it goes on to be a report?

You need to get your terminology right - in a query you have a where clause, which is a criteria and it limits the number of records to be returned - which will be faster. The objective should be to return as few records as is actually required to minimise network traffic.

A filter is applied to the returned recordset to filter further.

The most efficient method, at least for large datasets is to use a criteria in a query and subsequently filter. For example you have data going back 5 years and you are only interested in the current month. You would use criteria to return only the current month and a filter to filter for a specific day or date range.

If you will know the date range prior to opening the form then apply that as a criteria

Be aware that the docmd.openform/report WHERE parameter is actually a filter. the db engine prioritises the filtered records for loading first and displaying but will continue to load the other records in the background. Easy to prove - the filter button at the bottom (assuming it is not hidden) will be highlighted and if you turn the filter off, all records will be displayed.

Why does this matter? Other than efficiency, perhaps it doesn't. But If you have a form which displays a department managers personnel, filtered for that manager using the docmd WHERE clause and the filter button is exposed. By removing the filter using the button, the manager will see all personnel from all departments. So it depends if that is the behaviour you want as to whether the button should be visible or not.

 

[Ken Sheridan]

I always paint myself in a corner with wanting to do stuff with Access that it is not suppose to do haha.

If it can be done another way, I'm willing to try as well.
How do you mean as read only form?

One way to restrict a form's recordset is to reference unbound controls as parameters in the form's RecordSource query. The following is a simple example:

PARAMETERS
Forms!frmEmployees!txtDateFrom DATETIME,
Forms!frmEmployees!txtDateTo DATETIME;
SELECT
Employees.*, FirstName & " " & Lastname AS FullName, Projects.Project
FROM Projects RIGHT JOIN (Employees LEFT JOIN ProjectEmployees
ON Employees.EmployeeID=ProjectEmployees.EmployeeID)
ON Projects.ProjectID=ProjectEmployees.ProjectID
WHERE
(CityID=Forms!frmEmployees!cboCity
    OR Forms!frmEmployees!cboCity Is NULL)
AND (DepartmentID=Forms!frmEmployees!cboDepartment
    OR Forms!frmEmployees!cboDepartment Is NULL)
AND (DateAppointed>=Forms!frmEmployees!txtDateFrom
    OR Forms!frmEmployees!txtDateFrom Is NULL)
AND (DateAppointed<Forms!frmEmployees!txtDateTo+1
    OR Forms!frmEmployees!txtDateTo Is NULL)
ORDER BY
LastName, FirstName;

In this case the rows returned in the form can be restricted to those which match values selected or entered in a set of unbound controls in the form's header or footer. The restriction can be on City and/or Department and/or DateAppointed. In the case of the last a range can be defined by entering start and end dates, or only one of the two date parameters can be entered to make the range open ended in one direction or the other.

To implement the restriction once values have been entered in any of the unbound controls the form' recordset is reloaded with Me.Requery, which can in the Click event procedure of a Confirm button, or it can be in each unbound control's AfterUpdate event procedure to drill down through the rows returned as each parameter value is selected or entered.