Need to code a variable Query Critereon in a forms vba.

[Guest]

eHey all

So here is what I need to do.

I have a form that when you click a button with open another form that is
based on a query. That query has a variable criteron where you enter what you
are looking for utilizing the [Enter search] type method.

What I want to do is capture what a person types in there into a string, so
I can then utilize that from the form they are on. So when a person clicks
the button and the dialogue box comes up, they type in "hello" I would like
that to be captured in vba, so all functions on that form can continue to
work with that variable.

 

[Marshall Barton]

I have a form that when you click a button with open another form that is
based on a query. That query has a variable criteron where you enter what you
are looking for utilizing the [Enter search] type method.

What I want to do is capture what a person types in there into a string, so
I can then utilize that from the form they are on. So when a person clicks
the button and the dialogue box comes up, they type in "hello" I would like
that to be captured in vba, so all functions on that form can continue to
work with that variable.

It is better to use a text box on a form instead of a
parameter prompt as a query's criteria. The criteria would
look like:
Forms!nameofform.nameof textbox
Then, the newly opened form can just refer to to the
original form the same way to retrieve what the user
entered.

If you must use a prompt string in the query's criteria,
then add the prompt string to the query's field list so you
can refer to it in the form. [Note that this is not
necessary in a report where it is automatically made
available]

 

[Guest]

What I really need to do though, is capture the users input, so I have that
saved and can use it for other coding, the way the database has already been
formed it would be easiest to do it in this fashion.

I have a form that when you click a button with open another form that is
based on a query. That query has a variable criteron where you enter what you
are looking for utilizing the [Enter search] type method.

What I want to do is capture what a person types in there into a string, so
I can then utilize that from the form they are on. So when a person clicks
the button and the dialogue box comes up, they type in "hello" I would like
that to be captured in vba, so all functions on that form can continue to
work with that variable.

It is better to use a text box on a form instead of a
parameter prompt as a query's criteria. The criteria would
look like:
Forms!nameofform.nameof textbox
Then, the newly opened form can just refer to to the
original form the same way to retrieve what the user
entered.

If you must use a prompt string in the query's criteria,
then add the prompt string to the query's field list so you
can refer to it in the form. [Note that this is not
necessary in a report where it is automatically made
available]

 

[Marshall Barton]

I am unaware of any way to retrieve a parameter's value from
a form's record source query without modifying the query or
using a different kind of parameter.
--
Marsh
MVP [MS Access]

What I really need to do though, is capture the users input, so I have that
saved and can use it for other coding, the way the database has already been
formed it would be easiest to do it in this fashion.

I have a form that when you click a button with open another form that is
based on a query. That query has a variable criteron where you enter what you
are looking for utilizing the [Enter search] type method.

What I want to do is capture what a person types in there into a string, so
I can then utilize that from the form they are on. So when a person clicks
the button and the dialogue box comes up, they type in "hello" I would like
that to be captured in vba, so all functions on that form can continue to
work with that variable.

It is better to use a text box on a form instead of a
parameter prompt as a query's criteria. The criteria would
look like:
Forms!nameofform.nameof textbox
Then, the newly opened form can just refer to to the
original form the same way to retrieve what the user
entered.

If you must use a prompt string in the query's criteria,
then add the prompt string to the query's field list so you
can refer to it in the form. [Note that this is not
necessary in a report where it is automatically made
available]

 

[Guest]

Looks like I need to think of a different way to do it then =).

So let me lay this example out then, and see what would be the best way to
go about this.

I have a query that pulls inventory information for all of america and
canada. the user input further refines this query by region. I have this
currently as a user entered parameter in the query I use for this. The Form
which is just the graphical interface for this, and also has some programming
in it for ease of use, would need to use that region specific source.

How would I program the form, so it pulls up lets say just the "west" region
without using the, user entered parameter from the query?

I am unaware of any way to retrieve a parameter's value from
a form's record source query without modifying the query or
using a different kind of parameter.
--
Marsh
MVP [MS Access]

What I really need to do though, is capture the users input, so I have that
saved and can use it for other coding, the way the database has already been
formed it would be easiest to do it in this fashion.

havocdragon wrote:
I have a form that when you click a button with open another form that is
based on a query. That query has a variable criteron where you enter what you
are looking for utilizing the [Enter search] type method.

What I want to do is capture what a person types in there into a string, so
I can then utilize that from the form they are on. So when a person clicks
the button and the dialogue box comes up, they type in "hello" I would like
that to be captured in vba, so all functions on that form can continue to
work with that variable.

It is better to use a text box on a form instead of a
parameter prompt as a query's criteria. The criteria would
look like:
Forms!nameofform.nameof textbox
Then, the newly opened form can just refer to to the
original form the same way to retrieve what the user
entered.

If you must use a prompt string in the query's criteria,
then add the prompt string to the query's field list so you
can refer to it in the form. [Note that this is not
necessary in a report where it is automatically made
available]

 

[Marshall Barton]

Looks like I need to think of a different way to do it then =).

So let me lay this example out then, and see what would be the best way to
go about this.

I have a query that pulls inventory information for all of america and
canada. the user input further refines this query by region. I have this
currently as a user entered parameter in the query I use for this. The Form
which is just the graphical interface for this, and also has some programming
in it for ease of use, would need to use that region specific source.

How would I program the form, so it pulls up lets say just the "west" region
without using the, user entered parameter from the query?

Add an unbound text box, or maybe better, a combo box to the
form's header section. Then in the control's after update
event set the form's Filter property or reconstruct the
form's record source query.

Me.Filter = "regionfieldname = """ & Me.comboboxname & """"
Me.FilterOn = True

Since I have had a lot of difficulties with the Filter
property, I prefer to change the record source:

strSQL = "SELECT * FROM your table " _
& "WHERE regionfieldname = """ & Me.comboboxname & """"
Me.RecordSource = strSQL