Pass OpenArgs from form to subreport

you have to pass the value of the variable "MyArgs" to the command

DoCmd.OpenReport "PriceListWholesaleEnglish", acViewPreview, OpenArgs:=MyArgs

in the open or load event of the subreport

Private Sub Report_Open(Cancel As Integer)
if me.openargs & ""<>"" then
Me.OrderBy = me.openArgs
Me.OrderByOn = True
end if
End Sub

Did not worked

what is the name of the MAIN report?
what is the name of the subreport?

MAIN report "PriceListWholesaleEnglish"

SubReport "PriceListWholesalePoultry"

what Office version are you using?

Access 2002

The open args belongs to the parent. Also, have in mind that the subform opens twice, first before the parent, second after the parent form, thus:

Private Sub Report_Open(Cancel As Integer)
     If Nz(Me.Parent.OpenArgs) <> Me.OrderBy Then
         Me.OrderBy = Nz(Me.Parent.OpenArgs)
         Me.OrderByOn = True
     End If
End Sub

Select allOpen in new window

/gustav

Did not worked

here is how to do this.
if the record source of your subreport is a table, create a query against the table.
to apply the order by dynamically, change the querydef before opening the report

dim qd as dao.querydef, db as dao.database
set db=currentdb

set qd=db.querydefs("YourQuery")

qd.sql="select * from tablex order by [ProductName]"


DoCmd.OpenReport "PriceListWholesaleEnglish", acViewPreview


get the idea?

post back if you need more help..

'open the report

OK, I think everyone is confusing reports and forms.

In reports, if you have a sort order defined in the report definition, I don't think you can override it, can you?

1. Open the report that is the subreport in design view.
2. Select the report (upper left corner) then right click and turn on the Sorting and Grouping
3.  Select "Add a Sort" and select the field (ProductName) and direction
4.  Save the subreport
5.  Run your report

Can not do that,  in the subreport there is 2 fields, "ProductName" and "ProductNameSpanish", when I print the subreport in English "ProductNameSpanish" is not visible and viceversa.

Then you could create an Expression as the sort order.

Is there a field that indicates whether it is Spanish or English?

If so, the expression would look like:

IIF([language] = "Spanish", [ProductNameSpanish], [ProductName])

Dale is right, it is grouping and sorting to control in reports.
Actually, any sorting in the queries used as source should be removed.

/gustav

Dale:
We are almost there, I had to put an = sign in front of the expression:
=IIf([language]="Spanish",[ProductNameSpanish],[ProductName])
we still have a problem, when I tried to view in preview, a window pops up asking for the Language
I click ok and the report shows the ProductName ascending the way is suppose to

Just tried with the [ProductNameSpanish] visible, it does not sort at all

Should not matter whether the field is visible in the report or not, as long as both of those fields are included in the record source for the sub-report.

Did you set this sort order in the sub-report or in the main report, must be done in the sub-report.  Can you post what you are using for the sort expression?  You understand that report sorting is not done by the OrderBy property, correct?  To do that:
1.  open the SUB-REPORT in design view
2.  right click on the report selector and select Sorting and Grouping
3.  This will display the Group, Sort, and Total section at the bottom of the page
4.  Click the add sort button
5.  Click on the Expression selection at the bottom of the popup and build the expression to use in the subform sort.
 

Did you set this sort order in the sub-report or in the main report, in the subreport

 Can you post what you are using for the sort expression?
=IIf([language]="Spanish",[ProductNameSpanish],[ProductName])

You understand that report sorting is not done by the OrderBy property, correct? Yes

To do that:
1.  open the SUB-REPORT in design view
2.  right click on the report selector and select Sorting and Grouping
3.  This will display the Group, Sort, and Total section at the bottom of the page
4.  Click the add sort button
5.  Click on the Expression selection at the bottom of the popup and build the expression to use in the subform sort.
That is what I did

do you actually have a [language] column in the reports record source?  That was an example, you need to use an expression which will accurately determine the language that is being used for the report.

No I do not have a language column, all I have is 2 text boxes, [ProductNameSpanish] and [ProductName]

Here is what I did and it worked:
In a form I made an unbound box named “English” and then
English = "English"
DoCmd.OpenReport "PriceListSpecialEnglish", acNormal, "", ""
If I want to print the report in Spanish then
English = "Spanish"
DoCmd.OpenReport "PriceListSpecialSpanish", acNormal, "", ""
In the subreport in the sorting and grouping window
=IIf([Forms]![PriceListsPrint].[English]="English",[ProductName],[ProductNameSpanish])

It worked

Glad you got it working

Did you intend to ignore all of the help you received getting to that point, or did you accidentally select your final solution as the only solution?

I am new to the Expert Exchange, I accidentally select the final solution as the only solution.
I did not mean to ignore anyone involved that help me.
THANK YOU, everyone.

dailymeat.  I guess you are not a vegan!

To reopen the question, click the "Request Attention" hyperlink at the bottom right corner of your original post.  Then indicate that you would like to reopen the question to reallocate points.

You can select a single solution, or click the "Accept Multiple Solutions" hyperlink that shows up next to the name of each commenter.  If you accept multiple solutions, you can allocate points based on the degree of assistance that each response helped you with your solution (they do not all have to be equal).

You will also be asked to provide a grade.

I screwed up and did not give Dale points, how do I do that?

You have to go back to your original post and select the "Request Attention" hyperlink at the bottom right.  In the message box, indicate that you would like to reallocate points.