Pass OpenArgs from form to subreport

I have the same problem as biang and I tried the answer by stevbe Posted 0n 6/2/04 but did not work. (Need to sort the "ProductName" ascending)

Here is what I have in the button that opens the report:

Private Sub Command0_Click()
MyArgs = ProductName
DoCmd.OpenReport "PriceListWholesaleEnglish", acViewPreview
End Sub

And in the open event of the subreport:

Private Sub Report_Open(Cancel As Integer)
Me.OrderBy = MyArgs
Me.OrderByOn = True
End Sub
dailymeatAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
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
0
dailymeatAuthor Commented:
Did not worked
0
Rey Obrero (Capricorn1)Commented:
what is the name of the MAIN report?
what is the name of the subreport?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

dailymeatAuthor Commented:
MAIN report "PriceListWholesaleEnglish"

SubReport "PriceListWholesalePoultry"
0
Rey Obrero (Capricorn1)Commented:
what Office version are you using?
0
dailymeatAuthor Commented:
Access 2002
0
Gustav BrockCIOCommented:
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

Open in new window

/gustav
0
dailymeatAuthor Commented:
Did not worked
0
Rey Obrero (Capricorn1)Commented:
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
0
Dale FyeOwner, Developing Solutions LLCCommented:
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
0
dailymeatAuthor Commented:
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.
0
Dale FyeOwner, Developing Solutions LLCCommented:
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])
0
Gustav BrockCIOCommented:
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
0
dailymeatAuthor Commented:
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
0
dailymeatAuthor Commented:
Just tried with the [ProductNameSpanish] visible, it does not sort at all
0
Dale FyeOwner, Developing Solutions LLCCommented:
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.
 steps to enable report sorting
0
dailymeatAuthor Commented:
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
0
Dale FyeOwner, Developing Solutions LLCCommented:
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.
0
dailymeatAuthor Commented:
No I do not have a language column, all I have is 2 text boxes, [ProductNameSpanish] and [ProductName]
0
Dale FyeOwner, Developing Solutions LLCCommented:
How is the report supposed to know what language you are using?

You might be able to use main forms OpenArgs argument, the Expression would read something like:

iif(me.parent.OpenArgs = "ProductName", [ProductName], [ProductNameSpanish])

but I have never actually tried that in a report.  Personally, I would probably have a combo box (cbo_RptLanguage) on the form that calls this report that allows the user to select either English or Spanish, and then have a single button that opens the report.  If you did it that way, then the expression would look like:

iif(Forms!yourFormName.cbo_RptLanguage = "English", [ProductName], [ProductNameSpanish])
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dailymeatAuthor Commented:
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])
0
dailymeatAuthor Commented:
It worked
0
Dale FyeOwner, Developing Solutions LLCCommented:
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?
0
dailymeatAuthor Commented:
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.
0
Dale FyeOwner, Developing Solutions LLCCommented:
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.
0
dailymeatAuthor Commented:
I screwed up and did not give Dale points, how do I do that?
0
Dale FyeOwner, Developing Solutions LLCCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.