Friday 20 May 2011

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

16 comments:

  1. Nice work........
    helps a lot
    thank u

    ReplyDelete
  2. Thanks KSAK - glad you found it helpful.

    ReplyDelete
  3. Awesome! Exactly what i was looking for.
    Thank you so much for this

    ReplyDelete
  4. I can't get this to work. When I click on the record field a box pops asking "Enter Parameter Variable". Clicking OK opens a blank form. Entering a value also gives a blank form. The record I want to open is the same as on the datasheet just with more field displayed. The data in the field is a custom order number in the following format AAS12-001 where AAS is the system, 12 is the year and 001 is the record number. Any ideas why this won't work for me.

    ReplyDelete
    Replies
    1. Hi Ridgetech

      It sounds like your custom order number is of the text data type. As such you will need to modify the varWhereClause variable in the code snippet to:

      varWhereClause = "ID = '" & Me!productId & "'"

      This contains the necessary quotation marks that enclose the search value. This is important if it is a text data type as opposed to a number data type. My example code was intended for the latter type.

      Delete
  5. Thanks Justin. It works perfectly now and passing the text value was the issue. Besides being an excellent learning experience, my frustration level just went down 100% because I could not figure out what I was doing wrong. Thanks again.

    ReplyDelete
  6. And if we want to add new record in the subform with the same way?

    ReplyDelete
    Replies
    1. Hi

      The simple answer is that you wouldn't use the subform to add a new record. You would click the link to open that same record in its own form. The reason we have gone to the trouble of creating the subform link is to enhance the user friendliness of the database application. Subforms are great for displaying summary information, but not quite as good for entering data (especially when there are many fields to fill in).

      Delete
  7. All of this is working properly, however, when the form opens (what would be your productid form) it shows that the linked record is the only one available, I want to still be able to navigate through all of the records in the table.

    ReplyDelete
    Replies
    1. It appears that it is because there is a filter in place. I noticed that the open form command has a filtername argument that is left blank, how do I remove the filter.

      Delete
    2. You just need to click the FILTERED button at the bottom of the form (alongside the navigation buttons). This removes the filter manually. The user may then navigate through all the other records. Alternatively you could set up a command button which runs the following line of code:

      Me.FilterOn = False

      This removes the filter. However, the filter does need to be on initially, otherwise it will not display the original record that the user wanted to view.

      Delete
  8. This worked great at first, but then my form stopped opening. I've been over it a lot and I can't imagine what I changed.

    Here is my code:

    Private Sub btnYouthView_Click()

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

    End Sub

    ReplyDelete
  9. nevermind....Access security disabled the buttons...fixed now

    ReplyDelete
  10. How would you apply this if you wanted to open another form with a subform in it. I want the subform of the 2nd form to only show details of the record I clicked on in the first subform.

    ReplyDelete
    Replies
    1. and both fields are text data fields. They are the same field just on 2 different subforms.

      Delete