DoCmd.OpenReport and OrderBy

[CLSWL]

1st: Where do i insert the order by criteria in a DoCmd.OpenReport?
2nd: What syntax do i use?
3rd: How do I get a string variable from a sub form to become the OrderBy
string

I have a form that filters my report. I have now created a subform to sort
the report. The subform has the following variable:

Dim Ord As String 'Variable that sets the sorting of the report

I don't know how to get that value from the sub form to the main form...

Once I do that, I don't know where to put the string in the
DoCmd.OpenReport. Does it belong in the "WhereCondition"? If so, I already
have something in that slot:

strFilter = strMod & "AND" & strWindTyp & "AND" & strDrawNo & "AND" &
strDrawTyp

strMod = "[Model].[AutoID] IN (" & Left$(strMod, l_Mod) & ")"

How do I add on to that syntax to put in the OrderBy criteria?

 

[Ken Sheridan]

You can't pass the OrderBy property directly to the report. Instead pass it
as its OpenArgs property, which can then be read in the report's OpenEvent
procedure.

Assign the value of the Ord variable in the subform's module to a hidden
text box control on the main parent form. This can be identified in the
subform's module by it parent property so use:

Parent.txtHidden = Ord

in the subform's module, where txtHidden is the hidden text box on the
parent form. To open the report pass the value of the hidden text box
control as the OpenArgs argument of the OpenReport method:

DoCmd.OpenReport, "YourReport", _
WhereCondition:=strFilter, _
OpenArgs:=Me.txtHidden

In the report's Open event procedure put:

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

I'm assuming that you do in fact mean a true subform here, i.e. one that is
embedded in the main form in a subform control. I'm not sure why you need a
subform for selecting the sort order, however. Most of the time a subform is
used for showing rows from a table related to the main table's underlying
recordset, not as a dialogue. If the 'subform' is actually a separate
dialogue form then instead of using the parent property, in its module assign
a value to the hidden control on the main form by referencing it as a member
of the Forms collection:

Forms("YourMainForm").txtHidden = Ord

Ken Sheridan
Stafford, England

 

[CLSWL]

Nevermind. Instead I created a separate form to order the reports.
--
-CLSWL

You can't pass the OrderBy property directly to the report. Instead pass it
as its OpenArgs property, which can then be read in the report's OpenEvent
procedure.

Assign the value of the Ord variable in the subform's module to a hidden
text box control on the main parent form. This can be identified in the
subform's module by it parent property so use:

Parent.txtHidden = Ord

in the subform's module, where txtHidden is the hidden text box on the
parent form. To open the report pass the value of the hidden text box
control as the OpenArgs argument of the OpenReport method:

DoCmd.OpenReport, "YourReport", _
WhereCondition:=strFilter, _
OpenArgs:=Me.txtHidden

In the report's Open event procedure put:

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

I'm assuming that you do in fact mean a true subform here, i.e. one that is
embedded in the main form in a subform control. I'm not sure why you need a
subform for selecting the sort order, however. Most of the time a subform is
used for showing rows from a table related to the main table's underlying
recordset, not as a dialogue. If the 'subform' is actually a separate
dialogue form then instead of using the parent property, in its module assign
a value to the hidden control on the main form by referencing it as a member
of the Forms collection:

Forms("YourMainForm").txtHidden = Ord

Ken Sheridan
Stafford, England

1st: Where do i insert the order by criteria in a DoCmd.OpenReport?
2nd: What syntax do i use?
3rd: How do I get a string variable from a sub form to become the OrderBy
string

I have a form that filters my report. I have now created a subform to sort
the report. The subform has the following variable:

Dim Ord As String 'Variable that sets the sorting of the report

I don't know how to get that value from the sub form to the main form...

Once I do that, I don't know where to put the string in the
DoCmd.OpenReport. Does it belong in the "WhereCondition"? If so, I already
have something in that slot:

strFilter = strMod & "AND" & strWindTyp & "AND" & strDrawNo & "AND" &
strDrawTyp

strMod = "[Model].[AutoID] IN (" & Left$(strMod, l_Mod) & ")"

How do I add on to that syntax to put in the OrderBy criteria?