This web page will tell you how to design such a search form within an Access database. It has several positive characteristics:
Previously I had searches conducted by building "on the fly" SQL statements which were then applied as a RECORDSOURCE to a "spreadsheet-style" form. (I actually figured this out myself in what was for me, at the time, a major breakthrough.) You'd have a "frmSearch" form which would then open a "frmBrowse" form. Using a sample search database by Allen Browne, I started creating search forms which combined these 2 forms into one, and used FILTERS for searching records instead of applying a RECORDSOURCE. I also searched using combo-boxes instead of "free form" text boxes, this served as a way of making it quasi-"suggest" criteria that would actually match the records on file. These combo boxes actually used fields from the main table itself as the recordsource (as opposed to a separate table of employees, classes, etc).
Assuming a table named tblTrainingRecords with fields being searched of FirstName and LastName and date_train and date_entered:
SELECT DISTINCT tblTrainingRecords.FirstName FROM tblTrainingRecords ORDER BY tblTrainingRecords.FirstName;
(this was done by using a query and then under query property setting "Unique Values" to "Yes")
Private Sub cmdSearch_Click()
Dim strWhere As String
Dim lngLen As Long
' Employee Number, a "Like" Search
If Not IsNull(Me.cboEmpNumb) Then
strWhere = strWhere & "([EmployeeID] Like ""*" & Me.cboEmpNumb & "*"") AND "
End If
' Train Date
'Note: I have read that this syntax for searching by a date can cause issues
'with computers with "regional" settings not done correctly, they recommend
'a different format. One example is given at http://www.mvps.org/access/forms/frm0001.htm
If Not IsNull(Me.cboDateTrain) Then
strWhere = strWhere & "([date_train] =#" & Me.cboDateTrain & "#) AND "
End If
' First Name, a "Like" Search
If Not IsNull(Me.cboFirstName) Then
strWhere = strWhere & "([FirstName] Like ""*" & Me.cboFirstName & "*"") AND "
End If
'Last Name, a "Like" Search
If Not IsNull(Me.cboLastName) Then
strWhere = strWhere & "([LastName] Like ""*" & Me.cboLastName & "*"") AND "
End If
' Date Entered
If Not IsNull(Me.cboDateEntered) Then
strWhere = strWhere & "([date_entered] =#" & Me.cboDateEntered & "#) AND "
End If
' Instructor, a "Like" Search
If Not IsNull(Me.cboInstructor) Then
strWhere = strWhere & "([Instructor] Like ""*" & Me.cboInstructor & "*"") AND "
End If
' Merge, a Yes/No Field
If Me.chkMerge = True Then
strWhere = strWhere & "([merge] = True) AND "
End If
'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere
'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
|
Private Sub cmdReset_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all records again.
Dim ctl As Control
'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next
'Remove the form's filter.
Me.FilterOn = False
DoCmd.GoToControl "cboEmpNumb" 'Otherwise, the cursor is in "nowhere" land
Me.OrderBy = "" 'it should jump to the 1st search box
Me.OrderByOn = False
End Sub
|
|
When bits of Visual Basic and SQL are shown here, some parts of it are "literal" or "boilerplate"; eg., they will not change no matter your situation. Other parts--names of your fields you're searching for, name of table being search, etc--will vary depending on the names. Basically, it's almost as if you can copy & paste the code and replace the parts that vary with the actual names. With that in mind, to make it easier to read, I devised what I hope is a helpful color scheme. Standard code is this color (blue); this is code you can pretty much copy & paste as is, comment lines are green (as they are in Access, in fact), and most importantly of all--the portions of the code that will vary depending on your situation (names, especially) are colored in red.. This is mentioned again later on when the code starts really coming together. |
The first thing you will want to do--if one doesn't already exist--is create a "browse" form. This form will show you all the records in the table(s) being searched, and do so in a spreadsheet-style layout. This form is NOT used to delete any existing records, or to edit any of them either. It is used to show all the records in the table/query you are searching, and give you the option of opening whichever one you wish (which you can then edit or delete--or view every field of the record.) To create this, create a continous-form based on the table/query whose records you are searching. (If you don't know how to do this, you will need to learn how; to explain all of this is a bit beyond the scope of this page.) Such a form would look similar to the one below:
Save this form with a name like frmBrowseRecords. With the form open under "design" view, assign it the following attributes:
| Tab | Property | Setting |
| Format | Allow Deletions | No |
| Allow Edits | No | |
| Allow Additions | No | |
| Caption | Showing All Records. Double-click your choice to open it. |
This form shows ALL the records in the table you're searching. It is also capable of showing your search results when you're done, and that's what we're ultimately making it for.
A few other loose items to finish here: you need to create an option which will allow this form to return to the "showing all records" mode even after a search is performed, and you need to make it to where you can open your "main form" (the form you use for data-entry into the table you're searching) by selecting a particular entry and either double-clicking it or clicking a button.
Me.Form.RecordSource="tblName" Me.Form.Caption="Showing All Records, double-click your selection to open it."
Substitute the name of your table where it says tblName
You can also make it to where double-clicking an entry opens up. Select the properties of the command button, then the Event tab, then click the .... where you see [Event Procedure] (which should be on the "on click" row). You will see code that looks something like this:
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmMainPOs"
stLinkCriteria = "[ship_to]=" & "'" & Me![po_number] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Copy that portion of the code, and then go to each text box in the "detail" section, select it, and in the EVENT tab (assuming the properties tab is up) click the ..... on the "On Double-Click" row, click "Code Builder," and then paste the code you copied from the command button. (Actually, it's even easier than that--you can copy the code in the left-most text box, then for the other text boxes you would only need to type in something like Call autoid_DblClick(Cancel), but explaining this is not within the scope of this page. If you want me to elaborate, email me.)
Create a new form and choose "design" view. This form will NOT be based on any table, query, etc. Select Form, Properties, and apply the following attributes:
| Tab | Property | Setting |
| Format | Caption | Search Form |
| Record Selectors | No | |
| Navigation Buttons | No |
Save the form and give it a name like frmSearch (that name will be assumed in the code that shows up later)
Now, we will create search boxes. That is, if you want your database to be able to search by, say, last name, date shipped, state, whatever--we are creating those boxes on the search form.
Re-open the form frmSearch in "design" view and, using the TOOLBOX, add a "text box." Name the text box something like "txtLastName". (If you are trying to create a box which searches by, say, state, name it "txtState.") Repeat this step for every search criteria the search form will contain.
Create the buttons for Search and Clear. Create a command button and exit out when the wizard appears. Create another one, same way. With the command button's properties selected, assign the following properties:
| Tab | Property | Setting |
| Format | Caption | &Search |
| Tab | Property | Setting |
| Format | Caption | &Clear |
Make sure your tab order is good (it's changed using "View....Tab Order"). It's annoying to have the tab order not reconcile with the order your controls appear on the screen.
Then, save the form and exit. There is more to be done to this form, but not just yet.
Create a new query based on the table whose records you are searching. Assume NO search criteria with this query. Name the query something like qrySearchRecords. This query will NOT be a permanent part of your database; it is used merely for generating the SQL statements (code) which will perform the search.
Open the query again, in "design" view. Then, select "View....SQL View." You should see code which looks somewhat like the following:
SELECT mytable.* FROM mytable;
At this point, open up a text-editor program (like Windows' own "Notepad," though I recommend Notetab Lite, a free program which is much more useful). Copy/paste the SQL statements into the text-editor. NOTE: What we are doing here is generating SQL statements which will be used for applying criteria to the search. Further, this will involve the use of Visual Basic. Pasting this code into the text-editor allows you to work on the SQL statements and Visual Basic before plugging it into Access.
With the string of text now pasted into your text-editor, remove the ; from the end of the line, and add WHERE. The reason: in SQL language, the ; character means that the statement is at the end. In this search form, there will be more added to that line programatically. If you do this with the semi-colon not removed, it will generate an error message. Also, the WHERE statement means that criteria narrowing the scope of the "query" are about to be used, which of course is the case.
So, the new format for the prior statement would read:
SELECT mytable.* FROM mytable WHERE
Since these statements can become confusing, you should add comment lines for documentation. (These comment lines are actual Visual Basic codes, but since they are comment lines they don't execute; they exist so that Visual Basic programmers are able to track what's going on within the code.) .Add a comment line just above this string of text, which looks much like the following:
' The following statements are the (base) SQL statements, assuming all records should be shown.
Reword this comment line as you wish, just make sure that the apostrophe character is there. That is the character in Visual Basic which tells VB not to execute the line, allowing you to type within that line whatever you wish.
Then, view the query again in "design" view and set the query up to screen out the table based on whatever search criteria you are screening for. If, for example, you want the query to filter records based on last name, then (assuming you have a text box on your search form named "txtLastName" as mentioned in the prior statements) you would tell the query to show records based on that text box. The syntax would look similar to the following:
Forms![frmSearch]![txtLastName]
If you don't know how to make a query screen records based on such criteria, understand that is beyond the scope of this web page. This page assumes you already know how to do such a thing. If not, email me and I will elaborate.
After setting up this query, again view it under "SQL View." You should notice that, near the end of the code, extra statements appearing after the word WHERE. Copy/paste the characters appearing after the WHERE word into the text-editor. Keep it separate from the other statements pasted into the text-editor. Again, you will notice a ; at the end. Again, remove it. Add the word AND to the end of this statement. Also, at the BEGINNING of this string of text, add a leading space. You will need this leading space. (Make sure it is only ONE space.) When done, it should look somewhat like this:
(((mytable.lastname)=[Forms]![frmSearch]![txtLastName])) AND
Insert a comment line above this string of text as well, and describe in this comment line what search criteria this string of text performs. Again, the comment line would look much like this:
' Search By Last Name Entered
Again, re-word this comment line as you see it, just make sure the APOSTROPHE character is at the beginning of the line.
Repeat these steps for every search criteria you are going to search for. The steps are repeated in a summary below (of course, they assume you already have the "SELECT mytable*. from mytable WHERE" part already done):
***Add a text box to your frmSearch search form and name it something constructive; e.g., "txtLastName"Now, we're at the really crucial stage--inserting the code into the Search button which will actually perform the search. Much of the code was just created when you performed the steps with the query of creating the SQL. You've copied and pasted that code into the text editor. Now it's time to assemble it to make it ready for use.
The 1st part of the code just sets things up--in the code that follows, you will see certain bits of code not explained here. I will explain them as we go along.
|
To make it easier to read, I devised what I hope is a helpful color scheme. Standard code is this color (blue); this is code you can pretty much copy & paste as is, comment lines are green (as they are in Access, in fact), and most importantly of all--the portions of the code that will vary depending on your situation (names, especially) are colored in red. |
Let's get started:
Private Sub cmdSearch_Click() ' larryharrisonjr@dbases.net On Error GoTo Err_search_button_Click Dim sSQL As String, stLen As Integer, stCaption As String sSQL = "SELECT mytable.* FROM mytable WHERE" stCaption = "Search Results. "
The Private Sub cmdSearch_Click() code will be created when you go to enter the code in the 1st place. It's included here simply for purposes of being complete.
The On Error GoTo Err_search_button_Click portion is a part called "error trapping;" in the event there is an error in the program, you don't want your users getting error messages that are more cryptic than they need to be. This helps do this.
Dim sSQL As String, stLen As Integer, stCaption As String sets up variables which will be used for performing these operations. sSQL will hold the SQL statements you just completed. stLen is used for manipulation of this string in the code to make it have proper syntax, and stCaption will make your "browse form" show a friendly heading when you perform the search; it will cause it to say something like "You searched for POs dated 01-22-2003. Here are the results."
We are now at a VERY important part of the code. The statement sSQL = "SELECT mytable.* FROM mytable WHERE ". This is where you would go to your text editor and look for the "base" SQL statements--the one you 1st created when the query you had designed contained no search criteria. So, if the statement you had created read SELECT mytable.* FROM mytable WHERE, you would type it in as (I'm repeating the line again):
sSQL = "SELECT mytable.* FROM mytable WHERE"
This is a VERY important step. Up until now, the code you saw was pretty much "cooker cutter" or "boilerplate;" you could just paste it in as is in the steps coming up. But this part, you need to modify based on what you created earlier.
stCaption = "Search Results. ". This line of code starts the process of making the user-friendly captions that will appear when the user performs their search.This is where it gets interesting. This is where you really have to pay close attention. Here, all that code you generated with the query that you pasted into your text editor will really come into action.
Some sample code:
If Not IsNull(Me.txtLastName) Then ' Search By Last Name sSQL = sSQL & " (((mytable.last_name)=[Forms]![frmSearch]![txtLastName])) AND" stCaption = stCaption & "Last Name = " & Me.txtLastName & " " End If
If Not IsNull(Me.txtLastName) Then. This line of code checks to see if the user entered a value in the box txtLastName. If they did, then a search by this criteria needs to be performed; otherwise, it needs to move along. As I think is obvious, you would substitute in your own names where apprproiate. In this case, I'm assuming you are searching by last name and that the text box on the search form is named "txtLastName."
' Search By Last Name. This is the comment line associated with this particular search. You should have many such lines in your text editor. Simply match up the proper comment with the proper search.
Once again, we're at a really crucial point here. sSQL = sSQL & " (((mytable.last_name)=[Forms]![frmSearch]![txtLastName])) AND". This is where you would, again, go to your text editor and grab the code--one line at a time--you created from the query earlier. Again, here I am assuming a table name of mytable and I am assuming the field being searched in this table is called last_name, and that the box on the search form matched up with this is called txtLastName. As the color-coding suggested, the sSQL = sSQL & portion is cooker-coooker; the part that varies is the part inside the quotes. Basically copy & paste the relevant portion of the SQL between the quotes, and you've got it.
stCaption = stCaption & "Last Name = " & Me.txtLastName & " ". This sets it up so that when you view the search results, a friendly caption will appear telling the user the results of their search. Again, modify the names here where necessary; I'm assuming the box on the search form is called txtLastName. (Don't leave out the Me. portion.)
End If. The end of the portion of the code that checks for a value in (in this case) last name. Standard, cookie-cooker, boilerplate type of code you can simply copy & paste.
You repeat these steps for every criteria being searched for. Simply copy & paste the code and replace the parts that need to be replaced with what you assembled in the text editor as I explained.. (Again, for more clarifications, email me.)
The AND portions of the SQL are what enable it to search by more than 1 criteria at the time; eg., search by state AND by city, for example.
Private Sub cmdSearch_Click() On Error GoTo Err_search_button_Click Dim sSQL As String, stCaption As String, stLen As Integer, sSQLOriginal As String stCaption = "Search Results. " sSQL = "SELECT tblMain.* FROM tblMain WHERE" sSQLOriginal = sSQL ' Search by Representative If Not IsNull(Me.txtRepresentative) Then sSQL = sSQL & " (((tblMain.assigned_representative)=[Forms]![frmSearch]![txtRepresentative])) AND" stCaption = stCaption & "Representative = " & Me.txtRepresentative & ". " End If ' Search by Date of Report If Not IsNull(Me.txtDateofReport) Then sSQL = sSQL & " (((tblMain.date_of_report)=[Forms]![frmSearch]![txtDateofReport])) AND" stCaption = stCaption & "Date of Report = " & Me.txtDateofReport & ". " End If ' Search by Drop Date If Not IsNull(Me.txtDropDate) Then sSQL = sSQL & " (((tblMain.tep_drop_date)=[Forms]![frmSearch]![txtDropDate])) AND" stCaption = stCaption & "Drop Date = " & Me.txtDropDate & ". " End If ' Search by City (Tucson/Sierra Vista) If Not IsNull(Me.cbox_city) Then sSQL = sSQL & " (((tblMain.city)=[Forms]![frmSearch]![cbox_city]));" stCaption = stCaption & "City = " & Me.cbox_city End If ' Search by Date Entered If Not IsNull(Me.txtDateEntered) Then sSQL = sSQL & " (((tblMain.date_entered)=[Forms]![frmSearch]![txtDateEntered])) AND" stCaption = stCaption & "Date Entered = " & Me.txtDateEntered End If ' Search by Entered By If Not IsNull(Me.txtEnteredBy) Then sSQL = sSQL & " (((tblMain.entered_by)=[Forms]![frmSearch]![txtEnteredBy])) AND" stCaption = stCaption & "Entered By = " & Me.txtEnteredBy End If
That takes care of the real "meat" of the code which analyzes every possible field and searches by each one of them, additively (not exclusively). The remaining portions here deal with other final issues to resolve to make it work.
As was mentioned before, SQL statements can't end in AND and since every sSQL = sSQL & ... statement ended with AND, you have to remove the last one. Why does every SQL statement end with AND? Because, remember, AND is what allows it to search by more than 1 criteria at the time. The AND has to be there for that so that in case other search criteria is selected the property SQL statement for performing the search is built. But since the statement can't end in AND, the following bit of code takes care of that at the end of the process. It's easier to do it that way, one time at the end, rather than trying to keep track of it with every possible search scenario.
The code:
' The following "IF...then" statement detects for "AND" at the end of the SQL statements ' and removes it if it exists. This prevents a "syntax error." If Right(sSQL, 3) = "AND" Then stLen = Len(sSQL) sSQL = Left(sSQL, stLen - 4) End If
There's still a bit more left to do. We have to finish off error-trapping, flash an error message in case the user fails to enter search criteria, open the "browse" form and show the search results. Here goes:
' Prompt user if they clicked SEARCH but didn't enter search criteria If sSQL = sSQLOriginal Then MsgBox "You need to enter search criteria", vbCritical, "Search Failed" End End If
This portion prompts the user if they click Search but didn't enter search criteria. The way it can tell: the contents of sSQL didn't change, since sSQLOriginal was set equal to it prior to this section of the code. As the color coding probably makes obvious, you can copy & paste this code literally. (The only reason those certain parts are in red is because you can change them to what you wish yet the code will still work; you could substitute "Search Failed" with "Oops," for example--and the code wouldn't crash.)
Now, we need to open up the "browse form" and show the search results.
DoCmd.OpenForm "frmBrowseRecords" Forms![frmBrowseRecords].Form.RecordSource = sSQL Forms![frmBrowseRecords].Form.Caption = stCaption
The DoCmd.OpenForm line opens up the "browse form" you created. Simply substitute in the name you used in place of frmBrowseRecords (if you didn't use that name). The Form.RecordSource causes the "browse" form to show only the records that matched your search, rather than all the records as it normally does. The Form.Caption causes the friendly "Search Results." prompt to show up in the caption (top portion) of the "browse" form.
Also, we need to finish error-trapping. This really has nothing to do with the search form part; it's just a good practice to do with Access. The process flashes a friendly error message if something is wrong with the code, freaking end-users out somewhat less.
The code:
Exit_search_button_Click: Exit Sub Err_search_button_Click: ' This routine handles any errors with a custom message MsgBox Err.Description, vbCritical, "Report this Error to the database designer" Resume Exit_del_rec_button_Click End Sub
In this example, here is the code, without the color-coding, complete in its entirity, that will be pasted into the search form's button, the "on click" event procedure (the comments are green, but then in Access they'll be green anyway):
Private Sub cmdSearch_Click() On Error GoTo Err_search_button_Click Dim sSQL As String, stCaption As String, stLen As Integer, sSQLOriginal As String stCaption = "Search Results. " sSQL = "SELECT tblMain.* FROM tblMain WHERE" sSQLOriginal = sSQL ' Search by Representative If Not IsNull(Me.txtRepresentative) Then sSQL = sSQL & " (((tblMain.assigned_representative)=[Forms]![frmSearch]![txtRepresentative])) AND" stCaption = stCaption & "Representative = " & Me.txtRepresentative & ". " End If ' Search by Date of Report If Not IsNull(Me.txtDateofReport) Then sSQL = sSQL & " (((tblMain.date_of_report)=[Forms]![frmSearch]![txtDateofReport])) AND" stCaption = stCaption & "Date of Report = " & Me.txtDateofReport & ". " End If ' Search by Drop Date If Not IsNull(Me.txtDropDate) Then sSQL = sSQL & " (((tblMain.tep_drop_date)=[Forms]![frmSearch]![txtDropDate])) AND" stCaption = stCaption & "Drop Date = " & Me.txtDropDate & ". " End If ' Search by City (Tucson/Sierra Vista) If Not IsNull(Me.cbox_city) Then sSQL = sSQL & " (((tblMain.city)=[Forms]![frmSearch]![cbox_city]));" stCaption = stCaption & "City = " & Me.cbox_city End If ' Search by Date Entered If Not IsNull(Me.txtDateEntered) Then sSQL = sSQL & " (((tblMain.date_entered)=[Forms]![frmSearch]![txtDateEntered])) AND" stCaption = stCaption & "Date Entered = " & Me.txtDateEntered End If ' Search by Entered By If Not IsNull(Me.txtEnteredBy) Then sSQL = sSQL & " (((tblMain.entered_by)=[Forms]![frmSearch]![txtEnteredBy])) AND" stCaption = stCaption & "Entered By = " & Me.txtEnteredBy End If ' The following "IF...then" statement detects for "AND" at the end of the SQL statements ' and removes it if it exists. This prevents a "syntax error." If Right(sSQL, 3) = "AND" Then stLen = Len(sSQL) sSQL = Left(sSQL, stLen - 4) End If ' Prompt user if they clicked SEARCH but didn't enter search criteria If sSQL = sSQLOriginal Then MsgBox "You need to enter search criteria", vbCritical, "Search Failed" End End If DoCmd.OpenForm "frmBrowseRecords" Forms![frmBrowseRecords].Form.RecordSource = sSQL Forms![frmBrowseRecords].Form.Caption = stCaption Exit_search_button_Click: Exit Sub Err_search_button_Click: ' This routine handles any errors with a custom message MsgBox Err.Description, vbCritical, "Report this Error to the database designer" Resume Exit_del_rec_button_Click End Sub |
Note, very important: the 1st line, Private Sub cmdSearch_Click() and last line End Sub will likely already be included as defaults when you 1st setup the command button. Make sure they don't appear twice unnecessarily; it WILL crash the code.
The only remaining step at this point is simply to delete the query qrySearchRecords as you no longer need it (although I do sometimes keep it if I'm constantly changing the search form, or I'm not done) and to program the search form's CLEAR button to clear all the contents of your search form AND to go to the 1st control in your search form.
To program the clear button, insert the following code into the clear button's "on click" event procedure (almost verbatim):
Dim cntrl As Control For Each cntrl In Me.Controls If cntrl.ControlType = acTextBox Or cntrl.ControlType = acComboBox Then cntrl.Value = Null End If Next cntrl DoCmd.GoToControl "txtLastName"
The only part of that code that needs to be changed is txtLastName which should be whatever the 1st text box on your search form's name is.
Again, I now have a demo database for this which you can download. They are in ZIP format and are avaialable in 97 and 2000/XP versions.