Results 1 to 10 of 10
  1. #1
    Join Date
    May 2004
    Posts
    37

    Question Unanswered: Changing Query Criteria

    Hi,

    How can I change the criteria of a query? For example, I have a Query called qryQuery1. It has the following fields:

    Item ID
    Item Author
    Item Question
    Item Classification

    I want the user to have the ability to limit the items by, say, Item Author and Item Classification so I would like to have the query criteria change accordingly.

    How can this be achieved?

    Thank you

  2. #2
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    A good way is for the querie criteria to reference an open form. You can either reference a field or an unbound text box

    So you might has as follows in the criteria

    [Forms]![YourFormName]![UnboundTextBoxName]

    You enter the autors name in the unbound text box.

    Another way is to have a macro or code open the query in design view and make you query so that the fields that will have criteria added are at the far left of the query.

    Mike

  3. #3
    Join Date
    May 2004
    Posts
    37
    Mike,

    Where would I put the following:

    [Forms]![YourFormName]![UnboundTextBoxName]?


    Thanks!

  4. #4
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Quote Originally Posted by infinitx
    Mike,

    Where would I put the following:

    [Forms]![YourFormName]![UnboundTextBoxName]?


    Thanks!
    You place it on the criteria row just as you would a name that you searched on. So instead of entering "Jones" you enter the

    [Forms]![YourFormName]![UnboundTextBoxName]

    If you query two fields then you reference a second text box and place that on the criteria row for the other field.

    [Forms]![YourFormName]![UnboundTextBoxNameNumber2]

    As is the base with entering "Jones" and Item123 if you place them on the same row then that is an And search. If both fields don't have a corresponding entry then no records will be returned.

    If you place the criteria on different criteria rows then that gives an Or search. So now it becomes Jones Or Item123. This would return any records that have either Jones or Item 123.

    But when placed on the same row for an And search then only records that have both Jones and Item123 will be returned.

    You can also make a form on this type of query and have a macro or code open the form. So if you entered Jones and Item123 in your unbound text boxes then when you opened this second form it would display only those records that met the search criteria

    Mike

  5. #5
    Join Date
    May 2004
    Posts
    37
    What if I want the user to be able to limit the data by item author or by item classification. So if the user clicks "item author" a prompt box will pop up and ask the user to input an item author and then display all of the items that contain that author. I don't want it to be limit to item author and item classification.

    In other words, I don't want it to display records that have either the item author OR the item classification, I just want to display the records based on what the user wants (in this case just item author or just item classification)

    Thank you

  6. #6
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Quote Originally Posted by infinitx
    What if I want the user to be able to limit the data by item author or by item classification. So if the user clicks "item author" a prompt box will pop up and ask the user to input an item author and then display all of the items that contain that author. I don't want it to be limit to item author and item classification.

    In other words, I don't want it to display records that have either the item author OR the item classification, I just want to display the records based on what the user wants (in this case just item author or just item classification)

    Thank you
    If you only had entry for one text box then no records would be returned unless it was Or

    So this can mean you need more than one query.

    The way this type of thing is easily handled is with the use of Visible and Invisible.

    A simple example would be that when the person selects an author then TextBox1 is made visible. If he then selects and Item then Textbox2 becomes visible and so on.

    If he has a label or button to click on to open the query or a form based on the query then the appropriate label of button becomes visible.

    If a form is to be opened then it is given the appropriate recordsource.

    So what you have is several options and queries and the ones that become "visible" depend on what the user does.

    Are you familiar with SetValue macro actions (or its counterpart in code) and also applying conditions?

    Mike

  7. #7
    Join Date
    May 2004
    Posts
    37
    Are you familiar with SetValue macro actions (or its counterpart in code) and also applying conditions?
    I am not, but I'll look into that.

    So basically what you are saying is that I need two queries that are the same in every aspect except the criteria, right? And then I would have two different forms with different queries as record sources.

    Am I correct?


    Thanks!

  8. #8
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Quote Originally Posted by infinitx
    I am not, but I'll look into that.

    So basically what you are saying is that I need two queries that are the same in every aspect except the criteria, right? And then I would have two different forms with different queries as record sources.

    Am I correct?


    Thanks!
    You may end up needing more than two queries depending on what you ultimately need.

    But you only need one form. The recordsoucre for the form is changed. Of course you can easily male two forms and one is just a copy of the other but with a different query as the recordsource or data source.

    However this can lead to problems later. One is that things can get messy on the screen/form you would operate from.

    But there is a bigger potential problem. Many macros or code that is run from a form by clicking on a label or button needs to reference the form. What this means is that the same code or macros won't work on a form with a different name.

    But I would advice you get the books out and help screen and look at SetValue. If you master SetValue and applying conditions you will be able to do just about anything you want in Access.

    "Conditions" invariably make reference to values on a form. So you have things like

    [Forms]![YourForm]![FieldName] Is Null or
    [Forms]![YourForm]![FieldName] >=100 or
    [Forms]![YourForm]![FieldName] Like "A"

    These sorts of "conditions" determine whether a macro or code runs or they determine what part of the macro or code runs.

    The SetValue action allows you to Set the Value of fields and it also allows you to Set the Value of form properties such as Visible, colour, size in fact all of the things that you can do in form design.

    Mike

  9. #9
    Join Date
    May 2004
    Posts
    37
    Quote Originally Posted by Mike375
    You may end up needing more than two queries depending on what you ultimately need.

    But you only need one form. The recordsoucre for the form is changed. Of course you can easily male two forms and one is just a copy of the other but with a different query as the recordsource or data source.

    However this can lead to problems later. One is that things can get messy on the screen/form you would operate from.

    But there is a bigger potential problem. Many macros or code that is run from a form by clicking on a label or button needs to reference the form. What this means is that the same code or macros won't work on a form with a different name.

    But I would advice you get the books out and help screen and look at SetValue. If you master SetValue and applying conditions you will be able to do just about anything you want in Access.

    "Conditions" invariably make reference to values on a form. So you have things like

    [Forms]![YourForm]![FieldName] Is Null or
    [Forms]![YourForm]![FieldName] >=100 or
    [Forms]![YourForm]![FieldName] Like "A"

    These sorts of "conditions" determine whether a macro or code runs or they determine what part of the macro or code runs.

    The SetValue action allows you to Set the Value of fields and it also allows you to Set the Value of form properties such as Visible, colour, size in fact all of the things that you can do in form design.

    Mike

    Oh, I do know SetValue then. I just didn't know that it was called SetValue!

    Also, can I change the recordsource like this:

    Code:
    Form_frmForm1.RecordSource=qryQuery1
    Thanks a lot!

  10. #10
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    That it. Now conditions determine what will run.

    Conditions can be tricky.

    Perhaps what you could have initially is two queries with say one query requiring Author and the other query requiring both Author and Item number.

    Lets say Author goes into Text1 and Item into Text2.

    When you open your form selecting the records source for the form will depend on whether Text2 Is Null or Text2 Is Not Null.

    Mike

Posting Permissions

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