Filtered Form Query

[b-rick]

I have a form that is filtered using Allen Browne's ajbFindAsUType module
(http://allenbrowne.com/AppFindAsUType.html).

I want to now create a query that utilizes only the filtered records as its
source in order to perform a computation. Is there a way to pass the
filtered Recordset into a query via a command button?

 

[Allen Browne]

You can use the RecordsetClone of the form to step through the records.

You may be able to pass the Filter from the form on to whatever other
operation you need to perform.

For example, you could create a report that summarizes the data, and open it
like this:
Dim strWhere As String
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "Report1", acViewPreview , , strWhere

Or you could replace the last line with this kind of thing:
Debug.Print DSum("Amount", "Table1", strWhere)

Or:
strSQL = "SELECT * FROM Query1 WHERE " & strWhere
Set rs = dbEngine(0)(0).OpenRecordset(strSql)

In Access 2002 and later, if you filter on a combo where the bound field is
hidden, the filter string may contain something like "Lookup_xxx". To get
that to work in the report or in another query, you'd need to alias the
table to match the name the combo used for it.

 

[b-rick]

Thanks for the quick reply. I ended up using a combination of your
suggestions to open a report where the RecordSource is a query of the
filtered form (see below).


In the Form there is a command button:
Private Sub cmd_TopAccount_Click()
Dim strWhere As String
Dim strSql As String
If Me.FilterOn Then
strWhere = Me.Filter
strSql = "SELECT Top 5 * FROM qryAccounts WHERE " & strWhere
Else
strSql = "SELECT Top 5 * FROM qryAccounts"
End If
DoCmd.OpenReport "rptTopAccount", acViewPreview, , , , strSql
End Sub

For the report:
Private Sub rptTopAccount_Open()

Me.RecordSource = Me.OpenArgs

End Sub

One more question. If my query has no results, then of course the report
opens up blank. Is there a way to test the RecordSource and then have a text
box that states "No Matching Records", or something similar? I have tried
RecourdSource BOF and EOF and also Me.Report.HasData in the report's open
event. Me.Report.HasData seems to always be False even when i get results.
The BOF and EOF caused errors.

Thanks for the great help!

 

[Allen Browne]

Just cancel the report's NoData event.
Access only fires this event if there's no data.

Example event procedure:
Cancel = True
MsgBox "Nuffin 2 C"

If you used OpenReport to open the report, trap error 2501 in that routine.
(Error 2501 is the way Access notifies your report that the OpenReport did
not succeed.)

 

[b-rick]

Exactly like that. Thank you , Allen.

Just cancel the report's NoData event.
Access only fires this event if there's no data.

Example event procedure:
Cancel = True
MsgBox "Nuffin 2 C"

If you used OpenReport to open the report, trap error 2501 in that routine.
(Error 2501 is the way Access notifies your report that the OpenReport did
not succeed.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.



.