Sorting subform from cmdbtn on main form

S

Shodan

I'm trying to sort one of the columns in a subform when a cmdbtn is clicked
on the main form.

I created a blank form and called it frmResults. I highlighted my table
tblData and click on Insert | Form on the insert menu. I made the form a
datasheet and called it subfrmResults

I went into the design of frmResults and added subfrmResults to it.

My subform has several columns in it. The first one being "insurer". I
want to add a cmdbtnInsr to frmResults that will sort the the subform by the
insurer column
Can you help me?
 
S

strive4peace

Hi James,

Here is some generic code written to do that -- it sits behind the form
and the event procedure sends it the name of the control(s) to sort by
-- you can send up to 3 of them. I often put it on the double-click
event of the label over a column on a continuous form so each column can
be easily sorted.

Another click sorts in descending order.

'~~~~~~~~~~~~~~~~~~~~
Private Function SortMe123( _
pField1 As String _
, pField2 As String _
, pField3 As String)

'written by Crystal
'strive4peace2006 at yahoo dot com

Dim mOrder As String, mOrderZA As String
If Len(Trim(pField1)) > 0 Then
mOrder = pField1
mOrderZA = pField1 & " desc"
End If
If Len(Trim(pField2)) > 0 Then
mOrder = (mOrder + ", ") & pField2
mOrderZA = (mOrderZA + ", ") & pField2
End If
If Len(Trim(pField3)) > 0 Then
mOrder = (mOrder + ", ") & pField3
mOrderZA = (mOrderZA + ", ") & pField3
End If

If Me.OrderBy = mOrder Then
Me.OrderBy = mOrderZA
Else
Me.OrderBy = mOrder
End If
Me.OrderByOn = True
' FilterMe -- another procedure I commented out
End Function
'~~~~~~~~~~~~~~~~~~~~

examples:

sort just by Insurer
=SortMe123("insurer","","")

sorty by Lastname then Firstname then OrderDate
=SortMe123("Lastname","Firstname","OrderDate")


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
S

strive4peace

Hi James,

Here is some generic code written to do that -- it sits behind the form
and the event procedure sends it the name of the control(s) to sort by
-- you can send up to 3 of them. I often put it on the double-click
event of the label over a column on a continuous form so each column can
be easily sorted.

Another click sorts in descending order.

'~~~~~~~~~~~~~~~~~~~~
Private Function SortMe123( _
pField1 As String _
, pField2 As String _
, pField3 As String)

'written by Crystal
'strive4peace2006 at yahoo dot com

Dim mOrder As String, mOrderZA As String
If Len(Trim(pField1)) > 0 Then
mOrder = pField1
mOrderZA = pField1 & " desc"
End If
If Len(Trim(pField2)) > 0 Then
mOrder = (mOrder + ", ") & pField2
mOrderZA = (mOrderZA + ", ") & pField2
End If
If Len(Trim(pField3)) > 0 Then
mOrder = (mOrder + ", ") & pField3
mOrderZA = (mOrderZA + ", ") & pField3
End If

If Me.OrderBy = mOrder Then
Me.OrderBy = mOrderZA
Else
Me.OrderBy = mOrder
End If
Me.OrderByOn = True
' FilterMe -- another procedure I commented out
End Function
'~~~~~~~~~~~~~~~~~~~~

examples:

sort just by Insurer
=SortMe123("insurer","","")

sorty by Lastname then Firstname then OrderDate
=SortMe123("Lastname","Firstname","OrderDate")

~~~~~~~~~~~~~~~

If you want to issue the command to sort from the main form, you will
need to modify the code slightly... and it will sit behind the main
form, of course, not the form it is sorting. You may need to do

Me.subform_controlname.requery
when done

to modify the code...

instead of -->
Me.
use -->
Me.subform_controlname.form.



Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
S

strive4peace

ps
If you want it to be behind the mainform instead of the subform it is
sorting, you should modify the routine name to something like -->

SortResults123

in the Event property that you want to launch it is where you put the
equal sign followed by the function name and the parameters.

If you are not accustomed to calling procedures directly like that, you
may wonder why it is defined as a Function instead of a Sub -- it does
not return a vlue as a function typically would.

One of the rules to assign code directly in the property is that it is a
Function if what is being called is not an {Evnet Procedure], which is
always a Sub ...


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
S

Shodan

Thanks Crystal
--
Shodan


strive4peace said:
ps
If you want it to be behind the mainform instead of the subform it is
sorting, you should modify the routine name to something like -->

SortResults123

in the Event property that you want to launch it is where you put the
equal sign followed by the function name and the parameters.

If you are not accustomed to calling procedures directly like that, you
may wonder why it is defined as a Function instead of a Sub -- it does
not return a vlue as a function typically would.

One of the rules to assign code directly in the property is that it is a
Function if what is being called is not an {Evnet Procedure], which is
always a Sub ...


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Hi James,

Here is some generic code written to do that -- it sits behind the form
and the event procedure sends it the name of the control(s) to sort by
-- you can send up to 3 of them. I often put it on the double-click
event of the label over a column on a continuous form so each column can
be easily sorted.

Another click sorts in descending order.

'~~~~~~~~~~~~~~~~~~~~
Private Function SortMe123( _
pField1 As String _
, pField2 As String _
, pField3 As String)

'written by Crystal
'strive4peace2006 at yahoo dot com

Dim mOrder As String, mOrderZA As String
If Len(Trim(pField1)) > 0 Then
mOrder = pField1
mOrderZA = pField1 & " desc"
End If
If Len(Trim(pField2)) > 0 Then
mOrder = (mOrder + ", ") & pField2
mOrderZA = (mOrderZA + ", ") & pField2
End If
If Len(Trim(pField3)) > 0 Then
mOrder = (mOrder + ", ") & pField3
mOrderZA = (mOrderZA + ", ") & pField3
End If

If Me.OrderBy = mOrder Then
Me.OrderBy = mOrderZA
Else
Me.OrderBy = mOrder
End If
Me.OrderByOn = True
' FilterMe -- another procedure I commented out
End Function
'~~~~~~~~~~~~~~~~~~~~

examples:

sort just by Insurer
=SortMe123("insurer","","")

sorty by Lastname then Firstname then OrderDate
=SortMe123("Lastname","Firstname","OrderDate")

~~~~~~~~~~~~~~~

If you want to issue the command to sort from the main form, you will
need to modify the code slightly... and it will sit behind the main
form, of course, not the form it is sorting. You may need to do

Me.subform_controlname.requery
when done

to modify the code...

instead of -->
Me.
use -->
Me.subform_controlname.form.



Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
Click to expand...
Click to expand...
 
S

strive4peace

you're welcome, James ;) happy to help


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Populate main form from subform record 0
main form/subform 1
changing subform source object 5
Clearing combo boxes and a text box 2
DLookup in Continuous form 0
showing a subform based on the main form 5
ApplyFilter gives error only when subform is placed on main form 1
Passing a value from subform or main form to subform query 4

Top