How to sort a subform from vba (1 Viewer)

[KitaYama]

I can call a function from any main form and sort it based on a specific requirement.

Public Sub ReSortForm (frmName as string)
  
    Dim frm as Form
    Dim CurrentSort As String
  
    Set frm = Forms(frmName)
    With frm
        CurrentSort = .OrderBy
        ..... Do something here
        ' Resort form with show order
        If SortThis = "Reset" Then
            frm.OrderByOn = False
        Else
            .OrderBy = tbl & SortThis & " " & AscDesc & SecondSort
            .OrderByOn = True
        End If
    End With
  
    ..... other actions here

But if I call the same function from a sub form , the following line fails.
Set frm = Forms(frmName)

Because Access doesn't see a sub form as an opened form.

Is there any way to go around this?

thank you.

 

[ebs17]

A subform is a control of the main form and must be addressed as such.

With Me.ControlName.Form
    .OrderBy "FieldX, FieldY DESC"
    .OrderByOn = True
End With

 

[arnelgp]

you change your sub declaration and pass your form/subform:

Public Sub ReSortForm (frm As Form)
 
    Dim CurrentSort As String
 
    With frm
        CurrentSort = .OrderBy
        ..... Do something here
        ' Resort form with show order
        If SortThis = "Reset" Then
            frm.OrderByOn = False
        Else
            .OrderBy = tbl & SortThis & " " & AscDesc & SecondSort
            .OrderByOn = True
        End If
    End With
 
    ..... other actions here

now to pass a Form:

Call ResortForm Me

to pass a subform:

Call ResortForm Me.SubformName.Form

 

[KitaYama]

@ebs17 thanks for helping. I'm not in front of PC now. I'll test it and will report back. Though I have a feeling it will fail. Because of the following.

@arnelgp Thanks for the solution.
I should have explained better. The function is fired when a user clicks labels of a sub form. I have the following code in OnLoad event of the subform :

    Dim ctrl As Control
    Dim S As String

    For Each ctrl In Me.Controls
        If ctrl.ControlType = acLabel Or ctrl.ControlType = acCommandButton Then
            Select Case Left(ctrl.Name, 7)
                Case "lblSort", "cmdSort", "btnSort"
                    S = "=ReSortForm ('" & Me.Name & "', '" & Right(ctrl.Name, Len(ctrl.Name) - 7) & "')"
                    ctrl.OnClick = S
            End Select
        End If
    Next

It means if a label name is lblSortOrderPK, and the form name is frmOrders, the onClick event of the label is set to:
=ResortForm("frmOrders","OrderPK")

Me.Name is a string and I can use it and concatenate it and create above string and set it as the OnClick event.
How can I change S variable above to pass
Me.SubformName.Form
to the function as an OnClick Event?

thanks

 

[CJ_London]

why not pass the form rather than the name as Arnel suggests

=ResortForm(Me,"OrderPK")

or if the subform is intended to sort the main form

=ResortForm(Parent,"OrderPK")

 

[KitaYama]

why not pass the form rather than the name as Arnel suggests

=ResortForm(Me,"OrderPK")

or if the subform is intended to sort the main form

=ResortForm(Parent,"OrderPK")

Did you read #4?

 

[theDBguy]

How can I change S variable above to pass
Me.SubformName.Form
to the function as an OnClick Event?

Just a guess, but maybe you could try something like?

S = "=ReSortForm (" & Me.Parent.Name & ".Form, '" & Right(ctrl.Name, Len(ctrl.Name) - 7) & "')"

 

[CJ_London]

OK, missed that - in that case it would be

=ResortForm([FORM],"OrderPK")

edit

S = "=ReSortForm ([FORM], '" & Right(ctrl.Name, Len(ctrl.Name) - 7) & "')"

However suspect you need to use double quotes

