filrter several reports

I create an unbound form to filter a report and i paste the following code in
the filter button of the form, and it works perfectly; but my problem is that
I need to build this filter to  most of my reports so what I don´t want is to
create a form of this type  for each report. My question is: Is There an easy
way to use this form to all these reports? Thank you in anticipation

Private Sub cmdSearch_Click()
Dim varWhere As Variant
Dim rst As DAO.Recordset

    ' Initialize to Null
    varWhere = Null
        
    ' OK, start building the filter       
       If Not IsNothing(Me.cmbCompanyID) Then
        ' .. build the predicate
        ' Must use a subquery here because the value is in a linking table...
        varWhere = (varWhere + " AND ") & _
            "([ContactID] IN (SELECT ContactID FROM qryCompanyContacts " & _
            "WHERE qryCompanyContacts.CompanyID = " & Me.cmbCompanyID & "))"
    End If
    
  
    ' Check to see that we built a filter
    If IsNothing(varWhere) Then
        MsgBox "Debe introducir al menos un criterio de busqueda.",
vbInformation, gstrAppTitle
        Exit Sub
    End If
    
    ' Open a recordset to see if any rows returned with this filter
    Set rst = CurrentDb.OpenRecordset("SELECT tblContacts.* FROM tblContacts
WHERE " & varWhere)
    ' See if found none
    If rst.RecordCount = 0 Then
        MsgBox "Ninguna persona aparece con este criterio.", vbInformation,
gstrAppTitle
        ' Clean up recordset
        rst.Close
        Set rst = Nothing
        Exit Sub
    End If
    
    ' Hide me to fix later focus problems
    Me.Visible = False
    
            ' Show the full contacts info filtered
            DoCmd.OpenReport "rptContactList", acViewPreview, WhereCondition:
=varWhere
            DoCmd.Maximize
                       
    ' Done
    DoCmd.Close acForm, Me.Name
    ' Clean up recordset
    rst.Close
    Set rst = Nothing

End Sub

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201006/1

0
JOSELUIS
6/3/2010 6:51:42 PM
access.reports 4434 articles. 0 followers. Follow

2 Replies
1175 Views

Similar Articles

[PageSpeed] 33

Yes: you can use one form to filter several reports.

You need a control where the user selects which report they want. That could 
be a option group, or a combo or list box. Use the AfterUpdate event of that 
control to show the filter boxes that apply to that report and hide the 
boxes that don't apply.

Then in the Click event of the command button that actually opens the 
report, you build the filter string from the controls that are Visible and 
Not Null, and finally OpenReport with that WhereCondition.

The example below assumes an option group where each report is one of the 
buttons. In the AfterUpdate of the option group (named grpReport), you call 
it like this so that the combo for filtering on a client (cblClientID) is 
shown if the chose option button optJob or optClient or optClientList:

    Dim bShow As Boolean
    bShow = GroupMatchesButtons(Me.grpReport, Me.optJob, Me.optClient, 
Me.optClientList)
    With Me.cboClientID
        If .Visible <> bShow Then
            .Visible = bShow
        End If
    End With

Here's the function:

Private Function GroupMatchesButtons(grp As OptionGroup, ParamArray 
aoptButtons()) As Boolean
On Error GoTo Err_Handler
    'Purpose:   Return True if the option group value matches on one of the 
option buttons named in the array.
    Dim i As Integer

    For i = LBound(aoptButtons) To UBound(aoptButtons)
        If aoptButtons(i).OptionValue = grp.Value Then
            GroupMatchesButtons = True
            Exit For
        End If
    Next

Exit_Handler:
    Exit Function

Err_Handler:
    Call LogError(Err.Number, Err.Description, conMod & 
".MatchesOptionButtons")
    Resume Exit_Handler
End Function

HTH
-- 
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.


"JOSELUIS via AccessMonster.com" <u58123@uwe> wrote in message 
news:a8fc803873f35@uwe...
> I create an unbound form to filter a report and i paste the following code 
> in
> the filter button of the form, and it works perfectly; but my problem is 
> that
> I need to build this filter to  most of my reports so what I don´t want is 
> to
> create a form of this type  for each report. My question is: Is There an 
> easy
> way to use this form to all these reports? Thank you in anticipation
>
> Private Sub cmdSearch_Click()
> Dim varWhere As Variant
> Dim rst As DAO.Recordset
>
>    ' Initialize to Null
>    varWhere = Null
>
>    ' OK, start building the filter
>       If Not IsNothing(Me.cmbCompanyID) Then
>        ' .. build the predicate
>        ' Must use a subquery here because the value is in a linking 
> table...
>        varWhere = (varWhere + " AND ") & _
>            "([ContactID] IN (SELECT ContactID FROM qryCompanyContacts " & 
> _
>            "WHERE qryCompanyContacts.CompanyID = " & Me.cmbCompanyID & 
> "))"
>    End If
>
>
>    ' Check to see that we built a filter
>    If IsNothing(varWhere) Then
>        MsgBox "Debe introducir al menos un criterio de busqueda.",
> vbInformation, gstrAppTitle
>        Exit Sub
>    End If
>
>    ' Open a recordset to see if any rows returned with this filter
>    Set rst = CurrentDb.OpenRecordset("SELECT tblContacts.* FROM 
> tblContacts
> WHERE " & varWhere)
>    ' See if found none
>    If rst.RecordCount = 0 Then
>        MsgBox "Ninguna persona aparece con este criterio.", vbInformation,
> gstrAppTitle
>        ' Clean up recordset
>        rst.Close
>        Set rst = Nothing
>        Exit Sub
>    End If
>
>    ' Hide me to fix later focus problems
>    Me.Visible = False
>
>            ' Show the full contacts info filtered
>            DoCmd.OpenReport "rptContactList", acViewPreview, 
> WhereCondition:
> =varWhere
>            DoCmd.Maximize
>
>    ' Done
>    DoCmd.Close acForm, Me.Name
>    ' Clean up recordset
>    rst.Close
>    Set rst = Nothing
>
> End Sub
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201006/1
> 
0
Allen
6/4/2010 1:06:48 AM
Thank you very much.

Allen Browne wrote:
>Yes: you can use one form to filter several reports.
>
>You need a control where the user selects which report they want. That could 
>be a option group, or a combo or list box. Use the AfterUpdate event of that 
>control to show the filter boxes that apply to that report and hide the 
>boxes that don't apply.
>
>Then in the Click event of the command button that actually opens the 
>report, you build the filter string from the controls that are Visible and 
>Not Null, and finally OpenReport with that WhereCondition.
>
>The example below assumes an option group where each report is one of the 
>buttons. In the AfterUpdate of the option group (named grpReport), you call 
>it like this so that the combo for filtering on a client (cblClientID) is 
>shown if the chose option button optJob or optClient or optClientList:
>
>    Dim bShow As Boolean
>    bShow = GroupMatchesButtons(Me.grpReport, Me.optJob, Me.optClient, 
>Me.optClientList)
>    With Me.cboClientID
>        If .Visible <> bShow Then
>            .Visible = bShow
>        End If
>    End With
>
>Here's the function:
>
>Private Function GroupMatchesButtons(grp As OptionGroup, ParamArray 
>aoptButtons()) As Boolean
>On Error GoTo Err_Handler
>    'Purpose:   Return True if the option group value matches on one of the 
>option buttons named in the array.
>    Dim i As Integer
>
>    For i = LBound(aoptButtons) To UBound(aoptButtons)
>        If aoptButtons(i).OptionValue = grp.Value Then
>            GroupMatchesButtons = True
>            Exit For
>        End If
>    Next
>
>Exit_Handler:
>    Exit Function
>
>Err_Handler:
>    Call LogError(Err.Number, Err.Description, conMod & 
>".MatchesOptionButtons")
>    Resume Exit_Handler
>End Function
>
>HTH
>> I create an unbound form to filter a report and i paste the following code 
>> in
>[quoted text clipped - 62 lines]
>>
>> End Sub

-- 
Message posted via http://www.accessmonster.com

0
JOSELUIS
6/4/2010 10:38:29 AM
Reply:

Similar Artilces:

Report options
I have a customer on V9 - when they try to amend a receivables report - Account History by Transaction - they get an error message that the range is already entered. They have ranges entered for Debtor and Post Date. They can have a GL account entered as a range - when they delete the range and save the option, then pull up the option and try to add back in the account range - they get an error message re 'Range already entered' Has anyone else come across this previously? Thanks Jean -- JB Have you tried deleting the report option and recreating it? If you have and...

Report Services KPI
Hi All, working with a SQL-Server 2005 i have a problem generating indicators (like kpi) on current dynamic data. Let me describe: Given is a dataset as this: Supplier Year Amount -------- ----- -------- Supp1 2008 10 Supp1 2009 15 Supp1 2010 13 Supp2 2008 15 Supp2 2009 03 Supp2 2010 05 Supp3 2008 12 Supp3 2009 10 Supp3 2010 11 Three Suppliers with their Amounts (whatever the amount is isnt important here) for the last three years. In the report a need a matrix lik...

Printing multiple reports
How can I supress the page eject when printing one report after another from a macro. -- Steve If you want the 2nd report to start on the same page that the first one finished, you will need to make them one report. Open the first report in design view, and add the 2nd report as a subreport in the Report Footer section. If I have not understood your question, please post a follow up. -- 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. "Steve" <Steve@d...

Report by day of week
Is there a way to report on time of day and day of week? In other words is there a way to get detailed sales for the time of day and day of week on the same report. I have a report that gives me time of day and date, but I would like it to show whether it's monday, tuesday, etc. That way I would know which hours are the busiest on which day. Craig Have you tried using the graphs option in POS? This can give you the information that you require, unfortunately there is no other way of getting this using HQ reporting. -- Jenny "Craig" wrote: > Is there a way to repo...

Several questions
So far, this program seems to be everything I could want in a photo program. Just two questions... Is there a dropdown menu bar hidden somewhere? Is there a way to change the interface color? Thanks, Clair Hi, Clair. The menu in Windows Live Photo Gallery is at the top in Gallery view- File/Fix/Info/Publich/E-mail/Print/Make/Slide Show/Extras There is no other menu bar. When you are viewing a photo, the "task pane" can be made to appear on the right by clicking Fix or Info. There are no options to change the color of the program elements from the washed ou...

Parameter report again
Anyone have an overview over available promptex? I have a parameter report which works ok for quote. I want to do the similar with account where I have a button which I click then open a custom report listing all cases to that account. I have created the report. In the aspx file for the quote it says: if(Request.QueryString("oType")=="1084") Response.Redirect("/reports/viewer/html/viewer.aspx?id=1890&promptex-Quote="+Request.QueryString("oId")) What do I have to use for account? I have tried to replace Quote with Account, but it didn't hel...

How do I setup a Crystal Reports server to host CRM 1.2 Reports?
Hi there, I recently upgraded from CRM 1.2 to CRM 3.0. As a result I can no longer run all of our custom crystal reports from the 1.2 installation. Can anyone tell me or point me to a document on the web which instructs on setting up a Crystal Reports server to host the CRM 1.2 reports? My company has a registered copy of Crystal 9 Developer edition & reports server. I don't know much about Crystal and at the moment we don't have time to convert these reports to SQL-RS. Thank you. You could also try http://rpttosql.com/faq.htm which is an email-based service to convert cry...

Selecting several messages
When I used OE I used to be able to select several messages at a time in order to file them. By holding down the Ctrl button I could check the content of each message as I clicked it. I cannot do this with Outlook as it will only allow the first message clicked to be viewed. Is this correct or is there a way around this? -- Valerie Valerie <Valerie@discussions.microsoft.com> wrote: > When I used OE I used to be able to select several messages at a time > in order to file them. By holding down the Ctrl button I could check > the content of each message as I clicked it....

showing last memo entry in report
Hello, I have a db that tracks projects and their details. I have a memo field entitled 'Notes' which is used to enter project data. This field can be quite large as notes can be added on various dates. To keep track of the dates that a particular note was added I simply put in the after update field on the form the following code to add in the date that a note was added: Me.Notes = Me.Notes & " -* " & Format(Date, "dd-mmm-yy") & "*" & vbCrLf I currently have a report that I run for the project details and what I would like is not to ...

Updating several XP-computers
Hi! I have 4 PCs in my LAN and they are running under Windows XP (2 time PRO, 1 time Media Center, 1 time Home). I have a slow Internet connection with just 5 GB volume per months, so when I have to update all my PCs, it takes a lot of time and consumes my download volume. I would like to download all windows patches and updates just one time, store them in a file-server or NAS-server and then apply them to all my 4 PCs. Is there anyway to do that? Thank you very much for your help. gianni wrote: > I have 4 PCs in my LAN and they are running under Windows XP (2 tim...

Report Filrters
I have been struggling with this now for some time. I have report that is based on a query/ The report is grouped on [StudioID] then [Stage] and sorted by date.. Of course there is other Data I have to send each [StudioID] section to a different email. What I am doing now is using sort and filter and manually imputting the [StudioID] running the report selection email | PDF and filling in the blanks. I really need to design the report so that it is more user friendly so that my secretary can do the work. What I want to do to if possible is to have a combo boxed based o...

filrter several reports
I create an unbound form to filter a report and i paste the following code in the filter button of the form, and it works perfectly; but my problem is that I need to build this filter to most of my reports so what I don´t want is to create a form of this type for each report. My question is: Is There an easy way to use this form to all these reports? Thank you in anticipation Private Sub cmdSearch_Click() Dim varWhere As Variant Dim rst As DAO.Recordset ' Initialize to Null varWhere = Null ' OK, start building the filter If Not ...

PA Detailed Trial Balance Reports
After upgrading to GP 9.0 I noticed that when ever I run the PA Detailed Trial Balance - Detail Level, any entry from purchasing related to project will appear without any project details, check the attached file for more details ------------------------------------------------------------------------------------------- The Business scenario is like the following: Create PA Receiving for Non Inventory Item on a project and post to GL, using T&M project type and Accounting method when billed, when I tracked the cost category account in GL I found the transaction, but when I printed th...

Date Based report
What is the best way to run a report with the following date criteria: Daily ,weekly,Monthly, Quarterly, Yearly. I realize that I can enter an [start date] & [end date ] in a expression however the above reports are ones that are consistently required. I would like the user to simply select their choice. TIA ...

Intrastat reports
I have a customer who runs the standard intrastat reports. A problem has arisen in that some invoices are showing on the report and some are not. The invoices not showing have a tax date outwith that month but were posted in the month so that may be why they are not showing on the report. Surely the report should show all invoices posted in that month that are EU transactions. If it only shows invoices that have a tax date in that month then what happens when you get a late purchase invoice with a tax date for last month but posted in the current month, this will not be shown on th...

Tender Summary report
The tender summary report, by definition, should give a summary of all tenders taken for a given time period. The report in RMS, however, joins the TenderEntry table to the Transaction table, and consequently only shows tender information for sales transactions. Payments to Account and deposits are excluded from this report. The result is a partial tender summary report that doesn't give a clear picture of all the tenders taken in a given time period. Does anyone know of a way to create a comprehensive tender report that will provide the details of ALL cash, checks, credit cards...

Smartlist Received Not Invoiced match Received Not Invoiced report
Smartlists (at least in GP9.0) comes with query already written under Receivings Line Items titled "Shipments Received but not Invoiced". This Smartlist, however, does not give the same result as Reports - Purchasing - Analysis - Received/Not Invoiced. The difference appears to be that the report will not include unmatched shipments from closed purchase orders, while the Smartlist will include these. This inconsistency is resulting in many of our users being reluctant to print their own reports and/or smartlists because of the varying answers they get. ---------------- This ...

Several usernames, several domains
Hello. My boss frequently gets his name misspelled and has six additional e-mail aliases to cover this. He also has 18 domains. So now when we're moving to Exchange 2003 he's getting 7x18 addresses. Thats no problem in my book, Email recipeint policies covers adding the 18 domains, but.... how to make every policy cover the seven aliases? best You don't need to. Just add the aliases manually to the user account. As long as the domains are the same, you're covered for him receiving mail properly. You only need one recipient policy for each inbound domain to cover ...

stationary how to make and use several
I need to use serveral differant stationaries to use with differant clients, how do I make more than one and use it, I have one made right now, but can not figure how to use it in a new email, I do not want it to be my default, I only need it once a week or so. ...

Help with approach to report
Hi, The charity I work for received money from different government grants. We have to submit reports government agencies using their formats. The point of this statement is there is no changing the report. I have two tables; tblProject and tblProjectDetail. The tblProject has one record / row per project. The tblProjectDetail has one record / row per thing / activity that was done to the project. At this point in time, I have five project that fall under this reporting requirement. The government defined report is formatted as such: First section of report con...

Suppressing lines on reports
How do you suppress 'zero' lines on reports. For example when reporting Investment value stocks which have all been sold show up on the report (with quantity zero). This increases the size of the report with zeroes in all the columns. You could customize out the investments in question. If the report in question has the option, remove any option to show closed investments. -- Glyn Simpson, Microsoft MVP - Money Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny for UK tips and fixes for MS Money. To send Microsoft your wishes or suggestions, use http://register.m...

Getting consistent report exports to Excel
Hi, I have set up a "favourite" report showing spending by subcategory for the last 12 months as a column of figures. My plan is that each month I will export the figures to an Excel spreadsheet column for that month. This will allow me to track changes in my expenditure patterns based on a rolling 12-month average for each category. Anyway, the problem is that at the moment if a particular subcategory has no expenditure in the month in question then there simply isn't as row for that subcategory, and consequently everything below the missing row is thrown out of allignment in ...

copy several items & paste several items?
Thanks in advance. I want to copy several items into the Office Clipboard (Edit>Office Clipboard...) and then paste those several items into the active sheet (using VBA). (I believe up to 12 items can be stored in the Office clipboard) So rather than copying one item, activating the sheet, pasting it, then activating the sheet to copy from, copying, then activating the sheet I want to paste to one more time, and pasting it, the macro would: select an item copy to Office clipboard select another item copy to Office clipboard activate another sheet select a range pastespecial an item s...

Crystal Reports Session Timeout #2
Hi, we are running currently MS CRM together with Windows 2003, IIS6 and .net 1.1, everything works fine, except Crystal Enterprise. At this moment everyone can see the list of reports but just or Domain Administrator (the Acount which had installed CRM) can open reports, everyone else will get "Crystal Reports Session Timeout". We have tried all Service Packs and Hotfixes from Crystal, nothing changes. In the Eventlog we can see just one entry which shows that something is going wrong: Event Type: Failure Audit Event Source: Security Event Category: Logon/Logoff Event ID: 529 Date...

Extended Pricing Report
We have a huge proble with printing Price book reports. We do not use multicurrency. asically when we prin the report the unit price does not show correctly. if the item price was 10.45 it shows SDG10.0000 or if the price was 12.75 it would show SDG13.00000 Does anyone know what the SDG bit is??? and why our currency seems to be rounded up? I have run a check-links on the multicurrency table and this had no effect I have also discovered that the Currency ID is greyed out and blank in the Extended Pricing Report window... I suspect this is causing the pricing tables to be refreshed usi...