The boss wants to be able to sort a report before he prints it. It needs to
sort by either Area, School District, Plan Type, or Project Approval. Is
there a way to put a combo or something to allow him to pick his sort? Thanks
a bunch!!!

On Fri, 15 Aug 2008 06:40:00 -0700, Golfinray
<Golfinray@discussions.microsoft.com> wrote:

Yes. When you click the report button, pop up a form with that
dropdown. Then your report in its Report_Open event can "look back" at
that form and decide how to set the sort order. You know the sort
order should be set in the report, not in the underlying query, right?

-Tom.
Microsoft Access MVP


>The boss wants to be able to sort a report before he prints it. It needs to
>sort by either Area, School District, Plan Type, or Project Approval. Is
>there a way to put a combo or something to allow him to pick his sort? Thanks
>a bunch!!!

I find that the easiest way to sort/group by different ways on a report
is to base the report on a query

ie:

SELECT [fieldname1] as [Sort1]
, [fieldname2] as [Sort2]
, [fieldname3]
, [fieldname3]
FROM [tablename]
etc

~~~

use a form to collect the report critera and the sort method. Then, on
the button to process the report, replace the query it is based on
before you open it

'~~~~~~~~~~~~~~~~~~~~~ MakeQuery
Sub MakeQuery( _
ByVal pSql As String, _
ByVal qName As String)

'modified 3-30-08
'crystal
'strive4peace2008 at yahoo dot com

On Error GoTo Proc_Err

debug.print pSql

'if query already exists, update the SQL
'if not, create the query

If Nz(DLookup("[Name]", "MSysObjects", _
"[Name]='" & qName _
& "' And [Type]=5"), "") = "" Then
CurrentDb.CreateQueryDef qName, pSql
Else
'if query is open, close it
on error resume next
DoCmd.Close acQuery, qName, acSaveNo
On Error GoTo Proc_Err
CurrentDb.QueryDefs(qName).sql = pSql
End If

Proc_exit:
CurrentDb.QueryDefs.Refresh
DoEvents
Exit Sub

Proc_error:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " MakeQuery"

Resume Proc_Exit

'if you want to single-step code to find error, CTRL-Break at MsgBox
'then set this to be the next statement
Resume
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~

to use the MakeQuery procedure, put this in your code:

MakeQuery strSQL, "YourQueryName"


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*




Golfinray wrote:
> The boss wants to be able to sort a report before he prints it. It needs to
> sort by either Area, School District, Plan Type, or Project Approval. Is
> there a way to put a combo or something to allow him to pick his sort? Thanks
> a bunch!!!

Tom. I can put a combo on the form with the sort selection, but how do I get
that to work in the report. I don't really want to make that selection in the
query for other reasons. Thanks!

"Tom van Stiphout" wrote:

> On Fri, 15 Aug 2008 06:40:00 -0700, Golfinray
> <Golfinray@discussions.microsoft.com> wrote:
>
> Yes. When you click the report button, pop up a form with that
> dropdown. Then your report in its Report_Open event can "look back" at
> that form and decide how to set the sort order. You know the sort
> order should be set in the report, not in the underlying query, right?
>
> -Tom.
> Microsoft Access MVP
>
>
> >The boss wants to be able to sort a report before he prints it. It needs to
> >sort by either Area, School District, Plan Type, or Project Approval. Is
> >there a way to put a combo or something to allow him to pick his sort? Thanks
> >a bunch!!!
>

ps

the [Sort1] and [Sort2] fields will be set as the sort/group by fields
in the report definition

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*




Tom van Stiphout wrote:
> On Fri, 15 Aug 2008 06:40:00 -0700, Golfinray
> <Golfinray@discussions.microsoft.com> wrote:
>
> Yes. When you click the report button, pop up a form with that
> dropdown. Then your report in its Report_Open event can "look back" at
> that form and decide how to set the sort order. You know the sort
> order should be set in the report, not in the underlying query, right?
>
> -Tom.
> Microsoft Access MVP
>
>
>> The boss wants to be able to sort a report before he prints it. It needs to
>> sort by either Area, School District, Plan Type, or Project Approval. Is
>> there a way to put a combo or something to allow him to pick his sort? Thanks
>> a bunch!!!

ps

the [Sort1] and [Sort2] fields will be set as the sort/group by fields
in the report definition

the only time a sort order in an underlying query is actually used by
the report is if the report has no sorting or grouping defined

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*




strive4peace wrote:
> I find that the easiest way to sort/group by different ways on a report
> is to base the report on a query
>
> ie:
>
> SELECT [fieldname1] as [Sort1]
> , [fieldname2] as [Sort2]
> , [fieldname3]
> , [fieldname3]
> FROM [tablename]
> etc
>
> ~~~
>
> use a form to collect the report critera and the sort method. Then, on
> the button to process the report, replace the query it is based on
> before you open it
>
> '~~~~~~~~~~~~~~~~~~~~~ MakeQuery
> Sub MakeQuery( _
> ByVal pSql As String, _
> ByVal qName As String)
>
> 'modified 3-30-08
> 'crystal
> 'strive4peace2008 at yahoo dot com
>
> On Error GoTo Proc_Err
>
> debug.print pSql
>
> 'if query already exists, update the SQL
> 'if not, create the query
>
> If Nz(DLookup("[Name]", "MSysObjects", _
> "[Name]='" & qName _
> & "' And [Type]=5"), "") = "" Then
> CurrentDb.CreateQueryDef qName, pSql
> Else
> 'if query is open, close it
> on error resume next
> DoCmd.Close acQuery, qName, acSaveNo
> On Error GoTo Proc_Err
> CurrentDb.QueryDefs(qName).sql = pSql
> End If
>
> Proc_exit:
> CurrentDb.QueryDefs.Refresh
> DoEvents
> Exit Sub
>
> Proc_error:
> MsgBox Err.Description, , _
> "ERROR " & Err.Number & " MakeQuery"
>
> Resume Proc_Exit
>
> 'if you want to single-step code to find error, CTRL-Break at MsgBox
> 'then set this to be the next statement
> Resume
> End Sub
> '~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> to use the MakeQuery procedure, put this in your code:
>
> MakeQuery strSQL, "YourQueryName"
>
>
> Warm Regards,
> Crystal
>
> remote programming and training
>
> Access Basics
> 8-part free tutorial that covers essentials in Access
> http://www.AccessMVP.com/strive4peace
>
> *
> (: have an awesome day :)
> *
>
>
>
>
> Golfinray wrote:
>> The boss wants to be able to sort a report before he prints it. It
>> needs to sort by either Area, School District, Plan Type, or Project
>> Approval. Is there a way to put a combo or something to allow him to
>> pick his sort? Thanks a bunch!!!

Reports have an OrderBy and OrderByOn property so if the report is otherwise
ungrouped/unsorted you can set these in the report's Open event procedure by
referencing the combo box on your dialogue form:

Const FORMNOTOPEN = 2450
Dim frm As Form
Dim ctrl As Control

On Error Resume Next
Set frm = Forms("YourForm")
Select Case Err.Number
Case 0
' no error
Set ctrl = frm("YourComboBox")
' if a sort order has been selected
' then order the report
If Not IsNull(ctrl) Then
Me.OrderBy = ctrl
Me.OrderByOn = True
End If
Case FORMNOTOPEN
' open report unordered
Case Else
' unknown error so inform user
MsgBox Err.Description, vbExclamation, "Error"
End Select

Select the sort order you want in the combo box on the dialogue form and
open the report from a button on the form with:

DoCmd.OpenReport "YourReport"

Ken Sheridan
Stafford, England

"Golfinray" wrote:

> The boss wants to be able to sort a report before he prints it. It needs to
> sort by either Area, School District, Plan Type, or Project Approval. Is
> there a way to put a combo or something to allow him to pick his sort? Thanks
> a bunch!!!