How to Create a Parameter Query

Parameter Queries are a great way to add interactivity to your Access Database.  Instead of entering a fixed Query Criteria in the Query Design Grid, we enter a question which prompts the user to enter the criteria in a dialogue box when the query is run. In this exercise we are going to create a Parameter Query to produce a list of surnames based on a value entered by the user.

1. Create a table with three columns - ID, Firstname, and Surname.
2. Enter a some test data into the table. Between five and ten random names should do for this purpose.  You might like to give some of the records the same surname.
3. Create a new query by clicking the QUERY DESIGN icon.  This is located in the OTHERS group of the Access CREATE ribbon. 
4. Once the Query Design Window opens, select the Table you just created from the SHOW TABLE dialogue box.
5. Then select the three fields contained in this table. The quickest way of doing this is to double click each field name listed in the table box in the upper section of the screen.  These will then appear as field headings in the QUERY DESIGN GRID.
6. The last stage is to enter the user prompt in the criteria row of the Surname column.  As you may remember, this has to be enclosed within square brackets.  Our prompt is going to be [Enter Surname].

Figure 1: A Parameter Query created in the Query Design Grid.

Then when you run the query you get this message:

Figure 2: Enter Parameter Value Dialogue Box.

All you need to do now is enter one of the surnames you added to your test data in stage 2, and click OK.  Then any record with that surname is produced in your query results.

Another great thing about Parameter Queries that you might like to try, is using them as the record source for a form.  When the form is loaded the query runs asking you to enter a parameter value as before.  This time, however, once you click OK, the form opens displaying a set of records based on the value entered.

Using a Subform Link to Open a form at a Specific Record

Imagine a scenario where you are looking at a record displayed on a form. The form contains a subform displaying a number of related records summarized in datasheet view.  This tip shows how we can open a new form at a specific record when we click a particular link on the subform.  As we shall see, it is a convenient way to navigate between forms when there is an underlying Many to Many Relationship structure in place.

To do this we are going to use the example of a Customer Order form.  The main section of the form displays the Customer Order, and the Subform displays the Order Details stored in the junction table.  When a user clicks the product link in the Order Details subform, the Product Form opens at the record for that particular product.

Figure 1: The Orders Form.  Clicking the product link in the Order Details Subform
opens the Product Form (see Figure 2 below) at that particular record.

Figure 2: The Products Form displaying the record specified in the Subform in Figure 1 above.

When the user clicks the link, the textbox's On_Click Event fires, triggering a short VBA subroutine.  This is the section of code responsible for opening the Product Form at the relevant record:

Dim varWhereClause As String
varWhereClause = "ID = " & Me!productId
DoCmd.OpenForm "frmProducts", , , varWhereClause

It begins by defining a string variable to hold an SQL Where Clause.  The next line sets the string variable.  Notice how the end of the string references the productId field of the subform field that has been clicked.  The final line uses the DoCmd OpenForm Statement to open the Products Form.  The varWhereClause string variable is used as the statement's WhereCondition, thereby opening the form at that particular product record.


Here is the full procedure for putting all this in place:

1. Create the main Customer Order Form (with the Order Details Subform).  
2. Create the Products Form.
3. Next you need to go back and edit the Order Details Subform.
   
   
4. Click on the ProductId field, and then open the PROPERTIES SHEET. 
5. Under the FORMAT tab, change the IS_HYPERLINK property to YES.  Then change the DISPLAY_AS_HYPERLINK property to SCREEN_ONLY.  This changes the appearance of the ProductId field to a hyperlink style.
6. Under the EVENT tab of the PROPERTIES SHEET, select the ON_CLICK cell in the grid.  Then click the three dot symbol on it's right to open the CHOOSE BUILDER dialogue box.  
7. Select CODE BUILDER from the list, and click OK to open the VBA Editor.
8. Past the code listed below in between the lines, "Private Sub ... " and "Exit Sub", in the VBA Editor.

On Error GoTo myError
Dim varWhereClause As String
varWhereClause = "ID = " &  Me!productId
DoCmd.OpenForm "frmProducts", , , varWhereClause
leave:
Exit Sub
myError:
MsgBox Error$
Resume Next

A Gentle Introduction to Access SQL

SQL is a language used by database applications such as Microsoft Office Access, SQL Server and MySQL.  Although people using Access at a basic level do not need to know much, if anything, about the SQL language, the deeper we go into database design, the more important it becomes.  This post is intended to be a 'gentle' introduction to the subject!

SQL stands for Structured Query Language.  As the name implies, it is used in the creation of queries.  Whenever we create a Query using the Access Query Design Grid, Access converts the information we provide into SQL Code. We can view and edit this code by selecting SQL VIEW from the RESULTS group of the QUERY DESIGN ribbon. But why go to the trouble of learning SQL when we can just use the Design Grid?  There are many reasons for this.  For advanced users there are things which can be done in SQL that are too complex for the Design Grid to handle. In addition to this, SQL is also used within the Access Visual Basic programming langage thereby allowing us to automate queries and use variables in query criteria. (See my post on Automating an Update Query)

However, SQL is also used in other areas of Access such as the properties windows where we can define Record Sources for forms, Row Sources for Combo Box controls, and criteria for filters to name just a few.  As such, even at a relatively basic level, it is good to have a general awareness of SQL, and maybe some knowledge about how to create and edit simple SQL Statements.

Lets take a look at a simple SQL Statement used to query a database table.  In this example, the table is called tblCustomer.  We are going to select three fields from this table - FIRSTNAME, SURNAME, and CITY. The criteria we are going to use ='bolton' under the CITY field.  The query is designed to show a three column list of records where the value contained in the CITY Column is 'Bolton'.  Here is the SQL Code:

