Results 1 to 5 of 5
  1. #1
    datadude60 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    2

    Exclamation Sorting Report Based off of a Combo Box in a Form

    I have a Form i have created to pull reports out of a SQL database. Everything has been working well up to the point where I have been tasked to make the user able to sort the report on the fly. meaning i have a combo box that has the following Row Source = "Mail Date";"Due Date";"CustName" I also have a list of about 15 reports to pick from. Basically I have all the reports Sort by = "Mail Date" and now they want me to make it so they can select from "Mail Date";"Due Date";"CustName" which are the same names of the fields in the query. I have been trying to get this to work for 2 days now and just can not figure out how to get this done. I am fairly new and self taught so any help you could give me would be GREATLY appreciated.

    I tried viewing the supporting query in SQL and then change the Order By [Forms]![frmReports]![Combo70] and that didn't work. I tried doing an event procedure in my preview command button and that didn't work. I've tried a bunch of things but like I said i am fairly new at this and am not sure if I am even scripting it correctly. Any help would be great.
    Thanks

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You say 'sort', do you mean 'filter'?

    You have a form for selecting criteria and a report that refers to the form for input parameters? Review this for one method http://datapigtechnologies.com/flash...earchform.html. Instead of opening the query as demonstrated, use it as the report RecordSource and open the report. Another method is to use VBA code to build a string for the WHERE argument of DoCmd.OpenReport action.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    datadude60 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    2
    Thanks for replying June7 sorry for the unclear post. When I was referring to Sort I meant to index the data Ascending. So I have a form that is a production report module. In this form they have the ability to select multiple reports and preview/print them. These reports have the following fields in the query... Mail Date, Due Date, CompanyName, Description, Process, ect. which are also reflected on the report. What I am trying to do is make it possible for the user to select a report and then let the user select what field the report will be sorted on /Indexed on. This will allow for one user to print the report in Mail Date Order and someone else to print it in Company Order. I have also attached some images to help clarify what I am attempting to do. Click image for larger version. 

Name:	Form_Screen_Shot.JPG 
Views:	13 
Size:	39.7 KB 
ID:	7437Click image for larger version. 

Name:	Report_Screen_Shot.JPG 
Views:	11 
Size:	40.9 KB 
ID:	7438

    I am not sure if this is the best way to upload pictures and if you will be able to see it bigger then how it is displayed but I thought I would give it a shot. Also I wanted to say thank you for the step by step video of what you thought I was needing. It is very cool and was very simple to follow. I thought that was slick . So if you have any suggestions on my new issue I would much appreciate it.

    Thanks again
    Datadude60

  4. #4
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    From a post on Yahoo Groups on Jun 27,2006 I found:

    Not possible from Print Preview. You can change Sorting and Grouping levels
    only in Design view
    . Make more than one report.

    John Viescas, author
    Building Microsoft Access Applications
    Microsoft Office Access 2003 Inside Out
    Running Microsoft Access 2000
    SQL Queries for Mere Mortals
    http://www.viescas.com/
    (Paris, France)
    However, I also found "Report_CustomGroupSort" at rogersaccesslibrary.com:
    http://www.rogersaccesslibrary.com/forum/topic57.html

    Good luck

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I have never tried to do what you are looking for and suspect that building alternate reports would be easier than trying to programmatically modify one report. Especially if the report has Grouping and Sorting set up. If not, could maybe set the report OrderBy property. But I have no idea if this property can be set during runtime, might have to be in DesignView. The link ssanfu referenced might have solution.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 03-12-2012, 02:58 AM
  2. Report based on combo box values as columns
    By sadozai in forum Reports
    Replies: 2
    Last Post: 12-09-2011, 04:40 PM
  3. Replies: 9
    Last Post: 08-30-2011, 04:08 PM
  4. Sorting a Combo box?
    By newtoAccess in forum Access
    Replies: 3
    Last Post: 03-19-2011, 03:57 PM
  5. Replies: 5
    Last Post: 01-02-2011, 10:09 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums