Subform with queries that have parameters

[dsnyder]

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

 

[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]

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

 

[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]

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

 

[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