Search Form - Take 2

[Walt]

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.

 

[Ken Sheridan]

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

 

[Richard]

Hi Walt,

I found this ready made search form a couple of years ago. If you download
it and look at how its put together you may be able to use parts of it like I
have. I use the code and small popup form and module.

http://www.techonthenet.com/access/forms/search2.php

hth
Richard

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

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.

 

[Walt]

Hi ken and thank you again for assisting me. One question, how do I set up a
composite key in Access 2003? I've never heard of it before. I've done web
searches on it but haven't found anything that spells it out.

Thanks

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

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.

 

[Douglas J. Steele]

While in Design mode, highlight all of the fields you want to include in the
primary key (click in the gray box to the left of the field name while
holding down the Shift key) Click on the "key" icon to add all of the
selected fields to the primary key.



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi ken and thank you again for assisting me. One question, how do I set
up a
composite key in Access 2003? I've never heard of it before. I've done
web
searches on it but haven't found anything that spells it out.

Thanks

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

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.

 

[Walt]

Thanks Douglas....

While in Design mode, highlight all of the fields you want to include in the
primary key (click in the gray box to the left of the field name while
holding down the Shift key) Click on the "key" icon to add all of the
selected fields to the primary key.



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi ken and thank you again for assisting me. One question, how do I set
up a
composite key in Access 2003? I've never heard of it before. I've done
web
searches on it but haven't found anything that spells it out.

Thanks

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

:

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.

 

[Walt]

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

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

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.

 

[Ken Sheridan]

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

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

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

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.