Using Option group with a query

Apologies if you have already seen this question I posted it yesterday but
could not find it this morning.

I have a form in a A2000 database that shows course details at the moment I
have it set to only show future courses by using Date() in the startdate. I
would like to have an option group to be able to switch between all courses
and future courses.

I have never used an option group befo

I created an option group called [Courses] with two options 1. Future
Courses 2. Alll Courses.

In the After update event for courses I have put:

Select Case Me!Courses
Case 1
Me!Courses = "Date()"

Case 2
Me!Courses = ""

End Select

I have also put this in the on load event for the form. and in the query
the form is based on under startdate I have added

=Forms!Events!Courses

This does nothing at all the form loads with no data and has does not change
if you change the option group

I hope someone can help

Thanks

Phil

You can't set the value of an Option group to "" or "Date()" set an option
group stores number values (notice that your code is testing if the option
group is equal to 1 or 2).

What you could do is set a filter on the form if your query returns all
records or you could change the query.

Private Sub Courses_AfterUpdate()

Select Case Me!Courses
Case 1
Me.Filter = "[DateField]Date()"
Me.FilterOn = True

Case 2
Me.Filter = "[DateField]Date()"
Me.FilterOn = True
End Sub

An alternative would be to set the query up with this criteria (assuming that
your date field always has a value

Field: [DateField]
Criteria: IIF(Forms!Events!Courses=1,Date(),#1900-01-01#)

If the date field is sometimes blank (null) then enter the criteria as
Field: [DateField]
Criteria: Date() or Forms!Events!Courses=2
Access will restructure the above when you save the query, but the query
should still work.

Your after update code would then become simpler
Private Sub Courses_AfterUpdate()
Me.Requery
End Sub


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Phil wrote:
Apologies if you have already seen this question I posted it yesterday but
could not find it this morning.

I have a form in a A2000 database that shows course details at the moment I
have it set to only show future courses by using Date() in the startdate. I
would like to have an option group to be able to switch between all courses
and future courses.

I have never used an option group befo

I created an option group called [Courses] with two options 1. Future
Courses 2. Alll Courses.

In the After update event for courses I have put:

Select Case Me!Courses
Case 1
Me!Courses = "Date()"

Case 2
Me!Courses = ""

End Select

I have also put this in the on load event for the form. and in the query
the form is based on under startdate I have added

=Forms!Events!Courses

This does nothing at all the form loads with no data and has does not change
if you change the option group

I hope someone can help

Thanks

Phil

Thanks John

I took the filter route and it works great

Thanks again for the answer and the explanation

Phil

"John Spencer (MVP)" wrote:

You can't set the value of an Option group to "" or "Date()" set an option
group stores number values (notice that your code is testing if the option
group is equal to 1 or 2).

What you could do is set a filter on the form if your query returns all
records or you could change the query.

Private Sub Courses_AfterUpdate()

Select Case Me!Courses
Case 1
Me.Filter = "[DateField]Date()"
Me.FilterOn = True

Case 2
Me.Filter = "[DateField]Date()"
Me.FilterOn = True
End Sub

An alternative would be to set the query up with this criteria (assuming that
your date field always has a value

Field: [DateField]
Criteria: IIF(Forms!Events!Courses=1,Date(),#1900-01-01#)

If the date field is sometimes blank (null) then enter the criteria as
Field: [DateField]
Criteria: Date() or Forms!Events!Courses=2
Access will restructure the above when you save the query, but the query
should still work.

Your after update code would then become simpler
Private Sub Courses_AfterUpdate()
Me.Requery
End Sub


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Phil wrote:
Apologies if you have already seen this question I posted it yesterday but
could not find it this morning.

I have a form in a A2000 database that shows course details at the moment I
have it set to only show future courses by using Date() in the startdate. I
would like to have an option group to be able to switch between all courses
and future courses.

I have never used an option group befo

I created an option group called [Courses] with two options 1. Future
Courses 2. Alll Courses.

In the After update event for courses I have put:

Select Case Me!Courses
Case 1
Me!Courses = "Date()"

Case 2
Me!Courses = ""

End Select

I have also put this in the on load event for the form. and in the query
the form is based on under startdate I have added

=Forms!Events!Courses

This does nothing at all the form loads with no data and has does not change
if you change the option group

I hope someone can help

Thanks

Phil