Filtered Form Query
in [Queries]
|
From: b-rick on 29 Jan 2010 16:46 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?
From: Allen Browne on 29 Jan 2010 22:32 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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "b-rick" <b-rick(a)discussions.microsoft.com> wrote in message news:792040A6-9A69-4DA3-BC74-B96FABE23FF6(a)microsoft.com... > 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?
From: b-rick on 1 Feb 2010 14:43 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" wrote: > 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. > > -- > Allen Browne - Microsoft MVP. Perth, Western Australia > Tips for Access users - http://allenbrowne.com/tips.html > Reply to group, rather than allenbrowne at mvps dot org. > > > "b-rick" <b-rick(a)discussions.microsoft.com> wrote in message > news:792040A6-9A69-4DA3-BC74-B96FABE23FF6(a)microsoft.com... > > 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? > > . >
From: Allen Browne on 1 Feb 2010 19:08 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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "b-rick" <brick(a)discussions.microsoft.com> wrote in message news:961D870C-2B3F-4B3B-B9F8-B9D31E501BC5(a)microsoft.com... > 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" wrote: > >> 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. >> >> -- >> Allen Browne - Microsoft MVP. Perth, Western Australia >> Tips for Access users - http://allenbrowne.com/tips.html >> Reply to group, rather than allenbrowne at mvps dot org. >> >> >> "b-rick" <b-rick(a)discussions.microsoft.com> wrote in message >> news:792040A6-9A69-4DA3-BC74-B96FABE23FF6(a)microsoft.com... >> > 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? >> >> . >>
From: b-rick on 2 Feb 2010 09:10 Exactly like that. Thank you , Allen. "Allen Browne" wrote: > 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 > Tips for Access users - http://allenbrowne.com/tips.html > Reply to group, rather than allenbrowne at mvps dot org. > > > "b-rick" <brick(a)discussions.microsoft.com> wrote in message > news:961D870C-2B3F-4B3B-B9F8-B9D31E501BC5(a)microsoft.com... > > 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" wrote: > > > >> 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. > >> > >> -- > >> Allen Browne - Microsoft MVP. Perth, Western Australia > >> Tips for Access users - http://allenbrowne.com/tips.html > >> Reply to group, rather than allenbrowne at mvps dot org. > >> > >> > >> "b-rick" <b-rick(a)discussions.microsoft.com> wrote in message > >> news:792040A6-9A69-4DA3-BC74-B96FABE23FF6(a)microsoft.com... > >> > 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? > >> > >> . > >> > . >
|
Pages: 1 Prev: BETWEEN a rock AND a hard place Next: MESSAGE BOX DISPLAYS INPUT CHARACTERS |