Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parameter Query (2000)

    I just came from a meeting with our Marketing department and am not sure I can develop the database that they want. (I am sure I will be asking lots of questions). My question is - they want the ability to create queries on the fly. They gave me 12 fields that they want to be able to fill or not. The first field is a field called Members which has member counts. They want to be able to put in the parameter >100 (for instance). Can you do that? My next question is how would I set up a parameter query with 12 parameters where you can either fill it in or press enter and go on to the next parameter . I am looking for ideas......Thanks for any help you can provide.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parameter Query (2000)

    The way to do this is to create a form with text boxes or combo boxes or a combination in which the user can specify the criteria. When the user clicks the "OK" command button, the WHERE condition is created dynamically. Microsoft has an example in MSKB article ACC2000: How to Filter a Report from a Pop-Up Form; it contains a link to a downloadable sample database. This does not do exactly what you want, but it will hopefully give you some ideas.
    Feel free to post more questions as you go along.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Parameter Query (2000)

    Hi Linda

    This is reasonably easy.

    Create a form with the 12 Boxes. Give each a name say txtMember,txtNonMember etc

    From the query that runs that form put all the relevant fields in it and use the parameters as follows:

    >[Forms]![frmhhh]![txtMember]

    >[Forms]![frmhhh]![txtNonMember]

    Create a Report from this query.

    On the form create acommand button that opens that report. Save

    All the officer has to do is say type in 100 in a specific text box and it will find anything over 100

    Is that what you are looking for?
    Jerry

  4. #4
    Lounger
    Join Date
    Sep 2003
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter Query (2000)

    I have an idea - If I create a form with the 12 fields and use Filter by Form. I know how to put a print command button that will print the results. My question is - is there a way to put the Filter by Form, Apply Filter button and Remove Filter button on the form? I need specifics since I am VBA challenged.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parameter Query (2000)

    You can put a Filter By Form button on a form, as well as an Apply Filter button, by using the control wizard. The wizard doesn't offer an option for a Remove Filter button, but it's easy to create one yourself:
    - Turn control wizards off.
    - Place a command button on the form.
    - Name it CmdRemoveFilter.
    - Set its caption to Remove Filter.
    - Activate the Event tab of the Properties window.
    - Click in the On Click box.
    - Select [Event Procedure] from the dropdown list.
    - Click the ... to the right of the dropdown arrow.
    - Make the code look like this:

    Private Sub cmdRemoveFilter_Click()
    DoCmd.ShowAllRecords
    End Sub

    - Switch back to Access.

    BUT none of these buttons, in particular the Apply Filter one, will work while Filter by Form is on. Access disables all command buttons in Filter by Form mode. The user will have to use the toolbar button or menu option to apply the filter.

  6. #6
    Lounger
    Join Date
    Sep 2003
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter Query (2000)

    That didn't work like I thought. I kept the Remove Filter button. I made a custom toolbar with Filter by form and apply filter. When I open the form, how can I have the custom toolbar open when the form is open?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parameter Query (2000)

    Set the Toolbar property of the form (in the Other tab of the Properties window) to the name of the custom toolbar.

  8. #8
    Lounger
    Join Date
    Sep 2003
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter Query (2000)

    Everything works but the Print button. It prints everything and not just the filtered records. I am using the following code: I am using my home computer so I have Access 2003

    Option Compare Database
    Dim iFilterType As Integer


    Private Sub cmdPrint_Click()
    Dim frm As Form
    Set frm = Forms!frmFilter
    If iFilterType = acApplyFilter Then
    DoCmd.OpenReport "rptFilter", acViewPreview, frm.Filter
    Else
    DoCmd.OpenReport "rptFilter", acViewPreview
    End If
    End Sub

    Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
    iFilterType = ApplyType
    End Sub

    Thanks for your help....

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parameter Query (2000)

    Change

    DoCmd.OpenReport "rptFilter", acViewPreview, frm.Filter

    to

    DoCmd.OpenReport "rptFilter", acViewPreview, , frm.Filter

    (WhereCondition is the 4th argument of OpenReport)

  10. #10
    Lounger
    Join Date
    Sep 2003
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter Query (2000)

    Thanks Hans - I guess I was too tired to notice a missing comma.

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter Query (2000)

    Another problem. I created the form and when I Filter by Form, in the dropdown list beside each field, I get two choices Null and Not Null. Shouldn't I get the data that is in the query behind the form? I used a query for the form because I want everything at a Client level. I changed the control source to the table and still get Null and Not Null. Any ideas.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parameter Query (2000)

    Do you have a split frontend/backend design? Filter by Form is severely limited when dealing with linked tables.

  13. #13
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter Query (2000)

    I really haven't even got all the specifications for the database. I haven't split the database. Would the problem be the amount of records? There are 20,000 records. Is that too many to show in a dropdown list?

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parameter Query (2000)

    Select Tools | Options...
    Activate the Edit | Find tab.
    Increase the number in the box "Don't display lists where more than this number of records is read". The default is 1,000. Increasing it to over 20,000 will slow down performance, however.

  15. #15
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Parameter Query (2000)

    As Hans notes, there are performance concerns when you start loading up dropdown lists with large number of records. However testing I have done suggests that some of the concerns date from 1994 and Access 2 when we were still running the original Pentium chips or even slower. I've successfully loaded as large as 60,000 records and gotten surprisingly good results on a Celeron 333 with 64 MB of RAM. Not recommended, but it may be acceptable.

    Another idea you might consider: Filter by Form does have it's limitations as you've been discovering. However on a regular form you can apply filters by using the right click menu and applying various kinds of filters. There you can capture the filter properties of the form, then apply them to a report based on the same data source and print the result. User driven requirements are necessary, but they often over-specify the detail level making the developer's task much more difficult. Another alternative is to create the SQL statement used by the form and report dynamically based on an unbound form - that is more difficult than the Filter by Form approach. Hope this helps.
    Wendell

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •