Filter data based on Textbox Value

[Lori]

Okay here is the problem, I have a form written for Change Orders, this form
includes the project number (which is the field I need to filter by) along
with the various information for each project. The subform includes a the
specific information for the change. My problem is that when I open the form
It asks me the project number twice (once for the main form and once for the
subform) and then when I move from one record to the next the form will again
ask me the project number two more times.

I am trying to find a way to filter the data once by possibly using an
unbound textbox on the form, linked to the query to pull up the information.
I've got the textbox in place with the link to the query working beautifully
but it doesn't feed the information from the query to the form.

I'm not sure if this is the best way to go but I have several forms like
this and I really need to eliminate the annoying "Which Project#?" prompt
popping up twenty times (which is not an exaggeration.) I need to have the
data filtered by project but I really need to find the most efficient way to
accomplish this. HELP!!!

 

[kingston via AccessMonster.com]

How is the subform linked to the main form? If you use [Project] as the
common field, the subform will be updated automatically using the value in
the main form. However, you'll have to modify the underlying query by
removing the parameter that prompts the user. You can also replace the
parameter (and not link the subform) with something like: [Forms]![MainForm]!
[Project].

Is the main form supposed to prompt the user for a project number when
navigating through records? It's not clear how you want the main form to
present the data. Must the user provide input or is this something that you
do not want at all?

 

[Lori]

I use two criteria, first the project # and second would be the change #
since there are several line items per change in some instances (thus the
reason for the subform). The main query is set to filter by project number.
I have the form (not the query) prompt for the project number by way of a
unbound textbox. This works great when I open the query after running the
form but now it doesn't prompt once I run the query. The main form needs to
prompt for the project number, once, which it does with the unbound textbox
I'm using, however, it still prompts repeatedly, I'm not sure how to
eliminate this nuisance.
--
Lori A. Pong

How is the subform linked to the main form? If you use [Project] as the
common field, the subform will be updated automatically using the value in
the main form. However, you'll have to modify the underlying query by
removing the parameter that prompts the user. You can also replace the
parameter (and not link the subform) with something like: [Forms]![MainForm]!
[Project].

Is the main form supposed to prompt the user for a project number when
navigating through records? It's not clear how you want the main form to
present the data. Must the user provide input or is this something that you
do not want at all?

Okay here is the problem, I have a form written for Change Orders, this form
includes the project number (which is the field I need to filter by) along
with the various information for each project. The subform includes a the
specific information for the change. My problem is that when I open the form
It asks me the project number twice (once for the main form and once for the
subform) and then when I move from one record to the next the form will again
ask me the project number two more times.

I am trying to find a way to filter the data once by possibly using an
unbound textbox on the form, linked to the query to pull up the information.
I've got the textbox in place with the link to the query working beautifully
but it doesn't feed the information from the query to the form.

I'm not sure if this is the best way to go but I have several forms like
this and I really need to eliminate the annoying "Which Project#?" prompt
popping up twenty times (which is not an exaggeration.) I need to have the
data filtered by project but I really need to find the most efficient way to
accomplish this. HELP!!!

 

[Lori]

Sorry, to answer the second half of the question, I don't want the form to
prompt everytime you change a record. With the exception of 2 people everyone
else using the database will be only concentrating on on project at a time, I
would like to be able to set the form to ask for the project # once and then
hold that information until the user closes the form.
--
Lori A. Pong

How is the subform linked to the main form? If you use [Project] as the
common field, the subform will be updated automatically using the value in
the main form. However, you'll have to modify the underlying query by
removing the parameter that prompts the user. You can also replace the
parameter (and not link the subform) with something like: [Forms]![MainForm]!
[Project].

Is the main form supposed to prompt the user for a project number when
navigating through records? It's not clear how you want the main form to
present the data. Must the user provide input or is this something that you
do not want at all?

Okay here is the problem, I have a form written for Change Orders, this form
includes the project number (which is the field I need to filter by) along
with the various information for each project. The subform includes a the
specific information for the change. My problem is that when I open the form
It asks me the project number twice (once for the main form and once for the
subform) and then when I move from one record to the next the form will again
ask me the project number two more times.

I am trying to find a way to filter the data once by possibly using an
unbound textbox on the form, linked to the query to pull up the information.
I've got the textbox in place with the link to the query working beautifully
but it doesn't feed the information from the query to the form.

I'm not sure if this is the best way to go but I have several forms like
this and I really need to eliminate the annoying "Which Project#?" prompt
popping up twenty times (which is not an exaggeration.) I need to have the
data filtered by project but I really need to find the most efficient way to
accomplish this. HELP!!!

 

[kingston via AccessMonster.com]

