Solved Option Group, Sorting Form based on selection

[Chief]

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

One of the select case code I have tried is

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]

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]

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]

Or maybe you've forgotten to just turn on the ordering:

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]

Or maybe you've forgotten to just turn on the ordering:

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]

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]

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)

Here is Data on Delivery Due

Here is Code (I reduced it down to one sort to see where problem is)

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]

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]

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]

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.
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:

Me.OrderBy = "Delivery_Due, JobNumber, CompanyName"
Me.OrderByOn = True

 

[Chief]

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.

 

[cheekybuddha]

I think it does it on its own.

It will do if you sorted in the RecordSource.

Or it may remember the OrderByOn state from when the form was last open - I can't remember.