PROBLEM WITH OPTION GROUPING (Access 2000)

Topic

New Reply

I’m trying to filter a list of Courses. On the Course table I have three fields: CourseNum, CourseName, CourseLevel. I’m trying to filter through the CourseLevel which is E for Elementary, J for Jr High, and S for Senior High. I’m using an option group to do this. After I set up the option group, I then wrote the following code:

Private Sub GradeOptions_AfterUpdate()
If GradeOptions = 3 Then
Me.Filter = “CourseLevel = ‘S'”
Me.FilterOn = True ‘Apply the filter.
Else
Me.FilterOn = False ‘Remove the filter.
End If
If GradeOptions = 2 Then
Me.Filter = “CourseLevel = ‘J'”
Me.FilterOn = True ‘Apply the filter.
Else
Me.FilterOn = False ‘Remove the filter.
End If
If GradeOptions = 1 Then
Me.Filter = “CourseLevel = ‘E'”
Me.FilterOn = True ‘Apply the filter.
Else
Me.FilterOn = False ‘Remove the filter.
End If
End Sub

This code doesn’t seem to do anything. I’ve tried a few different ways, but it always lists all of the courses. What am I doing wrong? Does anyone have any idea?

Reply | Quote

Replies

It’s all the else parts that are executed and give problems
Try these:

Private Sub GradeOptions_AfterUpdate()
Select Case GradeOptions
Case 3 
Me.Filter = "CourseLevel = 'S'"
Me.FilterOn = True 'Apply the filter.
Case 2 
Me.Filter = "CourseLevel = 'J'"
Me.FilterOn = True 'Apply the filter.
Case 1
Me.Filter = "CourseLevel = 'E'"
Me.FilterOn = True 'Apply the filter.
Case Else
Me.FilterOn = False 'Remove the filter.
End Select
End Sub

Reply | Quote

Now I’ve run into another problem. You had helped me before on my combo boxes. Do I have to make a subform? When I select the combo box selections I want it to filter my Course Table but it looks like it filters the Course table PLUS the Module table, but it still lists all the courses. It’s because my form comes from the CourseModule Query. How can I correct this? All these selection boxes are driving me crazy!

Reply | Quote

Me.Filter is a filter that apply on the forms recordset. In your case CourseModule query.
You can’t filter a combobox.
What is the name of your combobox and the rowsource ? If it is a query, please give the sql (Query in design view, select View, SQL view and copy the whole Select …). If it’s a table, what are the field names.
I’ll modify the code to requery your combobox

Reply | Quote

Here’s the code that was in SQL View.

SELECT [Sample Courses].CourseNum, [Sample Courses].CourseName, [Sample Courses].CourseLevel, [Sample Modules].LineNo, [Sample Modules].ModuleName, [Sample Modules].Price
FROM [Sample Courses] INNER JOIN [Sample Modules] ON [Sample Courses].CourseNum = [Sample Modules].CourseNum;

Thanks for all of your help!

Reply | Quote

Hi Jennifer,

Sorry I answer so late but I had to leave yesterday evening.
Here is the code.
In the 2 last line before End Sub, you’ll have to replace ComboCourseLevel with the name of your combo.

Private Sub GradeOptions_AfterUpdate()
Dim strSQL As String
Dim strWhere As String
strSQL = "SELECT [Sample Courses].CourseNum, [Sample Courses].CourseName, " & _
         "[Sample Courses].CourseLevel, [Sample Modules].LineNo, [Sample Modules].ModuleName, " & _
         "[Sample Modules].Price FROM [Sample Courses] INNER JOIN [Sample Modules] ON " & _
         "[Sample Courses].CourseNum = [Sample Modules].CourseNum"
Select Case GradeOptions
   Case 3 
      strWhere = " Where [Sample Courses].CourseLevel = 'S'"
      strSql = strSQL & StrWhere
   Case 2 
      strWhere = " Where [Sample Courses].CourseLevel = 'J'"
      strSql = strSQL & StrWhere
   Case 1
      strWhere = " Where [Sample Courses].CourseLevel = 'E'"
      strSql = strSQL & StrWhere
End Select
Me.ComboCourseLevel.RowSource = strSQL
Me.ComboCourseLevel.Requery
End Sub

Reply | Quote

Hmmm…one problem. I now get the right list of courses, but it is listing them as it is taking them from the CourseModuleQuery. For example, it lists Health, under Jr High, 21 times! It’s listing it that way because there are 21 different Modules that are under Health. Is there any way to change this?

Reply | Quote

What if you replace
strSQL = “SELECT [Sample Courses].CourseNum, [Sample Courses].CourseName, ” & _
“[Sample Courses].CourseLevel, [Sample Modules].LineNo, [Sample Modules].ModuleName, ” & _
“[Sample Modules].Price FROM [Sample Courses] INNER JOIN [Sample Modules] ON ” & _
“[Sample Courses].CourseNum = [Sample Modules].CourseNum”
by
strSQL = “SELECT [Sample Courses].CourseNum, [Sample Courses].CourseName, ” & _
“[Sample Courses].CourseLevel, [Sample Modules].LineNo, [Sample Modules].ModuleName, ” & _
“[Sample Modules].Price FROM [Sample Courses]”

Reply | Quote

Alright!!!! It works great! Thanks so much, Francois!

Reply | Quote

Another question regarding this, Francois…I want to be able to design a form where you type in the Date of the transaction, SchoolName, StudentName, then you select the level (Elementary, Jr, or Sr), then select the Course, Module, then you put in a Quantity, and a P.O. number. My question is how can I put these all on a form when the Course and the Modules are made up of a query, and the rest are off of different tables. Do I have to use a subform? If I do, how can I connect the query to the other tables?

The tables I have are: Entries (EntryNum, Date, SchoolName, StudentName, ModuleName, Quantity, and PO); Students (StudentName); School (SchoolNum, SchoolName, SchJuris); then the Sample Course, and Sample Modules tables.

I’ve made the following relationships: Many-to-Many for ModuleName from the Sample Modules table and the Entries table; Many-to-Many for StudentName from the Students table and the Entries table; and Many-to-Many for SchoolName from the School table and the Entries table.

I have just totally confused myself. I have a really hard time when there is a query involved. Let me know if you need any more info.

Reply | Quote

Have you worked this one out ?
Pat

Reply | Quote

Yes, Pat. Sorry, I should have put that up. Yes, Francois helped me out with any problems I had. Your a life saver, Francois. Thank you.

Reply | Quote