Sorting in a subform?

[Kysteratwork]

Hi All,

I have a subfrm (continuous form) where I would like to create a command button that sorts the entire subform by the text field txtCompany... sounds easy enough, but I don't know how to do that. Is it at all possible in a form?

Thank you in advance for any indication.

Kysteratwork

 

[PHV]

Take a look at the OrderBy and OrderByOn properties of the Form object.

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

 

[Kysteratwork]

PHV,

I understand, like this the subfrm would be ordered automatically?

In my case, it would be more useful to have an 'order by' button, so that e.g. once ten entries have been done, I can but the continuous form in the right order.

I tried smth like 'on Click'and then the code coCmd.order .... my vba is unfortunately still very poor.

You see what I mean?

 

[PHV]

In the Click event procedure of your 'order by' button assign desired values to the OrderBy and OrderByOn properties of the subform.

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

 

[ZmrAbdulla]

You can assign the click event of the label of any textbox to sort it to alphabetic order. Remember to change the exact name of label and textbox

[COLOR=blue]Private Sub [b][COLOR=red]Company[/color][/b]_Label_Click()
   Me.[COLOR=red]txtCompany[/color].SetFocus [COLOR=green]'You need to set foucus to the textbox[/color]
   DoCmd.RunCommand acCmdSortAscending
End Sub[/color]

Zameer Abdulla
Visit Me

 

[Kysteratwork]

Zameer,

I tried your code:

Private Sub Command72_Click()
   Me.InvestorName.SetFocus 
   DoCmd.RunCommand acCmdSortAscending

End Sub

But it gives me an error message at

DoCmd.RunCommand acCmdSortAscending

Btw, I placed the button in the form header.

Any idea why that is?
Kysteratwork

 

[TheAceMan1]

How are ya Kysteratwork . . . . .

Try this:
[ol][li]Base the [blue]RecordSource[/blue] of the subform on a [blue]query.[/blue][/li]
[li][purple]Do your sorting in the query.[/purple][/li]
[li]If your [purple]button is on the subForm[/purple], use this:

[blue]   Me.Requery[/blue]

If your [purple]button is on the MainForm[/purple], use this:

[blue]   Me![purple][b]MainFormName[/b][/purple]![purple][b]subformName[/b][/purple].Form.Requery[/blue]

[/li][/ol]

See Ya! . . . . . .

 

[Kysteratwork]

TheAceMan1....

Good idea. However, I get an error message at:

Me!frmIssueEntry!subfrmTransactions.Form.Requery

... but then, a refresh button would also do the trick, I found out. But I would also like to sort by some other item, if required.

Wouldn't ZmrAbdulla's idea work better for that? ... but I also get an error message there...
Kysteratwork

 

[PHV]

I get an error message
Any chance you can post the whole error message ?

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

 

[Kysteratwork]

Of course, I'm sorry.

TheAceman1's idea doesn't give me an error message anymore (...hmmm), but it doesn't sort.

The error message for ZMRAbdulla's code is:

The command or 'SortAscending'isn't available now.

My feeling is that Zamir's idea provides the most flexibility, but then, I can't get it to work.

I really appreciate you guy's helping me out on this.

Kysteratwork (boss chasing me, not wanting me to work on Access...)

 

[PHV]

Just to know, have you tried to play with OrderBy and OrderByOn ?

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

 

[Kysteratwork]

PHV,

In the Click event procedure of your 'order by' button assign desired values to the OrderBy and OrderByOn properties of the subform.

I have assigned 'InvestorNames' to the OrderBy in Events, but am rather lost on what to put in the On click event.
Also, the OrderByOn ... couldn't find this in the properties field of the button or form, nor do I presently know how to apply this in VBA.

... then the other guys came with ideas.

My logic though tells me that if I put OrderBy InvestorNames for the Form, then I am limited to sorting by InvestorNames, no?

 

[PHV]

I'm afraid you have to play with the F2 and F1 keys when in VBE.
BTW, the user can already sort the subform by either the contextual menu (right-click) or the sort button in the toolbar, so why reinvent the wheel ?

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

 

[Kysteratwork]

PHV,

didn't know about the F2 key

Also, good point with sort button - I will have to find a way to activate this though, since the application does currently not allow for this as I have dissabled the menus from the Startup... option.

Thank you for your ideas. I will experiment more with the F2 key.

Kysteratwork

 

[ZmrAbdulla]

Why my suggestions was not working??

Btw, I placed the button in the form header.
The command or 'SortAscending'isn't available now.

You have to use the command in the subform. otherwise you can't sort it...
Or you have to create a public sub then call it from the command button.. It is something like catching your left ear by right hand rather than left.
Regards

Zameer Abdulla
Visit Me

 

[Kysteratwork]

...hmmm is this a matter of just changing the private sub to "public sub"? ... if it is, it doesn't work either with the same error message:

"The command or 'SortAscending'isn't available now."

*scratching head*

Kysteratwork

 

[TheAceMan1]

Kysteratwork . . . . .

[blue]BTW, the user can already sort the subform by either the contextual menu (right-click) or the sort button in the toolbar, [purple]so why reinvent the wheel ?[/purple][/blue]

I couldn't agree more! . . . .

Also, when you try one method, [purple]are you removing the one previously tested?[/purple]

See Ya! . . . . . .

 

[kysterathome]

Ahhhh!!!! Am at home now, and now ZmrAbdulla's idea works!


Very nice. I think, TheAceMan1:
Also, when you try one method, are you removing the one previously tested?

was once again the right idea.

Thank you all - made my weekend!

Kysterathome (now ... and finally ready for breakfast)

 

[kysterathome]

Sorry guys, one more question:

If I place the button not in the subform, I tried the following code:

Private Sub Command131_Click()

   Forms!frmIssueEntry!subfrmTransactions.Form!InvestorName.SetFocus
   DoCmd.RunCommand acCmdSortAscending


End Sub

However, it doesn't recognise the right field (InvestorName).

How do I do it right?

Thanks again for your help!

Kysterathome

 

[ZmrAbdulla]

[COLOR=blue]Private Sub Command131_Click()
[COLOR=purple]Me.subfrmTransactions.SetFocus[/color] [COLOR=green]'Set focus to the subform first
[/color]Me!subfrmTransactions.Form!InvestorName.SetFocus [COLOR=green]'then to the control on the form
[/color]DoCmd.RunCommand acCmdSortAscending [COLOR=green]'do sorting
[/color]End Sub
[/color]

Zameer Abdulla
JVBP MDS
[sub]Jack of Visual Basic Programming, Master in Dining & Sleeping[/sub]
Visit Me