Creating a Searchable Access Form
As helpful as Caspio's software is in creating an online database, anyone in the litigation support field should be able to create a searchable form in Access on their own. There are many messages boards and YouTube videos available which will show you how to do this. A good one is available from the PC Learning Zone at https://www.youtube.com/watch?v=CTiA_4Me0cI .
After you have a table with data set up like the one I used as an example in last night's tip, you can proceed with creating a form.
1. Go to the Create tab in Access and select 'Form Design'.
2. Choose the text box icon with 'ab' (see the picture below) and create labels and fields for each column of data in your table that you want to be able to search.
3. Enter the field names in the labels on the left and then double-click on the text boxes to open the 'Property Sheet' window, and then enter the names in the first field on the all tab.
4. After saving the form, create a new query design. Choose your table, and then drag down each field from the box in the upper section to the cell for the 'Field' row in the table in the lower section.
5. In order to create a searchable form in which the values you enter would only pull up exact matches in the table, you enter in the row labeled 'Criteria' '=Forms!' and then when prompted first select the name of the form you created followed by another exclamation point and then when prompted select the name of the field which corresponds to that from the table that you put in the Query's lower section's column. The full entry should look like this:
6. It's far more helpful to set up the form so that results are returned when the values you enter match any part of the corresponding fields in the data table. So instead enter:
Like "*" & [Forms]![Form1]![Case Name] & "*"
See the example in Figure 1 below.
7. Right click on the form you created and select design view. Select the command button (the icon with the four's x's pictured below), and double-click on the form. A command button wizard will appear. Choose 'Miscellaneous' from the Categories list and 'Run Query' from the Actions list. Click next and choose the query you created, then select the 'text' radial button to simply make the caption read 'Run Query'. After finishing the command button wizard, save the updated form.
8. Go back into design view for the query and modify the entry shown in step 6 so it is repeated in the 'Criteria' row for the other fields from the data table. As shown below in figures 2 and 3, you should now be able to run searches in more than one field on the form after clicking the 'Run Query' button that you created.