use combo box /macro to sort

[Guest]

I want to be able sort a form by two fields. I don't want to add another
command button to the form, so I thought I would use a combo box to select
the sort field and then exectute a macro on change.

So far, my attempts to exectute this have been futile. I'm able to get the
macro to run from the combo box, but I get a 'type mismatch' error. I'm sure
the problem is with the macro, since I have only a slight idea on how to
create a sorting macro.

 

[Steve Schapel]

Jpo,

I am not able to comment on the problem you have described, since you
gave no hint of what is in your combobox, or what is in your macro.

I think you could do this by using the SetValue action in a macro to
toggle the Record Source property of the form. Similarly, you could
toggle the Order By and Order By On properties of the form. Another
approach is to use an Option Group rather than a Combobox to select the
sorting field, and then use this as the basis of a Choose function
within the query that the form is based on.

 

[Guest]

Steve,

Thanks for the quick reply. I was trying to limit the info I gave as to not
confuse the matter.

The form is automatically sorted by ascending date by a query. I need to be
able to toggle back and forth between a sort by date and a sort by provider
(alphabetical). The combo box option is Date and Provider. For my latest
effort I've set the macro action for Date, to 'show all records'. For
provider I tried to apply a filter through another query. That didn't work. I
also have an option group at the bottom of the form to filter by date (last 7
days, last 15 days, etc.). I may use the same approach to solve my current
problem, but I like the look of a combo box.

I like your suggestion of using the macro to toggle the Order By and Order
By on. Do you have any specifics on how to make that happen?

 

[Steve Schapel]

Jpo,

Action: SetValue
Item: [Forms]![NameOfYourForm]![OrderBy]
Expression: ="'" & [NameOfCombobox] & "'"
Action: SetValue
Item: [Forms]![NameOfYourForm]![OrderByOn]
Expression: Yes

 

[Guest]

Steve,

I appreciate your help.

I can't seem to get that to work. I get a pop-up that has the first record
from the field I'm trying to sort by as a parameter. It's a message prompting
to enter parameter value and it lists the first record for that field. I
don't know enough about Access to fully understand what's going on, but it
seems that it's trying to sort by a record instead of the field name.

Would it be easier to tie the combo box to event procedures and use VB to
turn on "Order By"?

 

[Guest]

Disregard the last post. I was able to get it to sort correctly. The problem
is that the form that I'm sorting is a subform of another. When accessing the
subform from the main form, neither the combo box nor the option group works.
Not sure why this is, but I should be able to eliminate the need for a
subform.

Thanks again for your help.

Jpo,

Action: SetValue
Item: [Forms]![NameOfYourForm]![OrderBy]
Expression: ="'" & [NameOfCombobox] & "'"
Action: SetValue
Item: [Forms]![NameOfYourForm]![OrderByOn]
Expression: Yes

--
Steve Schapel, Microsoft Access MVP

I like your suggestion of using the macro to toggle the Order By and Order
By on. Do you have any specifics on how to make that happen?

 

[Steve Schapel]

Jpo,

Do you mean you you have the combobox on the main form, but you are
trying to sort the subform records according to the combobox selection?
Probably you are not referencing the subform correctly. The syntax
should be like this...
[Forms]![NameOfMainForm]![NameOfSubformControl].[Form].[OrderBy]

If you still can't get it, please post back with details of what you've
got in your macro.