qplo
asked on
sort or filter before open report
I have 4 fields, lastname, firstname, grade, schoolname
I created sql base on these fields. A report is based on this sql.
Now I would like to have user can selectable sort section , for example they can choose sort by schoolname then lastname then by grade OR sort by grade then firstname then schoolname, etc....
Then report will show whatever user selected.
Thanks
I created sql base on these fields. A report is based on this sql.
Now I would like to have user can selectable sort section , for example they can choose sort by schoolname then lastname then by grade OR sort by grade then firstname then schoolname, etc....
Then report will show whatever user selected.
Thanks
You can set the OrderBy in the report's Open event. Your report can "look" at a form to determine what to sort by:
Sub Report_Open()
Me.OrderBy = Forms("YourFormName").Some
Me.OrderByOn
End Sub
If you're using Groups on your report, however, you may find that OrderBy doesn't work as intended, however.
Sub Report_Open()
Me.OrderBy = Forms("YourFormName").Some
Me.OrderByOn
End Sub
If you're using Groups on your report, however, you may find that OrderBy doesn't work as intended, however.
ASKER
I have 2 questions:
first "yourFormName" you mean sql name or report name?
Run the code
Private Sub Report_Open(Cancel As Integer)
Me.OrderBy = Forms("ps1").grade
Me.OrderByOn
End Sub
Error: Compile error. invalid use property
Please help the correct error
first "yourFormName" you mean sql name or report name?
Run the code
Private Sub Report_Open(Cancel As Integer)
Me.OrderBy = Forms("ps1").grade
Me.OrderByOn
End Sub
Error: Compile error. invalid use property
Please help the correct error
Should be Me.OrderByOn = True
I have no idea what you mean by "sql name" ... YourFormName obviously refers to ... well ... Your Form Name.
I have no idea what you mean by "sql name" ... YourFormName obviously refers to ... well ... Your Form Name.
ASKER
this way is no flexible for user choose sort. I like to have a way user can select the way they want to sort.
I'm not sure what you're talking about, and for 30 points I've just about exhausted my time allotment for this question.
Your form could have a combo that included the various field names you want to be able to sort on, and the report could look to that combo for the field by which it should sort. I'm not sure how flexible you need to be, but giving a user the choice of sort fields would qualify, at least for my criteria. If you're looking to provide complete and robust filtering/sorting capabilities for your endusers then be prepared to spend a LOT of time on this ... in my most recent project fully 40% of development time was spent on building reporting filters and such.
Your form could have a combo that included the various field names you want to be able to sort on, and the report could look to that combo for the field by which it should sort. I'm not sure how flexible you need to be, but giving a user the choice of sort fields would qualify, at least for my criteria. If you're looking to provide complete and robust filtering/sorting capabilities for your endusers then be prepared to spend a LOT of time on this ... in my most recent project fully 40% of development time was spent on building reporting filters and such.
ASKER
I'm sorry, OK, for example, the user only use interface form, that why i like to have user select the way they want to sort. I can do the sort from the query or on the report form, but the user not suppose to do it.
I saw someone done that before, but I can not find it anymore.
Like they have check box or list box to select field sort in order or something like that.
I saw someone done that before, but I can not find it anymore.
Like they have check box or list box to select field sort in order or something like that.
qplo,
The easiest way out of this might be to just have the Report pick up the sorting from the query.
(When you create the Report *Do Not* set any Sorting or Grouping options)
You would build the Report's RecordSource in VBA.
Here is a very basic sample I threw together quickly, from something I had done a whlie ago.
I am sure you can adapt this to work in your database.
JeffCoachman
AccessEEQ23813140ControlReportSo.mdb
The easiest way out of this might be to just have the Report pick up the sorting from the query.
(When you create the Report *Do Not* set any Sorting or Grouping options)
You would build the Report's RecordSource in VBA.
Here is a very basic sample I threw together quickly, from something I had done a whlie ago.
I am sure you can adapt this to work in your database.
JeffCoachman
AccessEEQ23813140ControlReportSo.mdb
ASKER
boaq2000, please send me another one compatible with Access 2000, this one is unable top open. thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
YES, YES, this is exactly I would like to work on. Thank you for your help.
ASKER
great guy
;-)
ASKER