What the boss wants now?

G

Golfinray

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!!!
 
T

Tom van Stiphout

On Fri, 15 Aug 2008 06:40:00 -0700, Golfinray

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
 
S

strive4peace

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 :)
*
 
G

Golfinray

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!
 
S

strive4peace

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 :)
*
 
S

strive4peace

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 :)
*



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 :)
*



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!!!
Click to expand...
Click to expand...
 
K

Ken Sheridan

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
 
Top