Criteria entered at run time

N

Nancy

I have several databases with queries run by multiple users. Often they come
to me to complain that they must re-enter the criteria for a query (which is
entered at run time) more than once. The only solution I have found is to
recreate the query. Just copy and pasting it doesn't work.

Can anyone tell me how to correct the problem?
 
K

KARL DEWEY

The usual cause of this is that it is not exactly the same such as ---
[Enter start]
[Start Date]
[Enter Start Date]
I always type it one time then copy and paste everywhere else it is needed
so it will be exact.
You can put your criteria entry in an unbound form and reference the objects
of the form as criteria like this ---
[Forms]![YourFormName]![YourTextbox]
You will need to open the form, enter data, then run the query. You can have
a command button on the form.
 
N

Nancy

Karl,
The problem is not with a query linked to a form. It is only when running
the query.

--
Nancy


KARL DEWEY said:
The usual cause of this is that it is not exactly the same such as ---
[Enter start]
[Start Date]
[Enter Start Date]
I always type it one time then copy and paste everywhere else it is needed
so it will be exact.
You can put your criteria entry in an unbound form and reference the objects
of the form as criteria like this ---
[Forms]![YourFormName]![YourTextbox]
You will need to open the form, enter data, then run the query. You can have
a command button on the form.
--
KARL DEWEY
Build a little - Test a little


Nancy said:
I have several databases with queries run by multiple users. Often they come
to me to complain that they must re-enter the criteria for a query (which is
entered at run time) more than once. The only solution I have found is to
recreate the query. Just copy and pasting it doesn't work.

Can anyone tell me how to correct the problem?
Click to expand...
Click to expand...
 
M

Marshall Barton

Nancy said:
The problem is not with a query linked to a form. It is only when running
the query.
Click to expand...


I think you missed the point. Karl was not talking about a
query "linked" to a form. He was suggesting that you use an
**unbound** form with a text box for users to enter the
criteria. Then the query's criteria can refer to the text
box on the form and users will not be prompted for any
values.
 
J

John Spencer

This is often caused by a filter or sort being applied to the query in
datasheet view and then saving the change to the query layout when the
query is saved.

Next time this happens, try opening the query in SQL view and copying
and pasting the SQL into a new query. If the new query runs without
prompting your for parameters multiple times you can guess that the
query is not the problem, but something that has been done in the
datasheet view is the culprit.

Switch to the "query" view and check the properties of the query and the
fields and see if there is something there to cause the problem.

That said, in almost all cases your users should never see a query in
datasheet view. They should see a form (continuous or otherwise) that
displays the data for manipulation or a report that displays the data
for presentation.

Also, simple parameter prompts are good for testing, but as noted
elsewhere, using a form with comboboxes and textboxes is a better way to
handle this situation.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
K

KARL DEWEY

I guess I was not clear in my response.
Post the SQL of your query so it can be examined.

--
KARL DEWEY
Build a little - Test a little


Nancy said:
Karl,
The problem is not with a query linked to a form. It is only when running
the query.

--
Nancy


KARL DEWEY said:
The usual cause of this is that it is not exactly the same such as ---
[Enter start]
[Start Date]
[Enter Start Date]
I always type it one time then copy and paste everywhere else it is needed
so it will be exact.
You can put your criteria entry in an unbound form and reference the objects
of the form as criteria like this ---
[Forms]![YourFormName]![YourTextbox]
You will need to open the form, enter data, then run the query. You can have
a command button on the form.
--
KARL DEWEY
Build a little - Test a little


Nancy said:
I have several databases with queries run by multiple users. Often they come
to me to complain that they must re-enter the criteria for a query (which is
entered at run time) more than once. The only solution I have found is to
recreate the query. Just copy and pasting it doesn't work.

Can anyone tell me how to correct the problem?
Click to expand...
Click to expand...
Click to expand...
 
Top