Solved Option Group, Sorting Form based on selection (1 Viewer)

Chief

Registered User.
Local time
Today, 14:01
Joined
Feb 22, 2012
Messages
156
Hi,
I have a Form that I would like to be able to sort by means of an option group.
I have tried a few different versions of code, but I am out of my depth I think, :)
Form Looks like this
1634856666519.png

One of the select case code I have tried is

Code:
Private Sub FrameSortOptions_Click()
    Select Case Me.FrameSortOptions.Value
        Case 1
            Me.OrderBy = "IFA_Due, JobNumber, CompanyName"
        Case 2
            Me.OrderBy = "IFC_Due, JobNumber, CompanyName"
        Case 3
            Me.OrderBy = "SetOutDue, JobNumber, CompanyName"
        Case 4
            Me.OrderBy = "MachineShop_Due, JobNumber, CompanyName"
        Case 5
            Me.OrderBy = "AssemblyDue, JobNumber, CompanyName"
        Case 6
            Me.OrderBy = "Delivery_Due, JobNumber, CompanyName"
    End Select
                        
End Sub

Pretty sure I am missing something here, Is it that I have not identified what option number is what?

Thank you
 

cheekybuddha

AWF VIP
Local time
Today, 22:01
Joined
Jul 21, 2014
Messages
1,090
With your form in design view, click on each of the option radio buttons.

Look in the property sheet on the 'Data' tab and it will show you the Option Value for each radio button.
 

Chief

Registered User.
Local time
Today, 14:01
Joined
Feb 22, 2012
Messages
156
With your form in design view, click on each of the option radio buttons.

Look in the property sheet on the 'Data' tab and it will show you the Option Value for each radio button.
G'day mate,

Yes I know what value each one is, I'm not sure how to add that into my code.
Can you assist there?

thanks
 

cheekybuddha

AWF VIP
Local time
Today, 22:01
Joined
Jul 21, 2014
Messages
1,090
Or maybe you've forgotten to just turn on the ordering:
Code:
Private Sub FrameSortOptions_Click()
    Select Case Me.FrameSortOptions.Value
        Case 1
            Me.OrderBy = "IFA_Due, JobNumber, CompanyName"
        Case 2
            Me.OrderBy = "IFC_Due, JobNumber, CompanyName"
        Case 3
            Me.OrderBy = "SetOutDue, JobNumber, CompanyName"
        Case 4
            Me.OrderBy = "MachineShop_Due, JobNumber, CompanyName"
        Case 5
            Me.OrderBy = "AssemblyDue, JobNumber, CompanyName"
        Case 6
            Me.OrderBy = "Delivery_Due, JobNumber, CompanyName"
    End Select
    Me.OrderByOn = True    ' <-- add this line
End Sub
 

Chief

Registered User.
Local time
Today, 14:01
Joined
Feb 22, 2012
Messages
156
Or maybe you've forgotten to just turn on the ordering:
Code:
Private Sub FrameSortOptions_Click()
    Select Case Me.FrameSortOptions.Value
        Case 1
            Me.OrderBy = "IFA_Due, JobNumber, CompanyName"
        Case 2
            Me.OrderBy = "IFC_Due, JobNumber, CompanyName"
        Case 3
            Me.OrderBy = "SetOutDue, JobNumber, CompanyName"
        Case 4
            Me.OrderBy = "MachineShop_Due, JobNumber, CompanyName"
        Case 5
            Me.OrderBy = "AssemblyDue, JobNumber, CompanyName"
        Case 6
            Me.OrderBy = "Delivery_Due, JobNumber, CompanyName"
    End Select
    Me.OrderByOn = True    ' <-- add this line
End Sub

Cool,
Things are flashing now so I think nearly there.
It doesn't appear to know which option button is which, so its not sorting or its sorting incorrectly.

Do I need to state what each Case is at the start of the code?

thanks
 

cheekybuddha

AWF VIP
Local time
Today, 22:01
Joined
Jul 21, 2014
Messages
1,090
Yes I know what value each one is, I'm not sure how to add that into my code.
Can you assist there?
You must match each value against the Case statement values.

So, if the options' radio buttons have these values:
IFA_Due = 1
IFC_Due = 2
SetOutDue = 3
MachineShop_Due = 4
AssemblyDue = 5
Delivery_Due = 6
... then your case statements are correct.

Have you got your field names all correct? SetOutDue and AssemblyDue don't have underscores whereas all the rest do. These must match your table field names exactly.
 

Chief

Registered User.
Local time
Today, 14:01
Joined
Feb 22, 2012
Messages
156
You must match each value against the Case statement values.

So, if the options' radio buttons have these values:
IFA_Due = 1
IFC_Due = 2
SetOutDue = 3
MachineShop_Due = 4
AssemblyDue = 5
Delivery_Due = 6
... then your case statements are correct.

Have you got your field names all correct? SetOutDue and AssemblyDue don't have underscores whereas all the rest do. These must match your table field names exactly.
Yep, button values match Case # Values.
Yep Field names are correct too.

Here is Form with Delivery Due Selected, and its not sorted. (as an example)
1634860750817.png

Here is Data on Delivery Due
1634860820229.png

Here is Code (I reduced it down to one sort to see where problem is)
Code:
Private Sub FrameSortOptions_Click()
    Select Case Me.FrameSortOptions.Value
    
        Case 1
            Me.OrderBy = "IFA_Due" ', JobNumber, CompanyName"
        Case 2
            Me.OrderBy = "IFC_Due" ', JobNumber, CompanyName"
        Case 3
            Me.OrderBy = "SetOutDue" ', JobNumber, CompanyName"
        Case 4
            Me.OrderBy = "MachineShop_Due" ', JobNumber, CompanyName"
        Case 5
            Me.OrderBy = "AssemblyDue" ', JobNumber, CompanyName"
        Case 6
            Me.OrderBy = "Delivery_Due" ', JobNumber, CompanyName"
            
    End Select
            Me.OrderBy = True
End Sub

Cheers
 

cheekybuddha

AWF VIP
Local time
Today, 22:01
Joined
Jul 21, 2014
Messages
1,090
Have a look at the line I suggested you add in my post #4 and compare it with the line you added in your last post.
 

Chief

Registered User.
Local time
Today, 14:01
Joined
Feb 22, 2012
Messages
156
Have a look at the line I suggested you add in my post #4 and compare it with the line you added in your last post.
Doh!!!!!

That's it, Thanks Legend.

To have the Delivery due sort activate when the form opens, do I call that function in the open form event?
 

cheekybuddha

AWF VIP
Local time
Today, 22:01
Joined
Jul 21, 2014
Messages
1,090
To have the Delivery due sort activate when the form opens, do I call that function in the open form event?
Yes that's one way of doing it, and probably simplest, since it's consistent with how you will be ordering the records. (y)
It might be better to use the form's Load event rather than Open

You just need the two lines, not the whole Select Case statement:
Code:
Me.OrderBy = "Delivery_Due, JobNumber, CompanyName"
Me.OrderByOn = True
 

Chief

Registered User.
Local time
Today, 14:01
Joined
Feb 22, 2012
Messages
156
Doh!!!!!

That's it, Thanks Legend.

To have the Delivery due sort activate when the form opens, do I call that function in the open form event?
Actually
I think it does it on its own.

thanks mate.
 

Users who are viewing this thread

Top Bottom