How do you get the form to prompt the user? Do you do this by way of a
procedure? If so, look at when the procedure is triggered. If you are using
parameter queries to feed the form and subform, you may have to modify them
as I described earlier. I think you can accomplish what you describe via a
nested subform:

Main Form - control/prompt Project
SubForm - Display project information (linked via Project #)
SubSubForm - Display details (linked via Project # and Change #)

Sorry, to answer the second half of the question, I don't want the form to
prompt everytime you change a record. With the exception of 2 people everyone
else using the database will be only concentrating on on project at a time, I
would like to be able to set the form to ask for the project # once and then
hold that information until the user closes the form.

How is the subform linked to the main form? If you use [Project] as the
common field, the subform will be updated automatically using the value in

[quoted text clipped - 26 lines]

 

[Lori]

Originally I had it set to prompt the user via the query however one of our
IT Guys (a self proclaimed expert) recommended that I use the unbound textbox
on the form to prompt for the project #, then point the project number in the
query to look at that textbox. Doing it this way doesn't give me any
information on the form itself. I do know that if I restore it to where it
was, having the query prompt, it will sort properly and give me the
information I need but it still asks for the project number repeatedly (on
some forms every time I change fields, not just records). I'm sure you can
see why I need help with this one.
--
Lori A. Pong

How do you get the form to prompt the user? Do you do this by way of a
procedure? If so, look at when the procedure is triggered. If you are using
parameter queries to feed the form and subform, you may have to modify them
as I described earlier. I think you can accomplish what you describe via a
nested subform:

Main Form - control/prompt Project
SubForm - Display project information (linked via Project #)
SubSubForm - Display details (linked via Project # and Change #)

Sorry, to answer the second half of the question, I don't want the form to
prompt everytime you change a record. With the exception of 2 people everyone
else using the database will be only concentrating on on project at a time, I
would like to be able to set the form to ask for the project # once and then
hold that information until the user closes the form.

How is the subform linked to the main form? If you use [Project] as the
common field, the subform will be updated automatically using the value in

[quoted text clipped - 26 lines]

data filtered by project but I really need to find the most efficient way to
accomplish this. HELP!!!

 

[kingston via AccessMonster.com]

If you have the form loaded by way of a query with a parameter that isn't
tied to a control on the form, then you'll need to close and reopen the form
to prompt the user for a different project number or use code or macros. You
can use the same sorting criteria regardless of the approach. How do you
want the form to behave? It might help to map out the process and the
interface interaction that you ideally want.

Originally I had it set to prompt the user via the query however one of our
IT Guys (a self proclaimed expert) recommended that I use the unbound textbox
on the form to prompt for the project #, then point the project number in the
query to look at that textbox. Doing it this way doesn't give me any
information on the form itself. I do know that if I restore it to where it
was, having the query prompt, it will sort properly and give me the
information I need but it still asks for the project number repeatedly (on
some forms every time I change fields, not just records). I'm sure you can
see why I need help with this one.

How do you get the form to prompt the user? Do you do this by way of a
procedure? If so, look at when the procedure is triggered. If you are using

[quoted text clipped - 16 lines]

 

[Lori]

Ideally, I would like a prompt to ask the user "Which Project #?" and once
they enter it it will give them all of the data for that project without
prompting them multiple times. It did work this way with the prompt coming
from the query originally but changes in the way data needed to be entered
necessitated the subform so now it asks twice when opening and depending on
the form at least twice when changing between records.

The data entered into the form is also set to provide information for
letters that are generated thru the report function. As an example a letter
is generate requesting owner approval for a change order. If I view all
letters it will ask me for the project number twice for every letter in the
system!!! I need it to ask only once.
--
Lori A. Pong

If you have the form loaded by way of a query with a parameter that isn't
tied to a control on the form, then you'll need to close and reopen the form
to prompt the user for a different project number or use code or macros. You
can use the same sorting criteria regardless of the approach. How do you
want the form to behave? It might help to map out the process and the
interface interaction that you ideally want.

Originally I had it set to prompt the user via the query however one of our
IT Guys (a self proclaimed expert) recommended that I use the unbound textbox
on the form to prompt for the project #, then point the project number in the
query to look at that textbox. Doing it this way doesn't give me any
information on the form itself. I do know that if I restore it to where it
was, having the query prompt, it will sort properly and give me the
information I need but it still asks for the project number repeatedly (on
some forms every time I change fields, not just records). I'm sure you can
see why I need help with this one.

How do you get the form to prompt the user? Do you do this by way of a
procedure? If so, look at when the procedure is triggered. If you are using

[quoted text clipped - 16 lines]

data filtered by project but I really need to find the most efficient way to
accomplish this. HELP!!!