Using a form to enter query parameters

  • Thread starter Thread starter sharon
  • Start date Start date
S

sharon

I found some documentation about how to do this. I have
two drop down list boxes and 4 text boxes. They are not
registering the input. I can enter the data I need to but
the input is not being read into the query. Can you help
Please!!!!

Thanks
 
The selections need to be made on the form BEFORE the query is run or
opened. The form MUST be open for the query to read the data from it
(Visible, Yes or No is ok). The syntax to refer to the control on the form
is

Forms!frmMyForm!txtMyTextbox

Remember, if you have a multicolumn combobox, the value of the combobox
comes from the Bound Column. This may not be the visible column in the
combobox.
 
Hi Sharon

you need to set the criteria line of the field(s) that you want to be
affected to reference the field in the form ... ie if you have "startdate"
and "enddate" on your form called frm_reportoptions
then the date field needs to have something similar to
between Forms![frm_reportoptions]![startdate] And
Forms![frm_reportoptions]![enddate]
as its criteria line (use the expression builder to get it exactly right)

hope this helps
Cheers
JulieD
 
I ahve done this but how do you user the expression
builder. I am also thinking it is a bound issue.
-----Original Message-----
Hi Sharon

you need to set the criteria line of the field(s) that you want to be
affected to reference the field in the form ... ie if you have "startdate"
and "enddate" on your form called frm_reportoptions
then the date field needs to have something similar to
between Forms![frm_reportoptions]![startdate] And
Forms![frm_reportoptions]![enddate]
as its criteria line (use the expression builder to get it exactly right)

hope this helps
Cheers
JulieD

I found some documentation about how to do this. I have
two drop down list boxes and 4 text boxes. They are not
registering the input. I can enter the data I need to but
the input is not being read into the query. Can you help
Please!!!!

Thanks
Click to expand...


.
Click to expand...
 
the way i do it is
1. create the query that i want
2. use the report wizard to build a report based on the query
3. create the "report options" form - ensuring that i name each control
something easy to find later (ie if its the start date that i want i name
the control startdate) - close & save form
4. return to the query, click in the criteria line of the field that i
need to reference the control on the form
5. click on the expression builder icon on the toolbar
6. go to forms / all forms / find my "report options" form, in the centre
section double click on the form control that i am using as the criteria
do this for all of the criteria that i'm setting
7. close & save query
8. return to the form & put a command button on it to open the report in
print preview mode - close & save form
9. open form, set criteria, click print button

does this help?

Cheers
JulieD

I ahve done this but how do you user the expression
builder. I am also thinking it is a bound issue.
-----Original Message-----
Hi Sharon

you need to set the criteria line of the field(s) that you want to be
affected to reference the field in the form ... ie if you have "startdate"
and "enddate" on your form called frm_reportoptions
then the date field needs to have something similar to
between Forms![frm_reportoptions]![startdate] And
Forms![frm_reportoptions]![enddate]
as its criteria line (use the expression builder to get it exactly right)

hope this helps
Cheers
JulieD

I found some documentation about how to do this. I have
two drop down list boxes and 4 text boxes. They are not
registering the input. I can enter the data I need to but
the input is not being read into the query. Can you help
Please!!!!

Thanks
Click to expand...


.
Click to expand...
Click to expand...
 
You don't want the form nor its controls bound to
anything; that would enter new data into your underlying
table(s). All of your controls should be unbound.

The combo box, though, has a Bound Column property. When
the user makes a selection, the form control's value
becomes the value stored in the bound column. What is
*displayed* is the first non-zero-width column as
specified in the Column Widths property.

HTH
Kevin Sprinkel
 
Kevin is correct, you probably don't want bound controls on the form for
this. To refer to the control, open the query in design view. In the query
design grid, refer to the control on the form as previously mentioned,
adjusting for the actual names of your form and controls. The line referring
to the form should be placed in the criteria line of the query grid under
the field that the control is supposed to filter. If there are any spaces or
reserved words in the names of the form or controls you'll have to wrap them
in brackets. Once you have it correct, Access will probably wrap the
remainder in brackets, so you can just do it to begin with, if you like.

Example:
[Forms]![frmMy Form]![txtMy Textbox]
 
Thank you for the help. The issue I am having now is that
as soon as I add [Forms]![frmMy Form]![txtMy Textbox]

The query stops bringing back any data.
-----Original Message-----
Kevin is correct, you probably don't want bound controls on the form for
this. To refer to the control, open the query in design view. In the query
design grid, refer to the control on the form as previously mentioned,
adjusting for the actual names of your form and controls. The line referring
to the form should be placed in the criteria line of the query grid under
the field that the control is supposed to filter. If there are any spaces or
reserved words in the names of the form or controls you'll have to wrap them
in brackets. Once you have it correct, Access will probably wrap the
remainder in brackets, so you can just do it to begin with, if you like.

Example:
[Forms]![frmMy Form]![txtMy Textbox]

--
Wayne Morgan
MS Access MVP


I am placing them on the form. How do you make the fields
bound?
Click to expand...


.
Click to expand...
 
Check that:

1) Your criteria is typed correctly and includes an equal
sign, for example:

= [Forms]![Customers]![CustomerID]

2) Your criteria refers to the name of the form *control*,
not the name of the field that it's based on. E.g., by
default, Access names unbound textboxes Text0, Text1,
etc. Check the Name property of each control.

3) Your form is open and contains data when you execute
the query.

4) The criteria are typed in the appropriate matching
fields in your query grid.

5) Try temporarily replacing your cell reference (cut it
to the Clipboard first, so you don't have to retype it)
with a string literal, that is, whatever you'd typed in
the form control, to verify there are actually records
matching this criteria in your table.

6) Multiple criteria entered on the same line in the query
grid are treated as a logical AND. So if you entered "PA"
in a State form control, and "1234" in a Customer Number
control, the query will only return records that match
*both* criteria. It is possible to build a criteria form
that can contain user-selected OR and AND logic for
multiple criteria, but it will require additional
programming.

Keep plugging. You've got to climb the mountain if you
want to enjoy the view... :)

HTH
Kevin Sprinkel
-----Original Message-----
Thank you for the help. The issue I am having now is that
as soon as I add [Forms]![frmMy Form]![txtMy Textbox]

The query stops bringing back any data.
-----Original Message-----
Kevin is correct, you probably don't want bound controls on the form for
this. To refer to the control, open the query in design view. In the query
design grid, refer to the control on the form as previously mentioned,
adjusting for the actual names of your form and controls. The line referring
to the form should be placed in the criteria line of the query grid under
the field that the control is supposed to filter. If there are any spaces or
reserved words in the names of the form or controls you'll have to wrap them
in brackets. Once you have it correct, Access will probably wrap the
remainder in brackets, so you can just do it to begin with, if you like.

Example:
[Forms]![frmMy Form]![txtMy Textbox]

--
Wayne Morgan
MS Access MVP


I am placing them on the form. How do you make the fields
bound?
Click to expand...


.
Click to expand...
.
Click to expand...
 
One more thing to add to Kevin's list of things to check. When you check the
names of the controls on your forms, make sure the names of the controls are
NOT the same as the names of the fields they are bound to (if any of them
happen to be bound controls). If they aren't bound controls, then this isn't
a problem.
 
PS.

If you're still having problems, change to SQL view in the query (View menu,
SQL). Copy and paste the SQL text into a message.
 
Back
Top