"filtering" records

  • Thread starter Thread starter Tammy
  • Start date Start date
T

Tammy

I have the source property of a form set to a query
(could be one of two, depending on how it's opened). The
queries have been designed in Access (not w/VBA). Once
the form is opened, the user selects a certain type of
record/list. I would like to build a query or filter
based on the recordset of the form and the choice of the
user. How can I do this? I have tried just putting in a
select statement - but this does not seem to be using the
recordset(source) of the form.

THANKS!
 
What do you mean by "tried putting in a select statement"? If you put a
valid SELECT statement into the RecordSource property of the Form, it should
certainly define what records are displayed. For example, in the Form's
module,

Me.RecordSource = strSomeSQL

would replace the RecordSource with the contents of the variable strSomeSQL.

Larry Linson
Microsoft Access MVP
 
One quick way to solve this is to create TWO forms, each
one based on only one query. Then, you can build your
filter as applicable in each of the two forms. Why are
you basing one form on two or more queries anyway? (I
have done this before to save programming time space) All
you have to do is create one form and copy and paste it
with another name and then change one of the forms'
RecordSource.

email me if you need to.
 
-----Original Message-----
What do you mean by "tried putting in a select
Click to expand...
statement"?
I was referring to that "select" statement (only I didn't
use a variable) - and it worked for the form itself - or
seemed to. However, on top of that, I would like to
say 'of those records' I now want records which have this
criteria (for option 1) or THIS criteria (for option 2).
(I have this working, just not the way I had hoped!)

** side note, can I do this for a text box?? I would
like to set a default value, but base it on a query. I
need an employee's name in the box, but the entry is
actually based on the position, since the employee may
change! I cannot seem to get it to work. And, something
about trying to create an SQL statement comparing a
string, doesn't seem to want to work (even if I copy it
from a working query). Sorry, this is minor, but it's
bugging me!

If you put a
 
Which records the user accesses are based on the
authority/position of the user. If he/she is in
administration, he/she can access different records (all
records). If not, the user can only access "approved"
records, with in the approved records, there are
different types - SOPs and WIs.

So, upon opening the form, if opened by a standard user,
I would like the form to be based off "approved" records,
then the user gets to choose which type of record he/she
wants to search for.

I do have this working, just by selecting the
approved/not approved records at the same time as the
other selection (based on if statement) and then basing
the form off the main table. It works, but I was hoping
to do the other (and am now curious as to how!)

THANKS for all the help!
 
DanK said:
One quick way to solve this is to create TWO forms, each
one based on only one query. Then, you can build your
filter as applicable in each of the two forms. Why are
you basing one form on two or more queries anyway?
Click to expand...
[snip]

Why *wouldn't* he want to?

It is much better to reuse a common form than to build a bunch of copies just
because you want to look at different subsets of the data. If you do that then
any time you need to make design changes you have to make all of those changes
to your multiple form copies. Then if you miss one you get unexpected results
or errors.
 
I was referring to that "select" statement
(only I didn't use a variable) - and it worked
for the form itself - or seemed to. However,
on top of that, I would like to say 'of those
records' I now want records which have this
criteria (for option 1) or THIS criteria (for
option 2).
Click to expand...

I would most likely create a WHERE clause, append it to the SQL used in the
RecordSource of the Form, and replace the RecordSource. You could use the
Form's Filter property, but I don't find that nearly as straightforward and
simple as replacing the RecordSource.
** side note, can I do this for a text box?
I would like to set a default value, but base
it on a query.
Click to expand...

Neither the Control Source nor Default Value properties support using an SQL
Statement. However, you may be able to do what you want using a "domain
aggregate function" which is a Function that runs an SQL statement for you.
DLookup would be the one to check out for this purpose; there are several
others for other purposes.

Larry Linson
Microsoft Access MVP
 
Back
Top