Complexed Search Form - Stumped Novice User

Hi,

I have to create a search form that is harder than what I know how to do.
The page needs to have the following:

1 - Reports/Plans [drop down list]

2 - Recommendation [keyword search on the rec column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

3 - Accomplishment [keyword search on the acc column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

4 - Results Should inlcude:
Recommendations [check box] (if checked rec's show in report)
Accomplishments [check box] (if checked acc's show in report)
(if both are checked then both show in the report)

Go [button] once clicks returns report based on selections

Reports/plans is a table by itself.
Recommnedations and Accomplishments are in the same table.

I'm hoping this is easier to do than what I'm making it. I appreciate any
assistance.

Thank you

Walt wrote:
Hi,

I have to create a search form that is harder than what I know how to do.
The page needs to have the following:

1 - Reports/Plans [drop down list]

2 - Recommendation [keyword search on the rec column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

3 - Accomplishment [keyword search on the acc column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

4 - Results Should inlcude:
Recommendations [check box] (if checked rec's show in report)
Accomplishments [check box] (if checked acc's show in report)
(if both are checked then both show in the report)

Go [button] once clicks returns report based on selections

Reports/plans is a table by itself.
Recommnedations and Accomplishments are in the same table.

I'm hoping this is easier to do than what I'm making it. I appreciate any
assistance.

Thank you


You've left us guessing at what these things mean, and that makes it
hard to offer concrete advice. Initial suggestions:

When you select a Report/Plan, you could use an event procedure to
filter the records to include only those related to the selected
Report/Plan. The AfterUpdate event could be the one to use, as it
triggers when an item in the combo is selected.

Then you have two optional independent keyword (or "string") search
conditions. The OnClick event procedure could construct a further
filter condition from the contents of the search boxes (text boxes)
depending on the states of the check boxes, then run the report using
that filter. I _think_ you can set a filter using the Report/Plan combo
box and refine that further by running DoCmd.OpenReport and specifying a
Where condition but I'd want to test that! (Otherwise you'd need to
combine everything into a single filter.)

However, I rather think your data model needs work. If there could be
more than one recommendation or Accomplishment for a given Report/Plan
then you should have them in separate related tables. Rule 1 is get
your table design right before doing anything else. You're rather vague
about what these things are, so I suspect you haven't pinned down what
is to go into your table(s) and fields. This free video sample training
module may help:
http://www.lynda.com/home/Player.aspx?lpk4=31001

For the report, assuming that you do end up with separate tables for
Reports/Plans, Recommendations and Accomplishments I'd be thinking in
terms of a main report with two embedded subreports, made visible or
invisible depending on the check boxes.

Phil, London

Probably the easiest way is to base your report on a query which references
the various control on the form as parameters. You can make a parameter
optional by testing for 'OR parameter IS NULL' in the query, so your query
would be something like this:

SELECT *
FROM [Reports_Plans]
WHERE
([Report_Plan] = Forms![YourForm]![cboReport_Plan]
OR Forms![YourForm]![cboReport_Plan] IS NULL)
AND
([Rec] = Forms![YourForm]![txtRecommendation]
OR Forms![YourForm]![txtRecommendation] IS NULL)
AND
([Acc] = Forms![YourForm]![txtAccomplishment]
OR Forms![YourForm]![txtAccomplishment] IS NULL);

The underlying logic regarding the restriction of the results on the
recommendations and accomplishments is not entirely clear from your post.
The above uses a Boolean AND operation which means that if both a
recommendation and accomplishment parameter are entered in the form a row
would be returned only where it contains both of the parameter values.

Also its not clear whether the values of the rec and acc columns are the
keywords per se, or if the keywords are substrings within the values in these
fields. If the latter you'd use the LIKE operator with wildcards rather than
the = equality operator:

SELECT *
FROM [Reports_Plans]
WHERE
([Report_Plan] = Forms![YourForm]![cboReport_Plan]
OR Forms![YourForm]![cboReport_Plan] IS NULL)
AND
([Rec] LIKE "*" & Forms![YourForm]![txtRecommendation] & "*"
OR Forms![YourForm]![txtRecommendation] IS NULL)
AND
([Acc] LIKE "*" & Forms![YourForm]![txtAccomplishment] & "*"
OR Forms![YourForm]![txtAccomplishment] IS NULL);

I'd strongly recommend that you design and save the query in SQL view, not
design view, as you'll find that if you use the latter Access will move
things around a lot after you save it and it will be less easy to make any
amendments to the query.

To hide/show the Recommnedations and/or Accomplishments in the report add
code to the Format event procedure of the report header to hide/show the
controls bound to these fields:

Me.[acc].Visible = Forms![YourForm]![chkShowAcc]
Me.[rec].Visible = Forms![YourForm]![chkShowRec]

You'll need to change the table, fields, form and control names in the above
to your real ones of course.

The 'Go' button on the form simply needs to open the report.

Take note of what Phil has said about the possible need to decompose your
table into related tables. It doesn't affect the above, however, as if the
table is decomposed the query would simply be based on both tables joined,
rather than on the one.

Ken Sheridan
Stafford, England

"Walt" wrote:

Hi,

I have to create a search form that is harder than what I know how to do.
The page needs to have the following:

1 - Reports/Plans [drop down list]

2 - Recommendation [keyword search on the rec column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

3 - Accomplishment [keyword search on the acc column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

4 - Results Should inlcude:
Recommendations [check box] (if checked rec's show in report)
Accomplishments [check box] (if checked acc's show in report)
(if both are checked then both show in the report)

Go [button] once clicks returns report based on selections

Reports/plans is a table by itself.
Recommnedations and Accomplishments are in the same table.

I'm hoping this is easier to do than what I'm making it. I appreciate any
assistance.

Thank you

Ken Sheridan wrote:
Probably the easiest way is to base your report on a query which references
the various control on the form as parameters. You can make a parameter
optional by testing for 'OR parameter IS NULL' in the query, so your query
would be something like this:

SELECT *
FROM [Reports_Plans]
WHERE
([Report_Plan] = Forms![YourForm]![cboReport_Plan]
OR Forms![YourForm]![cboReport_Plan] IS NULL)
AND
([Rec] = Forms![YourForm]![txtRecommendation]
OR Forms![YourForm]![txtRecommendation] IS NULL)
AND
([Acc] = Forms![YourForm]![txtAccomplishment]
OR Forms![YourForm]![txtAccomplishment] IS NULL);

The underlying logic regarding the restriction of the results on the
recommendations and accomplishments is not entirely clear from your post.
The above uses a Boolean AND operation which means that if both a
recommendation and accomplishment parameter are entered in the form a row
would be returned only where it contains both of the parameter values.

Also its not clear whether the values of the rec and acc columns are the
keywords per se, or if the keywords are substrings within the values in these
fields. If the latter you'd use the LIKE operator with wildcards rather than
the = equality operator:

SELECT *
FROM [Reports_Plans]
WHERE
([Report_Plan] = Forms![YourForm]![cboReport_Plan]
OR Forms![YourForm]![cboReport_Plan] IS NULL)
AND
([Rec] LIKE "*" & Forms![YourForm]![txtRecommendation] & "*"
OR Forms![YourForm]![txtRecommendation] IS NULL)
AND
([Acc] LIKE "*" & Forms![YourForm]![txtAccomplishment] & "*"
OR Forms![YourForm]![txtAccomplishment] IS NULL);

I'd strongly recommend that you design and save the query in SQL view, not
design view, as you'll find that if you use the latter Access will move
things around a lot after you save it and it will be less easy to make any
amendments to the query.

To hide/show the Recommnedations and/or Accomplishments in the report add
code to the Format event procedure of the report header to hide/show the
controls bound to these fields:

Me.[acc].Visible = Forms![YourForm]![chkShowAcc]
Me.[rec].Visible = Forms![YourForm]![chkShowRec]

You'll need to change the table, fields, form and control names in the above
to your real ones of course.

The 'Go' button on the form simply needs to open the report.

Take note of what Phil has said about the possible need to decompose your
table into related tables. It doesn't affect the above, however, as if the
table is decomposed the query would simply be based on both tables joined,
rather than on the one.

Ken Sheridan
Stafford, England

"Walt" wrote:

Hi,

I have to create a search form that is harder than what I know how to do.
The page needs to have the following:

1 - Reports/Plans [drop down list]

2 - Recommendation [keyword search on the rec column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

3 - Accomplishment [keyword search on the acc column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

4 - Results Should inlcude:
Recommendations [check box] (if checked rec's show in report)
Accomplishments [check box] (if checked acc's show in report)
(if both are checked then both show in the report)

Go [button] once clicks returns report based on selections

Reports/plans is a table by itself.
Recommnedations and Accomplishments are in the same table.

I'm hoping this is easier to do than what I'm making it. I appreciate any
assistance.

Thank you



That's a great solution from Ken (as usual). Makes me realise I still
talk SQL with a heavy "procedural code" accent! I might be losing my
reluctance to express an opinion but I learn as much as anyone on this
group!

:-)

Phil

Hi Walt,

The simple solution to creating a search form that is harder than what you
know how to do is to get someone to create the form for you. I can build
your search form for you. I have been providing this kind of help with
Access applications for over ten years. My fee would be very modest! The
first step would be to make sure your database meets Phil's Rule 1 which is
get your table design right before doing anything else. I would check the
design of your tables and work with you if modifications need to be made.
Once Phil's Rule 1 is satisfied then we would work closely together to
create your search form. If you would like to work together, contact me.

Steve





"Walt" wrote in message
...
Hi,

I have to create a search form that is harder than what I know how to do.
The page needs to have the following:

1 - Reports/Plans [drop down list]

2 - Recommendation [keyword search on the rec column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

3 - Accomplishment [keyword search on the acc column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

4 - Results Should inlcude:
Recommendations [check box] (if checked rec's show in report)
Accomplishments [check box] (if checked acc's show in report)
(if both are checked then both show in the report)

Go [button] once clicks returns report based on selections

Reports/plans is a table by itself.
Recommnedations and Accomplishments are in the same table.

I'm hoping this is easier to do than what I'm making it. I appreciate any
assistance.

Thank you

"Steve" wrote in message
...

The simple solution to creating a search form that is harder than what you
know how to do is to get someone to create the form for you. I can build
your search form for you. I have been providing this kind of help with
Access applications for over ten years. My fee would be very modest! The
first step would be to make sure your database meets Phil's Rule 1 which
is get your table design right before doing anything else. I would check
the design of your tables and work with you if modifications need to be
made. Once Phil's Rule 1 is satisfied then we would work closely together
to create your search form. If you would like to work together, contact
me.

Steve



These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

A few gems gleaned from the Word New User newsgroup over the Christmas
holidays to show Stevie's "expertise" in Word.


Dec 17, 2008 7:47 pm

Word 2007 ..........
In older versions of Word you could highlght some text then go to Format -
Change Case and change the case of the hoghloghted text. Is this still
available in Word 2007? Where?
Thanks! Steve


Dec 22, 2008 8:22 pm

I am designing a series of paystubs for a client. I start in landscape and
draw a table then add columns and rows to setup labels and their
corresponding value. This all works fine. After a landscape version is
completed, I next need to design a portrait version. Rather than strating
from scratch, I'd like to be able to cut and paste from the landscape
version and design the portrait version.
Steve


Dec 24, 2008, 1:12 PM

How do you protect the document for filling in forms?
Steve


One of my favourites:
Dec 30, 2008 8:07 PM - a reply to stevie
(The original poster asked how to sort a list and stevie offered to create
the OP an Access database)

Steve wrote:
Yes, you are right but a database is the correct tool to use not a
spreadsheet.


Not at all. If it's just a simple list then a spreadsheet is perfectly
adequate...




John... Visio MVP

John... Visio MVP wrote:
"Steve" wrote in message
...

The simple solution to creating a search form that is harder than what
you know how to do is to get someone to create the form for you. I can
build your search form for you. I have been providing this kind of
help with Access applications for over ten years. My fee would be very
modest! The first step would be to make sure your database meets
Phil's Rule 1 which is get your table design right before doing
anything else. I would check the design of your tables and work with
you if modifications need to be made. Once Phil's Rule 1 is satisfied
then we would work closely together to create your search form. If you
would like to work together, contact me.

Steve



These newsgroups are provided by Microsoft for FREE peer to peer
support. There are many highly qualified individuals who gladly help for
free. Stevie is not one of them, but he is the only one who just does
not get the idea of "FREE" support. He offers questionable results at
unreasonable prices. If he was any good, the "thousands" of people he
claims to have helped would be flooding him with work, but there appears
to be a continuous drought and he needs to constantly grovel for work.

A few gems gleaned from the Word New User newsgroup over the Christmas
holidays to show Stevie's "expertise" in Word.


Dec 17, 2008 7:47 pm

Word 2007 ..........
In older versions of Word you could highlght some text then go to Format -
Change Case and change the case of the hoghloghted text. Is this still
available in Word 2007? Where?
Thanks! Steve


Dec 22, 2008 8:22 pm

I am designing a series of paystubs for a client. I start in landscape and
draw a table then add columns and rows to setup labels and their
corresponding value. This all works fine. After a landscape version is
completed, I next need to design a portrait version. Rather than strating
from scratch, I'd like to be able to cut and paste from the landscape
version and design the portrait version.
Steve


Dec 24, 2008, 1:12 PM

How do you protect the document for filling in forms?
Steve


One of my favourites:
Dec 30, 2008 8:07 PM - a reply to stevie
(The original poster asked how to sort a list and stevie offered to
create the OP an Access database)

Steve wrote:
Yes, you are right but a database is the correct tool to use not a
spreadsheet.


Not at all. If it's just a simple list then a spreadsheet is perfectly
adequate...




John... Visio MVP


To the OP (Original Poster) - just see if you can find a useful
contribution Steve has ever made to this group. Google for MVP, while
you're at it.

Phil

Walt,

Here's something that might get you started and it's FREE...

http://allenbrowne.com/ser-62.html

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Walt" wrote in message
...
Hi,

I have to create a search form that is harder than what I know how to do.
The page needs to have the following:

1 - Reports/Plans [drop down list]

2 - Recommendation [keyword search on the rec column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

3 - Accomplishment [keyword search on the acc column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

4 - Results Should inlcude:
Recommendations [check box] (if checked rec's show in report)
Accomplishments [check box] (if checked acc's show in report)
(if both are checked then both show in the report)

Go [button] once clicks returns report based on selections

Reports/plans is a table by itself.
Recommnedations and Accomplishments are in the same table.

I'm hoping this is easier to do than what I'm making it. I appreciate any
assistance.

Thank you

OP, please beware of the 'help' Steve provides....

A must-read before you contact him...
http://home.tiscali.nl/arracom/whoissteve.html

Regards, Arno R

Ken, Philip, John, and Gina - thank you all. I truly apprecaite the help
this forum and class act people like youselves provide; especially to those
of us still learning access but being asked to develop databases that go
beyond our capabilities.

Based on the comments, I broke the tables out. Here's what I did and I hope
my explanations make more sense. I now have 10 tables. Here's the names and
columns:

1)Plan1: Plan1ID, Description
2)Plan2: Plan2ID, Description
3)Plan3: Plan3ID, Description
4)Sources: SourceID, Description
5)Recommendations: RecID, Description
6)Accomplishments: AccID, Description, Impact, Obstacles, Next Steps
7)POC: POCID, 1stName, LastName, Title, Office, Phone, Email

8)Junction_Master:Plan1ID, Plan2ID, Plan3ID, SourceID, RecID,AccID, POCID
(My thinking was I need a table that relates all of the IDs together to make
querying easier - is that necessary?)

9)Junction_Plans: Plan1ID, Plan2ID, Plan3ID (this table is a cross reference
of the plans. For example: Plan1ID01 is the same as Plan2ID01,02,03 and
Plan3ID01 - 10
I created this table to try to ref the ID numbers in the master table so I
can query and pull the description of all plans that relate to a given
recommendation and/or accomplishment

10)Junction_SRA:SourceID,RecID, AccID (Not sure I need this table)

Here's what I'm trying to do. My database needs to allow a POC to input a
recommedation or accomplishment or both. The POC should be able to tell me
what plan the rec / acc ties into and based on their selection I should be
able to run a report that automatically pulls the other plans that coincide.
A recommendation will not always have an accomplishment and vice versus.
Also, not all rec/acc will have POCs which I think stops me from being able
to use autonumbers so I'm using text boxes for the IDs. I have set-up any of
the data entry forms yet, thinking that since i have a lot of information
already it would be better to get the search function working so I can pull
reports to give to my bosses now...and buy me some time.

Thank you all again for all the help because I truly need it.


"Ken Sheridan" wrote:

Probably the easiest way is to base your report on a query which references
the various control on the form as parameters. You can make a parameter
optional by testing for 'OR parameter IS NULL' in the query, so your query
would be something like this:

SELECT *
FROM [Reports_Plans]
WHERE
([Report_Plan] = Forms![YourForm]![cboReport_Plan]
OR Forms![YourForm]![cboReport_Plan] IS NULL)
AND
([Rec] = Forms![YourForm]![txtRecommendation]
OR Forms![YourForm]![txtRecommendation] IS NULL)
AND
([Acc] = Forms![YourForm]![txtAccomplishment]
OR Forms![YourForm]![txtAccomplishment] IS NULL);

The underlying logic regarding the restriction of the results on the
recommendations and accomplishments is not entirely clear from your post.
The above uses a Boolean AND operation which means that if both a
recommendation and accomplishment parameter are entered in the form a row
would be returned only where it contains both of the parameter values.

Also its not clear whether the values of the rec and acc columns are the
keywords per se, or if the keywords are substrings within the values in these
fields. If the latter you'd use the LIKE operator with wildcards rather than
the = equality operator:

SELECT *
FROM [Reports_Plans]
WHERE
([Report_Plan] = Forms![YourForm]![cboReport_Plan]
OR Forms![YourForm]![cboReport_Plan] IS NULL)
AND
([Rec] LIKE "*" & Forms![YourForm]![txtRecommendation] & "*"
OR Forms![YourForm]![txtRecommendation] IS NULL)
AND
([Acc] LIKE "*" & Forms![YourForm]![txtAccomplishment] & "*"
OR Forms![YourForm]![txtAccomplishment] IS NULL);

I'd strongly recommend that you design and save the query in SQL view, not
design view, as you'll find that if you use the latter Access will move
things around a lot after you save it and it will be less easy to make any
amendments to the query.

To hide/show the Recommnedations and/or Accomplishments in the report add
code to the Format event procedure of the report header to hide/show the
controls bound to these fields:

Me.[acc].Visible = Forms![YourForm]![chkShowAcc]
Me.[rec].Visible = Forms![YourForm]![chkShowRec]

You'll need to change the table, fields, form and control names in the above
to your real ones of course.

The 'Go' button on the form simply needs to open the report.

Take note of what Phil has said about the possible need to decompose your
table into related tables. It doesn't affect the above, however, as if the
table is decomposed the query would simply be based on both tables joined,
rather than on the one.

Ken Sheridan
Stafford, England

"Walt" wrote:

Hi,

I have to create a search form that is harder than what I know how to do.
The page needs to have the following:

1 - Reports/Plans [drop down list]

2 - Recommendation [keyword search on the rec column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

3 - Accomplishment [keyword search on the acc column]
*if Report/Plan is selected then search is only on that item, otherwise
entire column)

4 - Results Should inlcude:
Recommendations [check box] (if checked rec's show in report)
Accomplishments [check box] (if checked acc's show in report)
(if both are checked then both show in the report)

Go [button] once clicks returns report based on selections

Reports/plans is a table by itself.
Recommnedations and Accomplishments are in the same table.

I'm hoping this is easier to do than what I'm making it. I appreciate any
assistance.

Thank you