Run a Query through a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query and I'm wondering is there a way to build a form that runs the
query and you can change certain conditions on the query through the form?
 
Yes, this is very common. In the query builder you can reference the control
on the form as part of the criteria. for example:
=Forms!MyFormName.MyControlName
or
Like "*" & Forms!MyFormName.MyControlName & "*"
or
<> Forms!MyFormName.MyControlName
 
You can try this

Create an empty query, for this example will call it MyQuery
Create a button on the form to assign SQL to this Query that include a
filter from a field in the form.

Use the code to change the sql within this query, and
then run it.

Application.CurrentDb.QueryDefs("MyQuery").SQL = "Select * From TableName
Where FieldName = " & Me.[Field Name In the form]
Docmd.OpenQuery "MyQuery"
=======================================
Or create a query with reference to the fields in the form

Select TableName.* From TableName Where FieldName =
Forms![FormName]![FieldName]

And create a button on the form to run the query
 
Is there a way to be prompted for and change the criteria each time I press
the button on the form. I would like to be able to change the criteria
before running the query from the form.

Ofer said:
You can try this

Create an empty query, for this example will call it MyQuery
Create a button on the form to assign SQL to this Query that include a
filter from a field in the form.

Use the code to change the sql within this query, and
then run it.

Application.CurrentDb.QueryDefs("MyQuery").SQL = "Select * From TableName
Where FieldName = " & Me.[Field Name In the form]
Docmd.OpenQuery "MyQuery"
=======================================
Or create a query with reference to the fields in the form

Select TableName.* From TableName Where FieldName =
Forms![FormName]![FieldName]

And create a button on the form to run the query
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



Cindy said:
I have a query and I'm wondering is there a way to build a form that runs the
query and you can change certain conditions on the query through the form?
Click to expand...
Click to expand...
 
Then use the parameter in the query, and then just run the query

Select * From TableName Where FieldName = [Please enter value here:]

That will prompt the use with a message to enter a value, and the query will
filter the result by this value
To run the query
Docmd.OpenQuery "QueryName"

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



Cindy said:
Is there a way to be prompted for and change the criteria each time I press
the button on the form. I would like to be able to change the criteria
before running the query from the form.

Ofer said:
You can try this

Create an empty query, for this example will call it MyQuery
Create a button on the form to assign SQL to this Query that include a
filter from a field in the form.

Use the code to change the sql within this query, and
then run it.

Application.CurrentDb.QueryDefs("MyQuery").SQL = "Select * From TableName
Where FieldName = " & Me.[Field Name In the form]
Docmd.OpenQuery "MyQuery"
=======================================
Or create a query with reference to the fields in the form

Select TableName.* From TableName Where FieldName =
Forms![FormName]![FieldName]

And create a button on the form to run the query
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



Cindy said:
I have a query and I'm wondering is there a way to build a form that runs the
query and you can change certain conditions on the query through the form?
Click to expand...
Click to expand...
Click to expand...
 
Ok that worked great thanks. What if you have more than 1 field that you
would like to specify a criteria for.

In the other field of the query I have a criteria of "K*" but there can be 2
criterias either K or T as the first character. Is there a way to specify
this. Do I use a parameter as well.

Ofer said:
Then use the parameter in the query, and then just run the query

Select * From TableName Where FieldName = [Please enter value here:]

That will prompt the use with a message to enter a value, and the query will
filter the result by this value
To run the query
Docmd.OpenQuery "QueryName"

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



Cindy said:
Is there a way to be prompted for and change the criteria each time I press
the button on the form. I would like to be able to change the criteria
before running the query from the form.

Ofer said:
You can try this

Create an empty query, for this example will call it MyQuery
Create a button on the form to assign SQL to this Query that include a
filter from a field in the form.

Use the code to change the sql within this query, and
then run it.

Application.CurrentDb.QueryDefs("MyQuery").SQL = "Select * From TableName
Where FieldName = " & Me.[Field Name In the form]
Docmd.OpenQuery "MyQuery"
=======================================
Or create a query with reference to the fields in the form

Select TableName.* From TableName Where FieldName =
Forms![FormName]![FieldName]

And create a button on the form to run the query
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



:

I have a query and I'm wondering is there a way to build a form that runs the
query and you can change certain conditions on the query through the form?
Click to expand...
Click to expand...
Click to expand...
 
Try this

Select * From TableName Where FieldName Like "K*" Or FieldName Like "T*"
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



Cindy said:
Ok that worked great thanks. What if you have more than 1 field that you
would like to specify a criteria for.

