Subform Help

[Guest_imported]

I have a form with an option group and a subform on it. What I want to do is when the user selects a different option it re-sorts (desending) the subform. Hopefully this is possible. Please let me know if there needs to be more info in here to help out. I am using Access 2000. Thanks in advance..

jnp3547

 

[RickSpr]

To change the order in the subform, you can change its OrderBy property, while also setting its OrderByOn property to True. The OrderBy property takes a string consisting of a list of fields to sort on, from major to minor, separated by commas. To sort in descending sequence, you need to insert the DESC keyword following each field name (separated with a space, not a comma). To return to ascending sequence, you can simply set the OrderByOn property to False again (I'm assuming the subform's record source specifies ascending order).

Since this is a subform, and you want to change it from the main form, you'll need to use the following syntax to reference it. (I'm using sfmMySubform as the subform control name on the main form.)
Me!sfmMySubform.Form.OrderBy = "field1 DESC, field2 DESC, ..."
Me!sfmMySubform.Form.OrderByOn = True Rick Sprague

 

[jnp102]

Thanks for the reply but I couldn't get it to work. I wasn't sure where to put the syntax you wrote. Also, I thought that i would have to reference the option group some way because that is where the subform will be triggered to sort. Thanks.

 

[RickSpr]

Sorry, I took too much for granted. I thought you would already be famililar with event procedures and VBA coding.

For illustration, let's say the option group control on the main form is named fraSortOrder, and contains two option buttons for ascending and descending order, with their Option Value properties set to 1 and 2 respectively. I'm again assuming the subform control is named sfmMySubform.

I'll also have to know the names of field(s) in the subform's record source that you want to sort by, so I'll assume you're sorting on two fields, OrderDate and OrderNbr.

What you need to do is, when an option button is chosen, use the AfterUpdate event of the option group to modify the OrderBy property of the subform. (Note that the subform is not the same thing as the subform control. The subform is the actual form that's listed on the Forms tab in the database window; the subform control is a container for the subform on the main form. I hope that's clear; it's something that Access beginners frequently misunderstand.)

The following code illustrates how you do this:

    Private Sub fraSortOrder_AfterUpdate()
        Dim str As String

        If Me!fraSortOrder = 1 Then
            str = "OrderDate ASC, OrderNbr ASC"
        Else
            str = "OrderDate DESC, OrderNbr DESC"
        End If
        Me!sfmMySubform.Form.OrderBy = str
        Me!sfmMySubform.Form.OrderByOn = True
    End Sub

To create this procedure, first open the main form in design view and select the option group (frame) control. Then scroll down the Properties sheet until you see the After Update event property. Set it to "[Event Procedure]", and then click the Builder ("...&quot button to its right. This will open a code window with the Private Sub and End Sub statements already entered; just type in the rest of the code, substituting the actual names of your option group control, subform control, and sort fields. That should do it! Rick Sprague

 

[Guest_imported]

Thanks for the response, with a few minor adjustments it works perfectly! Thanks again for the help