Solved

MSAccess, change sorting on subform based on value on listbox

Posted on 2012-03-28
8
411 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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
 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

749 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