If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Search form for multiple fields
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. |
#2
|
|||
|
|||
Search form for multiple fields
Use a form with 3 text boxes.
-- KARL DEWEY Build a little - Test a little "GIraffe" wrote: 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. |
#3
|
|||
|
|||
Search form for multiple fields
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. "KARL DEWEY" wrote: Use a form with 3 text boxes. -- KARL DEWEY Build a little - Test a little "GIraffe" wrote: 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. |
#4
|
|||
|
|||
Search form for multiple fields
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. -- KARL DEWEY Build a little - Test a little "GIraffe" wrote: 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. "KARL DEWEY" wrote: Use a form with 3 text boxes. -- KARL DEWEY Build a little - Test a little "GIraffe" wrote: 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. |
#5
|
|||
|
|||
Search form for multiple fields
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 "GIraffe" wrote: 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. |
#6
|
|||
|
|||
Search form for multiple fields
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 "Ken Sheridan" wrote: 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 "GIraffe" wrote: 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. |
#7
|
|||
|
|||
Search form for multiple fields
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. -- TedMi |
Thread Tools | |
Display Modes | |
|
|