Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
 
Home > TechHelp > Directory > Access > Order By Filter On Load < Reminder Popup | Reminder Popup Date >
Back to Order By, Filter On Load    Comments List
Alter Sort From calling VBA Upload Images   Link   Email  
Sami Shamma               
2 years ago
I have watched the relevant videos. I have a code that open my report:

  'Biuld the Filter final statment
  Filter = Filter & vService
  If vPrintView = "Yes" Then
      DoCmd.OpenReport "RosterR", acViewPreview, , Filter
  Else
      DoCmd.OpenReport "RosterR", acViewNormal, , Filter
  End If
I have another Variable vOrderBy. if it is = "Yes" I want to Order by FullName if not order By InmateNumber.

How do I do that please.

many thanks

Dr. Sami Shamma
Sami Shamma OP               
2 years ago
Hi there.
I just figured it out.
Thanks.

But still would love to see if you will give me a better solution.

Dr. Sami Shamma
Sami Shamma OP               
2 years ago
Hi Again,
Spoke too soon. My way did not work after all
Kevin Robertson            
2 years ago
What is vPrintView declared as. I believe it should be a Boolean (True/False) - you're comparing it to a string ("Yes")
Also, you say your way didn't work - what did you try?

From the code given above, try this:
Dim vPrintView As Boolean

If vPrintView Then
    DoCmd.OpenReport "RosterR", acViewPreview, , Filter
Else
    DoCmd.OpenReport "RosterR", acViewNormal, , Filter
End If
Sami Shamma OP               
2 years ago

Sami Shamma OP               
2 years ago
Hi Kevin.
Can I manipulate the sort element in the Group section of a report? if I can, I can fix my problem.
Kevin Yip       
2 years ago
It requires some tricky VBA to change the grouping(s) inside a report.  You need to put VBA code inside the report to do that.

Inside the report's VBA module (in the Open event preferably, because you want the change to take effect as soon as the report opens), you need to change the report's GroupLevel property.  For instance, if my report's recordsource contains the fields country_name, capital, and continent, I specify the "continent" field to be made a group with this code:

   Me.GroupLevel(0).ControlSource = "continent"

The ordinal number (0) indicates that this is the first (top-level) grouping in the report.  Subsequent grouping levels can be referenced by GroupLevel(1), GroupLevel(2), and so on.

For groupings to work, the records need to be sorted the right way as well (obviously).  A grouping for "continent" would be useless if the records were sorted by country names.  So you need to use VBA to change the report's sort order as well, by changing the report's OrderBy property as shown below.  You also to need to set OrderByOn to True for the sort order to take effect.  (I believe Richard has touched on this topic in his videos, but I don't remember which.)

The OrderBy property is a string containing the field names separated by commas, in the order needed for the sort:

    Me.OrderBy = "continent,country_name"
    Me.OrderByOn = True

Lastly, you likely need your footer (or header) to show some of kind of description.  For "continent" groups, you want to show "Africa," "Asia," "Europe," in the footer.  To do that, you need a textbox in your footer that is bound to the field that is used in the grouping.  To do that in VBA:

    Me!footerfield.ControlSource = "continent"

See the pictures below for a sample report I made.  When the report opens, I have the user pick a sort order, and the report changes the grouping and sorting accordingly.


Kevin Yip       
2 years ago

Kevin Yip       
2 years ago

Kevin Yip       
2 years ago

Sami Shamma OP               
2 years ago
Thank you so much.
It worked like a charm.
this is the code I used:


Private Sub Report_Open(Cancel As Integer)
Dim vSortAlphabetically As String
Me.OrderByOn = True
vSortAlphabetically = DLookup("SortAlphabetically", "DefaultT", "DefaultID = 1")
If vSortAlphabetically Then
    Me.GroupLevel(1).ControlSource = "Full Name"
Else
    Me.GroupLevel(1).ControlSource = "Housing Code"
End If

End Sub
Sami Shamma OP               
2 years ago
Good morning
Another related question:
is there a property that 1 can set to change the number of copies from the default "1" to another number? either from the calling form or from the report itself.

many Thanks
Dr. Sami Shamma
Kevin Yip       
2 years ago
Hi Sami, you can specify the number of copies to be printed with the "DoCmd.PrintOut" method. But this only works if you have a report already opened in preview mode:

    Dim ncopies As Integer
    ncopies = 3
    DoCmd.OpenReport, "MyReportName", acViewPreview
    DoCmd.PrintOut , , , , ncopies

DoCmd.PrintOut has several settings including starting page, ending page, collated or not, etc.  The full documentation from Microsoft is here: https://learn.microsoft.com/en-us/office/vba/api/access.docmd.printout
Sami Shamma OP               
2 years ago
Kevin you are the best.
I actually only need to use the #of copies when in acViewNormal so I used this code in the calling Form and it works well:

vShowPreview = DLookup("ShowPreview", "DefaultT", "DefaultID =1")
vCopy = DLookup("Copies", "DefaultT", "DefaultID =1")

  If vShowPreview Then
      DoCmd.OpenReport "RosterR", acViewPreview, , Filter
      DoCmd.OpenReport "RosterMasterR", acViewPreview, , Filter
  Else
      DoCmd.OpenReport "RosterR", acViewNormal, , Filter
          For vCounter = 1 To vCopy
              DoCmd.OpenReport "RosterMasterR", acViewNormal, , Filter
          Next
End If

I thank you very much, My DB is looking and behaving beautifully

Have a great Holiday

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Order By, Filter On Load.
 

 
 
 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
PCResale.NET
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 11/10/2024 10:59:57 AM. PLT: 1s