S = "=ReSortForm ([FORM], """ & Right(ctrl.Name, Len(ctrl.Name) - 7) & """)"

 

[MajP]

I have not tested this, but I think the active form will be the subform when you initiate from the subform. So no need to even pass a form.

dim frm as Access.form
set frm = Screen.activeform

 

[CJ_London]

no - screen.activeform is the main form

Screen.ActiveForm property (Access)

Office VBA reference topic

learn.microsoft.com

@KitaYama - just seeking clarification - the labels are on the subform and you want to sort the subform? If so, presumably this is a continuous form and the sorting available on the right click menu in the detail section is not what you want?

 

[KitaYama]

the labels are on the subform?

Yes

you want to sort the subform?

Yes

If so, presumably this is a continuous form

Yes

sorting available on the right click menu in the detail section is not what you want?

The right click is disabled. If we don't find any solution, we will add a custom context menu bar and add only sort to it.
We wanted to be sure there's no another way to do it.

 

[KitaYama]

Maybe if I make a class it may work out. Because in the OnLoad event, the subform is passed to the class as an object. So I can work on it.

@theDBguy I will test your solution and see how it goes. But unfortunately I have an important meeting this morning.
I'll do it in the afternoon.
thanks

Edit:
I couldn't concentrate on the meeting. So I preferred to postpone it 30 minutes and test your solution.
Unfortunately it throws the following error:

 

[KitaYama]

S = "=ReSortForm ([FORM], '" & Right(ctrl.Name, Len(ctrl.Name) - 7) & "')"

@CJ_London
You're a star. I don't know what we have done without you. How many times you saved us.
Implementing @arnelgp 's solution (Passing the form and not it's name) and use your given code solved the problem.

I really don't know how to thank both of you.
Million Thanks.

 

[KitaYama]

However suspect you need to use double quotes
S = "=ReSortForm ([FORM], """ & Right(ctrl.Name, Len(ctrl.Name) - 7) & """)"

Just wanted to be clear.
Single quote is OK. Just like my post #4 above.

This will work.
S = "=ReSortForm ([FORM], '" & Right(ctrl.Name, Len(ctrl.Name) - 7) & "')"

Thanks again.

 

[CJ_London]

happy to help when I can.....

 

[CJ_London]

As an aside I sometimes use buttons rather than labels so I can show an up/down arrow to indicate the direction of sort.

You can use the inbuilt arrows but you do need to save them to the mysysResources table

or take an image of one of the web/wingding fonts - again saving to msysResources

Code is very simple

If Command0.Picture = "ArrowUp" Then
        Command0.Picture = "ArrowDown"
    Else
        Command0.Picture = "ArrowUp"
    End If

and the button might look like these examples depending on whether you want the text to the right or left.

if text is to the left then you need to add some spaces and a char (I use a pipe) to move the image to the right. Text to the right is less work!

You can also make the control do different things depending on where the user clicks on the button or whether they use a right/left button and/or shift. With a little bit of experimenting you can determine the image position and code accordingly.

So click on the arrow image, it sorts, click on the caption it filters for example (you can change caption, border or back colour if you want to indicate the column is filtered) I wouldn't use the bold/italic as that could mess up the image position if it's on the right.

 

[KitaYama]

As an aside I sometimes use buttons rather than labels so I can show an up/down arrow to indicate the direction of sort.

You can use the inbuilt arrows but you do need to save them to the mysysResources table
View attachment 104255
or take an image of one of the web/wingding fonts - again saving to msysResources
View attachment 104258

Code is very simple

If Command0.Picture = "ArrowUp" Then
        Command0.Picture = "ArrowDown"
    Else
        Command0.Picture = "ArrowUp"
    End If

and the button might look like these examples depending on whether you want the text to the right or left.
View attachment 104259

if text is to the left then you need to add some spaces and a char (I use a pipe) to move the image to the right. Text to the right is less work!

You can also make the control do different things depending on where the user clicks on the button or whether they use a right/left button and/or shift. With a little bit of experimenting you can determine the image position and code accordingly.

So click on the arrow image, it sorts, click on the caption it filters for example (you can change caption, border or back colour if you want to indicate the column is filtered) I wouldn't use the bold/italic as that could mess up the image position if it's on the right.

@CJ_London
Thanks for the additional info.

I also use arrows to show the sort direction in labels.
One priority of our language to English is that we have several hundred of smiles and characters in our language. So without using Webdings font or using images as you explained we can simply use them as a character between our words, sentences or letters.
So it's enough to change the caption of the label to show the arrows.

Though your idea about having the ability to run different actions by clicking different sections of the control is brilliant. I have to think about it. I'm sure I can use the idea.

Thanks again.

 

[CJ_London]

interesting - what font are you using? I've never really explored the higher numbers for unicode, it may be those characters exist there

edit: found it

as an example as a controlsource

="abc " & ChrW("&H25B2")

produces

abc ▲

"&H25B2" evaluates to 9650

Learned something new today!

 

[KitaYama]

interesting - what font are you using? I've never really explored the higher numbers for unicode, it may be those characters exist there

Any font. It's not the part of the font. It's a part of our language.
For example when I type 矢印 (Arrow), we have the option to use a word or a charachter.

No 6 & 7 are different. They come from IME and can not be used.

If I type 三角 (triangle) I have the option to choose any of them in any application. even in vba.

And then set the label's caption. Not that it can not be used for buttons or other objects, but I simply preferred using labels.

 

[KitaYama]

as an example as a controlsource

="abc " & ChrW("&H25B2")

Are you able to use it in vba too? For example to set the caption of a button or a label?
Mybutton.Caption = "abc " & ChrW("&H25B2")