Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Subform with queries that have parameters

Discussion in 'Information Technology' started by dsnyder, Jul 28, 2006.

  1. dsnyder

    dsnyder
    Expand Collapse
    Guest

    Hello All!

    I have a subform within a form. The subform has pulls up a query that
    has 4 parameters. When I click on the button the form to run this
    form/subform it prompts the parameters.

    The trouble that I am having is that when it prompts the parameters it
    asks them 2 times instead of 1 like it is supposed to. So instead of
    prompting 4 questions it shows 8. Is there any kind of code to prevent
    this or maybe a way to set a form/subform differently to stop this.

    Thanks All

    -Will
     
  2. Loading...

    Similar Threads Forum Date
    Importance of Hukamnama - Some Queries Sikh Sikhi Sikhism Jun 14, 2009
    Queries History of Sikhism Oct 26, 2006

  3. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    Guest

    Will:

    Instead of using simple system generated parameter prompts create a dialogue
    form with four controls, one for each parameter, and a button which opens the
    main parent form. In the subform's query reference the controls on the
    dialogue form as the parameters, using a fully qualified reference for each
    like so:

    Forms!MyForm!MyTextBox

    When you want to open the main form open the dialogue form instead. Al
    parameter values can then be entered in one go and the button clicked. Other
    advantages of using controls as the parameters are that you can build in
    validation code into the button's code to ensure than invalid values aren't
    entered (a missing parameter, or a closing date which is earlier than an
    opening date when defining a date range for example), and that you can use
    controls such as a combo box rather than a text box so that users can select
    from a list rather than typing in a value.

    You can close the dialogue form in the main form's Close event procedure if
    you don't want it to remain open after the main form is closed. The code
    would be along these lines:

    DoCmd.Close, acForm "MyDialogueForm"

    Ken Sheridan
    Stafford, England

    "dsnyder" wrote:

    > Hello All!
    >
    > I have a subform within a form. The subform has pulls up a query that
    > has 4 parameters. When I click on the button the form to run this
    > form/subform it prompts the parameters.
    >
    > The trouble that I am having is that when it prompts the parameters it
    > asks them 2 times instead of 1 like it is supposed to. So instead of
    > prompting 4 questions it shows 8. Is there any kind of code to prevent
    > this or maybe a way to set a form/subform differently to stop this.
    >
    > Thanks All
    >
    > -Will
    >
    >
     
  4. dsnyder

    dsnyder
    Expand Collapse
    Guest

    Ken,

    Thank you for the pointers. It makes sense what you are saying I just
    am not sure on how to incorporate that information into what I have.
    Maybe I didn't describe it enough. The parameters are coming from a
    query. So when I run the form that have the subform/query in it the
    parameters come up to ask questions. This query is used to find
    certain accounts. Such as, If I wanted to look up an account with my
    name on it "Will" and the system I used "_____" and the date it was
    saved "____" that is the type of info I am trying to find out. Mainly
    because after this form, you go to the account you found and can modify
    it. So the main basis of the parameters is to search. This is where I
    didn't know if I should use the combo boxes to search for it. I
    thought it might be easier if they just typed in a name/system/date.

    This probably isn't the most efficient way I'm sure. I am just trying
    to make it more user friendly by only asking the questions one time
    instead of twice. If you still think that the way you explained will
    work let me know and I will give it a go.

    Sorry for not being more detailed in my first question but I am open
    for any suggestions. I appreciate it

    Thanks

    -Will
     
  5. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    Guest

    Will:

    Parameters in a query don't have to be the simple system generated prompts
    which you get if you enter something like [Enter Name:] as a parameter for a
    column in the query; they can equally well be a reference to a control on an
    open form. If you use controls on a form as the parameters the difference
    between this and simple system generated prompts is that all the parameters
    are entered together, rather than having a separate prompt for each pop up
    one after the other. This is less confusing to the user and the overall
    effect is more professional.

    Lets say the query underlying your subform has three parameters. You would
    have three controls on an unbound dialogue form, one for each. They can be
    text boxes, combo boxes, list boxes, whatever is most appropriate for the
    field in question. With fields such as a name where there will be a fixed
    set of names in the database at any one time to choose from a combo box from
    which the name can be selected from a sorted list would usually be the best
    solution. With dates a text box is likely to be better as the dates entered
    as the parameter values are less likely to be a fixed set of values in the
    database; you might want to enter a start date and an end date of a range for
    instance to return rows with dates within the range.

    As well as the controls in which the parameter values are entered or
    selected you need a button on the dialogue form to open the main form. It
    doesn't matter whether the query which references the controls on the form
    underlies the main form or a subform within in it, you just enter a reference
    to each control as the parameter in the criteria row of the relevant column
    in query design view. Say the dialogue form is called frmSearchDlg and a
    combo box on it in which you select a name is called cboNames you'd simply
    enter Forms!frmSearchDlg!cboNames in the criteria row of the relevant column
    in the query in design view. References to other controls on the form would
    be entered in the same way.

    The button on the dialogue form would open the main form with something like
    this in its Click event procedure:

    DoCmd.OpenForm!YourMainForm

    or the button could run a macro to open the form. When the form opens its,
    or its subform's, underlying query would reference the controls on the
    dialogue form, so only the row(s) which match the values entered into the
    form would be returned.

    When you want to open your main form you don't open it directly but open the
    frmSearchDlg form. Values are then entered into the controls on it, which
    you'll have labelled appropriately, and the button clicked to open the main
    form. As far the user is concerned they are still being prompted for
    parameter values, but in a single form rather than one by one.

    To see an example of a query which references a control on a form as a
    parameter in this way take a look at the Invoices Filter query in the sample
    Northwind database. This query is not used as the RecordSource of a form or
    report, but instead as the filter setting for the OpenReport method in the
    code behind the Print Invoice button on the Orders form. Nevertheless the
    query serves to illustrate how a parameter can be a reference to a control on
    a form. The form is not a dialogue form in this case but a form bound to the
    Invoices table, so the reference to the InvoiceID control means the query
    returns the current invoice record, but the principle is the same.

    Ken Sheridan
    Stafford, England

    "dsnyder" wrote:

    >
    > Ken,
    >
    > Thank you for the pointers. It makes sense what you are saying I just
    > am not sure on how to incorporate that information into what I have.
    > Maybe I didn't describe it enough. The parameters are coming from a
    > query. So when I run the form that have the subform/query in it the
    > parameters come up to ask questions. This query is used to find
    > certain accounts. Such as, If I wanted to look up an account with my
    > name on it "Will" and the system I used "_____" and the date it was
    > saved "____" that is the type of info I am trying to find out. Mainly
    > because after this form, you go to the account you found and can modify
    > it. So the main basis of the parameters is to search. This is where I
    > didn't know if I should use the combo boxes to search for it. I
    > thought it might be easier if they just typed in a name/system/date.
    >
    > This probably isn't the most efficient way I'm sure. I am just trying
    > to make it more user friendly by only asking the questions one time
    > instead of twice. If you still think that the way you explained will
    > work let me know and I will give it a go.
    >
    > Sorry for not being more detailed in my first question but I am open
    > for any suggestions. I appreciate it
    >
    > Thanks
    >
    > -Will
    >
    >
     
  6. dsnyder

    dsnyder
    Expand Collapse
    Guest

    Ken,

    Thank you for your help. I got my form working and running the query
    like you explained. Although I have one more final question.
    Sometimes when people are looking up information they are not going to
    know the system name, or maybe the month it was created on. My
    question is, how do you make it so you can leave the combo box blank
    and it just overlook it. I thought maybe it could be done with a
    Like"*" but i just don't think thats going to do it. So if there is
    anyway to let it skip over the field like that please let me know.


    Thank you for your time,

    -Will
     
  7. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    Guest

    Will:

    The way to make a parameter optional is to test for it being NULL, so if in
    the criteria row of a SystemName column you've entered the parameter
    Forms!frm SearchDlg!cboNames say, then to make it so that the user can leave
    the combo box blank you'd extend this to:

    Forms!frm SearchDlg!cboNames OR Forms!frm SearchDlg!cboNames IS NULL

    All a criterion expression in a query has to is evaluate to TRUE. The above
    would translate in SQL to something like:

    WHERE (SystemName = Forms!frmSearchDlg!cboNames OR
    Forms!frmSearchDlg!cboNames IS NULL)

    So if the SystemName field in a row in is the name selected in the combo box
    the expression will evaluate to TRUE, because the first part will be TRUE,
    the second part FALSE, but because they are tacked together with OR, if
    either is TRUE the expression as whole is TRUE. If the combo box is NULL the
    second part of the expression will be TRUE for every row, so the whole
    expression will again evaluate to TRUE and every row will be returned
    regardless of the SystemName value.

    Note that you don't use Forms!frmSearchDlg!cboNames = NULL. If you compare
    something to NULL like this, even another NULL, the answer won't be TRUE or
    FALSE, but NULL. This is because NULL is not a value but an absence of a
    value, an 'unknown', so unknown = unknown must also evaluate to unknown.
    That's why you have to use IS NULL instead (you can also use IS NOT NULL when
    required).

    If you enter an expression like the above in query design view and save the
    query you'll find when you reopen it in design view Access will have moved
    things around. Don't worry, the result will be the same.

    Incidentally you can also do this with simple parameters as well as
    references to controls on a form, e.g.

    [Enter Name:] OR [Enter Name:] IS NULL

    Ken Sheridan
    Stafford, England

    "dsnyder" wrote:

    > Ken,
    >
    > Thank you for your help. I got my form working and running the query
    > like you explained. Although I have one more final question.
    > Sometimes when people are looking up information they are not going to
    > know the system name, or maybe the month it was created on. My
    > question is, how do you make it so you can leave the combo box blank
    > and it just overlook it. I thought maybe it could be done with a
    > Like"*" but i just don't think thats going to do it. So if there is
    > anyway to let it skip over the field like that please let me know.
    >
    >
    > Thank you for your time,
    >
    > -Will
    >
    >
     

Share This Page