Using Option group with a query

[Phil]

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 before:

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

 

[John Spencer (MVP)]

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]

Thanks John

I took the filter route and it works great

Thanks again for the answer and the explanation

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

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 before:

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