RE: Search Form - Take 2

I gave you the RowSource property wrongly I'm afraid. It should have been:

SELECT [ID], [Description] FROM Sources ORDER BY [Description];

With that amendment, if you've set the BoundColumn, ColumnCount and
ColumnWidths properties of the combo box in the way I described it should
list all source descriptions alphabetically. While you'll see the
description, the value of the control will actually be the hidden ID value,
which is why the parameter in the query is on the ID column.

Ken Sheridan
Stafford, England

"Walt" wrote:

Ken,

The search function is working like a charm when I enter a keyword in the
Rec or Acc boxes but the problem I am having is with the drop down list. For
some reason the list is empty?

Thank you

Tony

"Ken Sheridan" wrote:

Assuming that by 'keyword' you mean a substring with the value of the
description column in Recommendations or Accomplishments you'll have some
difficulty doing this without writing SQL as you'd need to use subqueries to
restrict the result set for the reports. This stems from a basic design flaw
as its due to the fact that the columns in master allow Nulls. This is not a
good design as the three columns in combination should be the primary key of
master, but a primary key cannot contain a Null. The solution is to include
in each of the Sources, Recommendations and Accomplishments tables an extra
row with a value of 0 as the ID and a value of N/A or similar as the
description. Then use update queries to update all Nulls in master to 0.
Then make the three columns the composite primary key of Master. Then create
relationships between Master and the other three tables or amend existing
relationships, ensuring that referential integrity is enforced.

From then on the solution is pretty much the same as in my reply to your
earlier post, but to reiterate, taking into account your wish to avoid
writing any SQL:

1. Create an unbound dialogue form with the combo box and text boxes,
cboSource, txtRec and TextAcc say. Set the combo box up as follows:

RowSource: SELECT [ID], [Description] FROM Sources ORDER BY Source;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

2. Create a query in which the Master table is joined to each of the other
three.

3. Add the SourceID and Description columns from Master to the query and
each Description column from the others three tables.

4. In the criteria row of the ID column from Master enter:

Forms!YourForm!cboSource OR Forms!YourForm!cboSource IS NULL

5. In the criteria row of the Description column from Recommendations enter:

LIKE "*" & Forms!YourForm!txtRec & "*" OR Forms!YourForm!txtRec IS NULL

6. In the criteria row of the Description column from Accomplishments enter:

LIKE "*" & Forms!YourForm!txtAcc & "*" OR Forms!YourForm!txtAcc IS NULL

7. Save the query. Then test it by opening YourForm (you'll need to change
the above to its real name of course). Select a source from the combo box
and open the query. It should give you the rows for that source. Repeat
with various permutations of values in the other controls and with leaving
some of the controls Null. If the query is returning the correct rows proceed
as follows:

8. Create a report based on the query.

9. Back in the dialogue form in design view add a command button to open
the report. The button wizard will generate the necessary code for you.

You should now be able to open the form, select or enter values in any of
the controls, and then click the button to open the report.

A couple of further comments:

1. If you create a query like the one above in design view, you'll find
that if you reopen it again in design view after saving, Access will have
moved things around quite a bit. The underlying logic will be the same and
the query will work just the same, but you might find it a little tricky to
make any amendments should you need to.

2. If you are allowing free text in the description columns and relying on
substrings of this as 'keywords' this is not very reliable as a user might
well enter the same 'keyword' slightly differently, possibly as a simple
typo. A more reliable solution is to have fixed set of keywords as distinct
values in separate tables which can be referenced by the Recommendations and
Accomplishments tables, enforcing referential integrity so that only
legitimate values can be entered in these tables. Data entry of these
keywords would be via subforms in the form based on the Master table, using a
combo box to select each keyword, and the combo box's NotInList event
procedure can be brought into play for adding new keywords when necessary.

Ken Sheridan
Stafford, England

"Walt" wrote:

Hi again, I'm battled with creating a search form. What I'm trying to do is
create a form with a drop down menu and two text boxes that can be used for
keyword searches.

I have 4 tables: Sources, Recommendations, Accomplishments, and Master

The first three tables contain 2 fields: ID and Description

The last table (Master) has 3 fields that cross reference the IDs and looks
like this:
Source01 | Rec01 | Acc01
Source01 | Rec01 | Acc02
Source01 | Rec02 |
Source02 | Rec03 | Acc05
As you can see, I have duplicates and blanks in this table.

1) What I'm want to do is have the dropdown menu on my form contain the
source descriptions from the source table. If someone selects a source they
can hit the Go command button and get a report with all the recommendations
and accomplishments linked to that source, even if the accomplishment is
blank.

2) Next is the text boxes. I want to tie the text boxes to the
Recommendations and Accomplishments for keyword searches. So, when someone
selects a source, they can put a keyword in the Recommendation box and / or
Accomplishments box and when they hit Go they will get a report on that
Source that contains the recommendation and / or accomplishment based on
their keyword.

I don't know sql or VB and am hoping this is something that can be done
without code. I posted this request previously and had to rethink my
database tables and relationships (now many to many).

I appreciate any help.



.