Solved

MSAccess, change sorting on subform based on value on listbox

Posted on 2012-03-28
8
372 Views
Last Modified: 2012-03-28
Hello Experts,

I am using MSAccess 2003.
Have a list box on a form with 2 values, "CA","NV"
When list box is clicked, it filters the data on a subform. Pretty simple program.

Problem is, I need the sort order to be different if CA is selected and another way if NV is selected.

CA - sort order is CustName, City, Rep
NV - sort order is Rep, City, CustName

any ideas on how I could do this?
 
Thank you
0
Comment
Question by:jann3891
  • 4
  • 3
8 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
I assume you are using something like the following in the click event of the listbox:

me.subformcontrolname.form.Filter = "[fieldname] = " & chr$(34) & me.cboStateCode & chr$(34)
me.subformcontrolname.form.filteron = true

You should be able to simply add the following:

IF me.cboStateCode = "CA" Then
    me.subformcontrolname.form.OrderBy = "[CustName], [City], [Rep]"
else
    me.subformcontrolname.form.OrderBy = "[Rep], [City], [CustName]"
endif
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
you need to add this line

  me.subformcontrolname.form.OrderByOn=true
0
 

Author Comment

by:jann3891
Comment Utility
Thanks Fyed,  for some reason, the OrderBy is not coming up as I type it. I typed it in anyway. but when I tried to run it, I get Compile error: Method or data memeber not found.
what am I doing wrong?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
jann,

Capricorn1 is right, I forgot the OrderByOn = True line, but that is not what is causing your compile error.

Can you post the code you are using to both filter the form and the ordering.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:jann3891
Comment Utility
The subform uses a query as the control source.
so, the onclick event of the list box just have 'refresh'
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
Comment Utility
Then what is the name of the subform "CONTROL"?

Open the main form in design view, click on the subform, then look at the Name property on the Other tab.  If that value is "mySub" then the syntax would be

me.mySub.form.orderby = "[CustName], [City], [Rep]"
me.mysub.form.orderbyon = true

Many people confuse the subform control name with the subform source object (which is the actual name of the form that is used in the subform control.
0
 

Author Comment

by:jann3891
Comment Utility
That did it.. thank you very much.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Glad to help.

C1 deserves some of those points, as without his reminder regarding the OrderByOn property, the OrderBy property would not have worked.

to redistribute points, just click the request attention hyperlink (bottom right corner of your original post) and ask the moderators to reopen the question for redistribution of points.

Dale
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now