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.|
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:
- Create the main Customer Order Form (with the Order Details Subform).
- Create the Products Form.
- Next you need to go back and edit the Order Details Subform.
- Click on the ProductId field, and then open the PROPERTIES SHEET.
- 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.
- 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.
- Select CODE BUILDER from the list, and click OK to open the VBA Editor.
- 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