Building in an Excel Like Sort feature......

[kcdoell]

I have a form that I want to change the way the subform (continuous) is displaying the info (the sort order). In another words, I want to give the user the ability to see the same information in different sort orders (By Name, Location, Product, etc).

They want me to have it so that they can filter on three of the fields in different orders much like Excel where the user can pick the first field to sort on then choose the second and then the third.

How would I go about building that “Excel” like sort functionality?? ??


Thanks,

Keith.

[kcdoell]

Hello:

I was reading and saw that there was a member who was looking for a similar solution [HTML]http://www.thescripts. com/forum/thread205219.ht ml[/HTML]. In my case scenario, I have three combo boxes with a value list that I created:

cboSort1 = Row Source ("Underwriter"; "Broker";"Insur ed Name";"Policy Type";"Product" ;"GWP";"NWP" )

cboSort2 = Row Source ("Underwriter"; "Broker";"Insur ed Name";"Policy Type";"Product" ;"GWP";"NWP" )

cboSort3 = Row Source ("Underwriter"; "Broker";"Insur ed Name";"Policy Type";"Product" ;"GWP";"NWP" )

The list does not match the field names on my table exactly (example Insured Name on my table “tblAllForecast ” is Insured_Name). How would I apply the code if these control boxes are not pointing to any table but somehow when selected would sort the records on my continuous form?

Any help would be great.

Thanks.

[ADezii]

I have a form that I want to change the way the subform (continuous) is displaying the info (the sort order). In another words, I want to give the user the ability to see the same information in different sort orders (By Name, Location, Product, etc).

They want me to have it so that they can filter on three of the fields in different orders much like Excel where the user can pick the first field to sort on then choose the second and then the third.

How would I go about building that “Excel” like sort functionality?? ??


Thanks,

Keith.

The easiest Method by far is to have 3 pre-defined, valid Queries with specific Sort Orders (qryName, qryLocation, and qryProduct) which could qualify as Record Sources for your Sub-Form. It is now a simple matter to dynamically change the Record Source of the Sub-Form, depending on certain conditions:
[CODE=vb]
Me!<Sub-Form Control>.Form.R ecordSource = "qryName"
OR
Me!<Sub-Form Control>.Form.R ecordSource = "qryLocatio n"
OR
Me!<Sub-Form Control>.Form.R ecordSource = "qryProduct "[/CODE]

[kcdoell]

The easiest Method by far is to have 3 pre-defined, ......on certain conditions:
[CODE=vb]
Me!<Sub-Form Control>.Form.R ecordSource = "qryName"
OR
Me!<Sub-Form Control>.Form.R ecordSource = "qryLocatio n"
OR
Me!<Sub-Form Control>.Form.R ecordSource = "qryProduct "[/CODE]

Dezii:

But would not this method only work independently? I need to build something where they could sort by one parameter then by another then by another (much like Excel does). I was doing a some reading and used this code to give the user the ability to sort on any header title:

[code=vb]

Private Sub LblBroker_Click ()
'When the user clicks on the Broker Header it will sort by that column

Me.OrderBy = "Broker"
Me.OrderByOn = True
End Sub

[/code]

Simple cool stuff that will hold them over but I know they want the "Excel" feel.

If you have any ideas that would be great. Thanks for the reply

Keith.

[ADezii]

Dezii:

But would not this method only work independently? I need to build something where they could sort by one parameter then by another then by another (much like Excel does). I was doing a some reading and used this code to give the user the ability to sort on any header title:

[code=vb]

Private Sub LblBroker_Click ()
'When the user clicks on the Broker Header it will sort by that column

Me.OrderBy = "Broker"
Me.OrderByOn = True
End Sub

[/code]

Simple cool stuff that will hold them over but I know they want the "Excel" feel.

If you have any ideas that would be great. Thanks for the reply

Keith.

How about an Option Group (fraSortOrder) with Name, Location, and Product as the Options. Clicking on either of these 3 Options would then create the corresponding Sort Order on the Sub-Form. The Default Option Values would be 1 (Name), 2 (Location), and 3 (Product):
[CODE=vb]
Private Sub fraSortOrder_Af terUpdate()
Select Case Me![fraSortOrder].Value
Case 1
Me![Sub-Form].Form.OrderBy = "Name"
Case 2
Me![Sub-Form].Form.OrderBy = "Location"
Case 3
Me![Sub-Form].Form.OrderBy = "Product"
Case Else
End Select
Me![Sub-Form].Form.OrderByOn = True
End Sub[/CODE]

[kcdoell]

I am trying to picture how I would apply that. I guess I would need to create an unbound combo box on my form called "fraSortOrd er" then apply the Select case statement in the AfterUpdate event:

[code=vb]
Private Sub fraSortOrder_Af terUpdate()
Select Case Me![fraSortOrder].Value
Case 1
Me![Forecast].Form.OrderBy = "Name"
Case 2
Me![Forecast].Form.OrderBy = "Location"
Case 3
Me![Forecast].Form.OrderBy = "Product"
Case Else
End Select
Me![Forecast].Form.OrderByOn = True
End Sub
[/code]

If that is correct how would I get the default values in the one combo box?? I am thinking that you were going down the road that it can all be done in one.

Thanks for the reply.

Keith.