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