Subform query change

[Guest]

Hi, I am using Access 2000 and i have a "Meetings" form with a subform based
on a query.
The subform works fine with filter and sort criteria entered on the query. I
would like to use an Option Group on the Meetings form to change filter and
sort fields of the subform query so the results can change. How do i do
accomplish this, im not good at VBA - what are my options. thanks

 

[Pieter Wijnen]

Get better at VBA <g>

Simple Sample

Private Sub SortBy_AfterUpdate()
Dim FrmS As Access.Form

Set SF = Me.SubFormControl.Form

Select Case Me.SortBy.Value
Case 1
SF.OrderBy="MeetingDate ASC"
Case 2
SF.OrderBy="MeetingDate DESC"
End Select
SF.OrderByOn = True
End Sub

HTH

Pieter

 

[Guest]

If i was to use this, where do i attach the code, to the AfterUpdate property
of the Option Group? What about the option group value; 1, 2,3 etc.
I would like value 1 to represent a filter of "not null" for example, option
2 to filter records greater than 14 days from today etc.

So you are saying there is no other way to accomplish this other than VBA?
What about control properties, Macro's etc.
Is there a beginners guide to VBA you can reference.
What about newer versions of Access, do they make VBA code easier to develop.

 

[Pieter Wijnen]

In this case & for most other purposes VBA is the answer. I haven't had more
than one macro (AutoKeys) in any of my databases since Access 2.0 & Even
then I had to have them to run Toolbar Code.
I Guess "Access For Dummies" is probably a good book to start of with, but
others may be more helpful. I haven't had any of those since the Developer
VBA book that came with Access 1.1

And back to your original Q. yes, you' put it in the OptionGroup's after
update event

Private Sub SortByOrFilter_AfterUpdate()
Dim FrmS As Access.Form

Set SF = Me.SubFormControl.Form ' Reference the Subform through it's
*control*

Select Case Me.SortByOr Filter.Value
Case 1 ' Sort By Date
SF.FilterOn=False
SF.OrderBy="MeetingDate ASC"
SF.OrderByOn = True
Case 2 ' Sort By Date Descending
SF.FilterOn=False
SF.OrderBy="MeetingDate DESC"
SF.OrderByOn = True
Case 3 'Filter on No Date
SF.OrderByOn = False
SF.Filter = "MeetingDate Is Null"
SF.FilterOn=True
Case 4 'Filter on Future
SF.OrderByOn = False
SF.Filter = "MeetingDate>Date()+14"
SF.FilterOn=True
Case 5 'Filter on a Date
SF.OrderByOn = False
SF.Filter = "MeetingDate Is Not Null"
SF.FilterOn=True
End Select
End Sub


HTH

Pieter

 

[Guest]

Thanks Pieter, i guess i have to become an Access programmer now
Will find a good VBA book

 

[Pieter Wijnen]

de nada <g>

A lot of good code samples & links at http://www.mvps.org/Access

Pieter