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.

Automating an Update Query Using DoCmd.RunSQL

This tip shows us how to create and execute an Update Query using the VBA DoCmd.RunSQL Statement.  We shall use the example of a Products table, and our objective is to update the CostPerUnit field of a particular record matching a set criteria.  To do this the user will open an unbound form to select a product from a combo box, and enter a new updated price in the textbox.  

Above: Unbound Form to select a product from a Combo Box list, and enter its new price.

The code which creates and executes the SQL Statement will run when the Update command button is clicked.  Information entered by the user is referenced by the code (as Me!ctlProduct and Me!txtNewCost) and integrated into the SQL statement.

Above:  This code runs when the users click the Update command button.

The varSQL variable contains the SQL from the Update Query which we are going to run.  This can be built using the Access Query Design Grid, and switching to SQL view to copy the statement.  The two form controls with our user input are built into this variable, and become part of the Statement. The next line uses the DoCmd.RunSQL statement to the run the SQL contained in our string variable.  This will then act like a standard Update Query: the relevant product is found, and the CostPerUnit field is updated to the new price specified by the user.