Solved

MSAccess, change sorting on subform based on value on listbox

Posted on 2012-03-28
8
382 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)
ID: 37778147
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
ID: 37778190
you need to add this line

  me.subformcontrolname.form.OrderByOn=true
0
 

Author Comment

by:jann3891
ID: 37778201
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)
ID: 37778226
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:jann3891
ID: 37778247
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
ID: 37778282
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
ID: 37778316
That did it.. thank you very much.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37778340
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

910 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

25 Experts available now in Live!

Get 1:1 Help Now