Using Databases

Getting Input from the User

This part of the tutorial shows how to allow the user of your publication to input a piece of text (or "string" of characters) and then display any records in the database containing that string.

Please find below a step-by-step procedure for modifying the publication you created in the first tutorial to allow searching:

1. Open the publication you created in the first tutorial
2. Right-click on the page and select Edit Actions
3. Delete the On Show trigger and Database Query action
4. Click OK to return to the editor
5. Now add a Text Input box to the page
6. Open the properties of the Text Input box, select the Text Input tab and click on the "New" button
7. Create a new publication variable named SEARCH
8. Click OK twice to return to the editor

Creating the Search

1. Add a button next to the Text Input box
2. In the Button Actions, click on the Actions tab, then the Database tab and select Database Query. This should open a Database Query tab
3. Click on the Select button next to the DSN drop-down menu
4. In the Select Data Source window, click New
5. Select Microsoft Access Driver (*.mdb) from the list of drivers and click Next
6. You can name this connection what ever you choose. Make a note of the name given and click Next
7. You will now be shown a summary of the changes made, just click OK to confirm
8. You will now need to specify which database to use. Click on Select and navigate to you database. Once selected, click OK
9. Ensure that the DSN you have just created is highlighted in the list and select OK
10. You will now return to the Database Query tab, but the DSN will be set to FILEDSN=.dsn
11. Click on SQL Wizard and remove the tick from the Select Data for Updating/Deleting (you can experiment with this later - you will need this option ON if you wish to run an Update Record or Delete Record action) and click Next
12. From the table drop-down list, select the table in which your required data resides, ensure that all of the field names are ticked, then click Next
13. This page allows you to apply SQL operators to filter the data. Set the "Where Field" setting to the first field in your database (NAME)
14. Select IS LIKE as the operator
15. Set the Compare To option to "Variable", then select SEARCH from the drop-down menu
16. Click Next
17. Select OR and click Next
18. Change the Where Field to AGE, set the operator to IS LIKE and set the variable SEARCH as the Compare To option. Click Next, select OR, and click Next
19. Repeat this procedure once more for the OCCUPATION field
20. This page allows you to sort the results by field. Leave the Field setting as (None) and click Finish
21. You will now return to the Database Query tab, but the Query name will read Query2
22. At the bottom of this window is a grid showing all of the variables assigned to your Field names. At present there are no variables assigned
23. Click on the box next to NAME (which currently reads None) and an Insert Variable window will open. Click New and type in NAME
24. Repeat Step 31 for your other Field names, calling their associated variables AGE and OCCUPATION respectively
25. Click Apply then OK to return to the main editor window
26. Preview the publication
27. You should now only be able to see the text input box and the button. The three text boxes should not be visible
28. Type a search term into the text input box and click the button. Opus will search your database for like values and display the first matching record within the three text boxes

You can add navigate to the previous and next records by adding two buttons to the page, then applying a Previous Record action to one button and a Next Record action to the other. When applying these actions, you will be asked to which query these actions should refer - simply select the same query that was used in your original Database Query action.