SELECT FirstName, Surname, City
FROM tblCustomer
WHERE City="bolton"



NB: When you look at code using Access SQL View you find that the syntax is slightly different.  Extra brackets are put around the expression following the Where clause; and the field names after the Select clause are written with a table name   [full stop] field name, like this - tblCustomer.FirstName.  This is how Access codes it own version of SQL.  It is, however, perfectly capable of reading the simpler version printed above.  Just be aware that Access will code statements slightly differently for its own purposes.  

As you can see there are three line to this SQL Statement.  The capitalised words at the beginning of each line are SQL keywords (or 'Clauses') and the small case words following relate to database fields, tables and criteria.  The Statement begins with the SELECT clause.  This is saying we are going to select the following fields (ie FirstName, SurName, and City) in this Statement.  In the next line we have the FROM clause.  This is saying that the fields selected above are taken from the following table (ie tblCustomer).  The final line is the WHERE clause.  This is saying that we are only interested in records where the following expression is true (ie city=Bolton). If we had created this Query using the Query Design Grid, it would have looked like this:

Figure 1: The equivalent query created using the Query Design Grid.

One thing that may strike you when you compare the SQL Statement with the Query Design Grid, is how simple and brief the Code actually is. A simple application of SQL is to enter a statement like that above for a form's RECORD SOURCE, as opposed to using a standard query created using the Grid.  This saves us having to create a seperate query, and minimises the number of Objects appearing in our database window. Just enter the whole SQL statement, as a single line, directly into the RECORD SOURCE cell of the PROPERTIES WINDOW.

Figure 2: An SQL Statement used as a forms RECORD SOURCE.

This has been a brief and simple introduction to Access SQL.  There is, of course, so much more to the subject. I intend to post more articles some time in the future which go into more detail.

Using a Combo Box to Search for a Record

In this tip we are going to look at how we can use a Combo Box control on a form to search for a particular record.  It works by clicking an item from the Combo Box's drop down list.  This activates a Visual Basic for Applications (VBA) Procedure using the forms ON CHANGE Event. Once the code executes, the form seamlessly displays the selected record.

Figure 1: Products Form with a Combo Box Search facility.  Selecting an item
searches for the relevant records and displays in on the form.

Lets begin by creating our Combo Box from scratch.

Create Combo Box from Scratch

1. Open your form in Design View.
2. Ensure the Wizard icon is deactivated.  If it is, just click the Wizard Icon so that it is no longer highlighted.
3. Click the Combo Box Icon.
4. When the Mouse Pointer changes to the Add Combo Box Symbol, click an area on the Form Design Grid where you would like it to go.

You now have a empty Combo Box Control on your form.  If you wish, you can resize the box and add some text to the label.  The next task is for us to enter the ROW SOURCE property.  This is the source of the data which will appear in the Combo Box's drop down list (take care not to confuse this property with the combo box CONTROL SOURCE, as we are going to keep our control unbound).

The Row Source Property

1. Select the Combo Box Control by clicking it with the mouse.
2. Click the PROPERTY SHEET icon. This brings up the Combo Box's PROPERTY SHEET.
   
3. Select the DATA tab.
4. Enter the ROW SOURCE property.

When you  enter the Row Source property, you can select a Query/Table from the Drop down list, or write an SQL Statement directly onto the property grid. Another option is to click the three dots symbol at the end of the row to bring up the QUERY DESIGN Window. Then it is just a case of creating your query.

Figure 2: The Combo Box PROPERTY sheet for
the form in figure 1.

In our example shown in figure 1, I used tblProducts as the ROW SOURCE for the Combo Box. This, of course, is the same table that is used as the RECORD SOURCE for the main form.  I set the COLUMN COUNT PROPERTY (from the FORMAT tab) to 2, so that we get two columns in the drop down list - that is, the ID field, and the ItemName field.  I also set the BOUND COLUMN property (from the DATA TAB) to 1, so our Combo Box stores the value from the first column (ie the ID field) when the user makes a selection from the drop down list.

The next stage is to enter the VBA code.  We are going to use the Combo Box's ON CHANGE event.  This

event triggers as soon as the user selects an item from the drop down list.

Enter VBA Code

1. Select the Combo Box Control by clicking it with the mouse.
2. Open the PROPERTIES window.
3. Select the EVENT tab.
4. Select the ON CHANGE event by clicking its row in the grid.
5. Click the three dots symbol on the far right of the row. This opens the CHOOSE BUILDER dialogue box.  
6. Select CODE BUILDER and click the OK button.  This opens the VISUAL BASIC editor.
7. Copy and Paste the code (listed below) between the PRIVATE SUB and END SUB statements.   You may need to edit the FINDFIRST Statement on line 4 (replace ID with the field you are searching for).

On Error GoTo myError
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "ID = " & Me!ctlSearch
Me.Bookmark = rst.Bookmark
leave:
Me!ctlSearch = Null
If Not rst Is Nothing Then Set rst = Nothing
Exit Sub
myError:
MsgBox "Record Not Found"
Resume leave

Figure 4: The VBA Editor

The main section of code works by cloning the forms's record set, which is stored in an object variable called rst. The FINDFIRST method is then used to search the cloned record set for the item selected in the Combo Box by the user.  Once found, the forms Bookmark property is then set to that of the cloned recordset.  This results in the Products Form seamlessly displaying the record selected from the drop down list.