dynamically creating order by string

C

CathyZ

Hi,

I have a form which has parameters for the user to select, and then open a
report.
I could use the parameters on the form to create the where clause that I can
use in the doCmd.open report statement. But I am also trying to use some of
these parameters on the form as the order by statement. I try saving the
statements into a public variable from the form's code and use it with the
report load sub. But the variable values remains empty. Is there another
way to send the value of a variable from a form to a report to be used in the
order by statement?

Any help appreciated.
 
K

Ken Sheridan

If the variables are declared in a standard module as Public then they should
be visible to the report's module. If they are declared Public in the form's
module they are a property of the class so you need to qualify them with the
class name, e.g. Form_frmMyForm.strMyVariable. A better option would be to
pass the values to the report as its OpenArgs property. This can be done by
building a single string expression separated by commas, e.g.
"LastName,FirstName" and assigning this to a variable. The OpenReport method
call would then go something like this:

Dim strWhereCondition As String, strOrderBy As String

strWhereCondition = <get string expression from parameters>
strOrderBy = <get string expression from parameters>

DoCmd.OpenReport "YourReport", _
View:=AcViewPreview, _
WhereCondition:=strWhereCondition , _
OpenArgs:=strOrderBy

In the report's Open event procedure you can then set its sort order like so:

If Not IsNull(Me.OpenArgs) Then
Me.OrderBy = Me.OpenArgs
Me.OrderByOn = True
End If

For a more flexible way of using the OpenArgs mechanism, allowing you to
pass an argument list or named arguments I have placed a demo which uses a
module for doing this at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=24091&webtag=ws-msdevapps


Ken Sheridan
Stafford, England
 
C

CathyZ

Thanks Ken,

This is great!

Ken Sheridan said:
If the variables are declared in a standard module as Public then they should
be visible to the report's module. If they are declared Public in the form's
module they are a property of the class so you need to qualify them with the
class name, e.g. Form_frmMyForm.strMyVariable. A better option would be to
pass the values to the report as its OpenArgs property. This can be done by
building a single string expression separated by commas, e.g.
"LastName,FirstName" and assigning this to a variable. The OpenReport method
call would then go something like this:

Dim strWhereCondition As String, strOrderBy As String

strWhereCondition = <get string expression from parameters>
strOrderBy = <get string expression from parameters>

DoCmd.OpenReport "YourReport", _
View:=AcViewPreview, _
WhereCondition:=strWhereCondition , _
OpenArgs:=strOrderBy

In the report's Open event procedure you can then set its sort order like so:

If Not IsNull(Me.OpenArgs) Then
Me.OrderBy = Me.OpenArgs
Me.OrderByOn = True
End If

For a more flexible way of using the OpenArgs mechanism, allowing you to
pass an argument list or named arguments I have placed a demo which uses a
module for doing this at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=24091&webtag=ws-msdevapps


Ken Sheridan
Stafford, England
Click to expand...
 
Top