(.OrderBy, .Refresh) in subform

[eocheng]

Hi,

I have a subform that displays the contents of a query in datasheet view. Now I know that I can order the column by right clicking, but I'm trying to implement an option box that will order all the information by whatever predefined ordering I desire.

I had a working model of it

code was as follows
<code>
Private Sub optOrderBy_AfterUpdate()
On Error GoTo Err_optOrderBy_AfterUpdate
If optOrderBy.Value = 1 Then
'order records by employee then project number
[Forms]![frmProgramEdit]![sbfrmProgramEmployee].Form.OrderBy = "Employee, ProjectNo, MonthNumber"
ElseIf optOrderBy.Value = 2 Then
'order records by month then employee
[Forms]![frmProgramEdit]![sbfrmProgramEmployee].Form.OrderBy = "MonthNumber, ProjectNo, Employee"
ElseIf optOrderBy.Value = 3 Then
'order records by project number then employee
[Forms]![frmProgramEdit]![sbfrmProgramEmployee].Form.OrderBy = "ProjectNo, Employee, MonthNumber"
End If
'"DoEvents" will also do the trick. This line ensures that .OrderBy is flushed out to master form
[Forms]![frmProgramEdit]![sbfrmProgramEmployee].Form.Refresh
Exit_optOrderBy_AfterUpdate:
Exit Sub

'error handler
Err_optOrderBy_AfterUpdate:
MsgBox "The following error occurred: " & Err.Description, vbCritical, "error number: " & Err.Number
End Sub
</code>

Then I changed some other things on the form. On the working version, I had 2 subforms. In the new version of it I have only this one subform. I've matched each property so that it is identical with the working model, but can't figure out why it doesn't work.

I know that it is changing the orderBy property, because when I run it and view the OrderBy property of the form, it updates according. As well, I can also see the subform refresh, but nothing gets reordered.

I have also tried changing the property manually in design view of the sbfrm by itself and then saving it, then view it. That also does nothing.

I'm wondering if anyone can offer any insight.

Thank you so much.

Regards,
Eric

 

[PHV]

Have you tried to set the form's property OrderByOn to True ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[eocheng]

Hi,

Thank you for the quick response. I can't seem to access that property. But I've put it at the beginning of the surboutine, but still not reordering.

 

[PHV]

Have you tried to add something like this before the Exit Sub:
Forms!frmProgramEdit!sbfrmProgramEmployee.Form.OrderByOn = True
Forms!frmProgramEdit!sbfrmProgramEmployee.Form.ReQuery

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[eocheng]

brilliant. Tat works wonderfully,

Thanks,
e