Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MSAccess, change sorting on subform based on value on listbox

Posted on 2012-03-28
8
Medium Priority
?
428 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 49

Expert Comment

by:Dale Fye
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 49

Expert Comment

by:Dale Fye
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 49

Accepted Solution

by:
Dale Fye earned 2000 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 49

Expert Comment

by:Dale Fye
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

824 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