In the other field of the query I have a criteria of "K*" but there can be 2
criterias either K or T as the first character. Is there a way to specify
this. Do I use a parameter as well.

Ofer said:
Then use the parameter in the query, and then just run the query

Select * From TableName Where FieldName = [Please enter value here:]

That will prompt the use with a message to enter a value, and the query will
filter the result by this value
To run the query
Docmd.OpenQuery "QueryName"

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



Cindy said:
Is there a way to be prompted for and change the criteria each time I press
the button on the form. I would like to be able to change the criteria
before running the query from the form.

:

You can try this

Create an empty query, for this example will call it MyQuery
Create a button on the form to assign SQL to this Query that include a
filter from a field in the form.

Use the code to change the sql within this query, and
then run it.

Application.CurrentDb.QueryDefs("MyQuery").SQL = "Select * From TableName
Where FieldName = " & Me.[Field Name In the form]
Docmd.OpenQuery "MyQuery"
=======================================
Or create a query with reference to the fields in the form

Select TableName.* From TableName Where FieldName =
Forms![FormName]![FieldName]

And create a button on the form to run the query
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



:

I have a query and I'm wondering is there a way to build a form that runs the
query and you can change certain conditions on the query through the form?
Click to expand...
Click to expand...
Click to expand...
 
What if I want to be able to choose either K or T. Is there a way for the
form to prompt me for that field as well?

Ofer said:
Try this

Select * From TableName Where FieldName Like "K*" Or FieldName Like "T*"
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



Cindy said:
Ok that worked great thanks. What if you have more than 1 field that you
would like to specify a criteria for.

In the other field of the query I have a criteria of "K*" but there can be 2
criterias either K or T as the first character. Is there a way to specify
this. Do I use a parameter as well.

Ofer said:
Then use the parameter in the query, and then just run the query

Select * From TableName Where FieldName = [Please enter value here:]

That will prompt the use with a message to enter a value, and the query will
filter the result by this value
To run the query
Docmd.OpenQuery "QueryName"

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



:

Is there a way to be prompted for and change the criteria each time I press
the button on the form. I would like to be able to change the criteria
before running the query from the form.

:

You can try this

Create an empty query, for this example will call it MyQuery
Create a button on the form to assign SQL to this Query that include a
filter from a field in the form.

Use the code to change the sql within this query, and
then run it.

Application.CurrentDb.QueryDefs("MyQuery").SQL = "Select * From TableName
Where FieldName = " & Me.[Field Name In the form]
Docmd.OpenQuery "MyQuery"
=======================================
Or create a query with reference to the fields in the form

Select TableName.* From TableName Where FieldName =
Forms![FormName]![FieldName]

And create a button on the form to run the query
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



:

I have a query and I'm wondering is there a way to build a form that runs the
query and you can change certain conditions on the query through the form?
Click to expand...
Click to expand...
Click to expand...
 
Select * From TableName Where FieldName Like [Please select First letter] &
"*" Or FieldName Like [Please select second letter] & "*"

So, the user can select two letters or if he/she press without entering any
letter, it will display all the records

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



Cindy said:
What if I want to be able to choose either K or T. Is there a way for the
form to prompt me for that field as well?

Ofer said:
Try this

Select * From TableName Where FieldName Like "K*" Or FieldName Like "T*"
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



Cindy said:
Ok that worked great thanks. What if you have more than 1 field that you
would like to specify a criteria for.

In the other field of the query I have a criteria of "K*" but there can be 2
criterias either K or T as the first character. Is there a way to specify
this. Do I use a parameter as well.

:

Then use the parameter in the query, and then just run the query

Select * From TableName Where FieldName = [Please enter value here:]

That will prompt the use with a message to enter a value, and the query will
filter the result by this value
To run the query
Docmd.OpenQuery "QueryName"

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



:

Is there a way to be prompted for and change the criteria each time I press
the button on the form. I would like to be able to change the criteria
before running the query from the form.

:

You can try this

Create an empty query, for this example will call it MyQuery
Create a button on the form to assign SQL to this Query that include a
filter from a field in the form.

Use the code to change the sql within this query, and
then run it.

Application.CurrentDb.QueryDefs("MyQuery").SQL = "Select * From TableName
Where FieldName = " & Me.[Field Name In the form]
Docmd.OpenQuery "MyQuery"
=======================================
Or create a query with reference to the fields in the form

Select TableName.* From TableName Where FieldName =
Forms![FormName]![FieldName]

And create a button on the form to run the query
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



:

I have a query and I'm wondering is there a way to build a form that runs the
query and you can change certain conditions on the query through the form?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top