Search form for multiple fields

[Guest]

I have a DB for various publications and reference tools. I have an OnOpen
Macro so that a window appears that says "What do you want to do?" and I have
2 choices - one of which is "Search". Currently, I have a query attached to
this "button" that pulls up 3 individual search windows, "Title", "Author",
"Series #", one following the other. You can put data for your search in one
or all three of these windows. Is it possible to create a window/form that
has all 3 criteria in one place so you can choose to fill in one, two, or
three options like I do now individually?

I'm not familiar with vb coding (other then the SQL view), so any
suggestions you might have for a layperson would be greatly appreciated.

Thank you.

 

[Guest]

Use a form with 3 text boxes.

 

[Guest]

Thank you Karl. Should I attach the form to the query or the table? I've
been unsuccessful so far with this ... so clearly I'm doing something wrong.

 

[Guest]

Use an unbound form. Use the unbound text boxes as criteria like this --
[Forms]![YourFormName]![TextBox1]
You can add Like function so as to enter partial --
This the enter the start of the field -
Like [Forms]![YourFormName]![TextBox1] & "*"
This the enter any part of the field -
Like "*" & [Forms]![YourFormName]![TextBox1] & "*"

The form must be opened first and data entered in the text boxes before the
query is run.

 

[Guest]

Create an unbound dialogue form with three unbound controls and a command
button. These could be text boxes or they could be combo box's which list
the possible values in order, e.g. for a combo box listing all titles its
RowSource might be:

SELECT Title
FROM Publications
ORDER BY Title;

Whatever type of control you use the query should reference each as a
parameter and, to make the selections optional, test for Nulls, so the query
might be along these lines:

SELECT *
FROM Publications
WHERE
(Title = Forms!frmSearch!cboTitle OR Forms!frmSearch!cboTitle IS NULL)
AND
(Author = Forms!frmSearch!cboAuthor OR Forms!frmSearch!cboAuthor IS NULL)
AND
([Series #] = Forms!frmSearch!cboSeries OR Forms!frmSearch!cboSeries IS NULL);

In the above frmSearch is the name of the dialogue form, and cboAuthor etc
are the names of the controls. Remember that any object names which include
spaces or other special characters have to be wrapped in brackets as with
[Series #]. The parentheses in the above are important to ensure that each
Boolean OR operation evaluates independently of the AND operations. A button
on the form can open the query, or better still a form or report based on the
query. You could of course have separate buttons to present the data in
different ways, one to open a form, one to preview a report and one to print
the report for instance.

BTW I hope you have a separate Authors table which is referenced by the main
publications table and with referential integrity enforced in the
relationship. I once came across three versions of myself as author in a
table of technical references in a database. Two were correct, but missing
my middle initial in one case, the other had changed my middle initial from a
W to a V! Having each author recorded only once in an Authors table would
have prevented this by virtue of the enforcement of referential integrity
between the References and Authors tables which would have meant only a name
in Authors could legitimately be entered in References. Even better would be
to have a numeric AuthorID foreign key column in References and a unique
numeric AuthorID as the primary key of Authors as two authors could have the
same name. There is at least one other Ken Sheridan, who wrote a guide book
to Buffalo (the city not the animal).

Ken (W not V) Sheridan
Stafford, England

 

[Guest]

Ken:

I'm going to go through your complete email tomorrow. However, I did want
to address the separate "Author" table. You brought up a situation I had not
thought of (ie, different initial). The challenge I have is, I have some
publications that have 30 authors! If there's a way to do this, then, I'd
like nothing more then not having to key in authors every time I add a
publication (as I have several hundred publications ahead of me -- I'd like
to learn how to save a few steps).

Donna

Create an unbound dialogue form with three unbound controls and a command
button. These could be text boxes or they could be combo box's which list
the possible values in order, e.g. for a combo box listing all titles its
RowSource might be:

SELECT Title
FROM Publications
ORDER BY Title;

Whatever type of control you use the query should reference each as a
parameter and, to make the selections optional, test for Nulls, so the query
might be along these lines:

SELECT *
FROM Publications
WHERE
(Title = Forms!frmSearch!cboTitle OR Forms!frmSearch!cboTitle IS NULL)
AND
(Author = Forms!frmSearch!cboAuthor OR Forms!frmSearch!cboAuthor IS NULL)
AND
([Series #] = Forms!frmSearch!cboSeries OR Forms!frmSearch!cboSeries IS NULL);

In the above frmSearch is the name of the dialogue form, and cboAuthor etc
are the names of the controls. Remember that any object names which include
spaces or other special characters have to be wrapped in brackets as with
[Series #]. The parentheses in the above are important to ensure that each
Boolean OR operation evaluates independently of the AND operations. A button
on the form can open the query, or better still a form or report based on the
query. You could of course have separate buttons to present the data in
different ways, one to open a form, one to preview a report and one to print
the report for instance.

BTW I hope you have a separate Authors table which is referenced by the main
publications table and with referential integrity enforced in the
relationship. I once came across three versions of myself as author in a
table of technical references in a database. Two were correct, but missing
my middle initial in one case, the other had changed my middle initial from a
W to a V! Having each author recorded only once in an Authors table would
have prevented this by virtue of the enforcement of referential integrity
between the References and Authors tables which would have meant only a name
in Authors could legitimately be entered in References. Even better would be
to have a numeric AuthorID foreign key column in References and a unique
numeric AuthorID as the primary key of Authors as two authors could have the
same name. There is at least one other Ken Sheridan, who wrote a guide book
to Buffalo (the city not the animal).

Ken (W not V) Sheridan
Stafford, England

I have a DB for various publications and reference tools. I have an OnOpen
Macro so that a window appears that says "What do you want to do?" and I have
2 choices - one of which is "Search". Currently, I have a query attached to
this "button" that pulls up 3 individual search windows, "Title", "Author",
"Series #", one following the other. You can put data for your search in one
or all three of these windows. Is it possible to create a window/form that
has all 3 criteria in one place so you can choose to fill in one, two, or
three options like I do now individually?

I'm not familiar with vb coding (other then the SQL view), so any
suggestions you might have for a layperson would be greatly appreciated.

Thank you.

 

[Guest]

Multiple authors per publication are a classic example of many-to-many
relationships - i.e. one author can have many publications, one pub many
authors. You implement this with three tables:
tblAuthor
fields AuthID, FirstName, LastName, Address, affililation, anything else
about this PERSON only - nothing about any publications.
Primary key on AuthID

tblPub
fields PubId, Title, date, keywords, etc.
Primary key on Pubid

tblAuthPub
fields AuthId, PubId, anything else that's unique to this ONE author and ONE
publication.
Primary key on AuthId, PubId - this ensures that an author can be assigned
to a publication only once.