MSAccess, change sorting on subform based on value on listbox

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
jann3891Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Dale FyeConnect With a Mentor Commented:
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
 
Dale FyeCommented:
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
 
Rey Obrero (Capricorn1)Commented:
you need to add this line

  me.subformcontrolname.form.OrderByOn=true
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
jann3891Author Commented:
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
 
Dale FyeCommented:
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
 
jann3891Author Commented:
The subform uses a query as the control source.
so, the onclick event of the list box just have 'refresh'
0
 
jann3891Author Commented:
That did it.. thank you very much.
0
 
Dale FyeCommented:
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
All Courses

From novice to tech pro — start learning today.