Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    PROBLEM WITH OPTION GROUPING (Access 2000)

    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?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PROBLEM WITH OPTION GROUPING (Access 2000)

    It's all the else parts that are executed and give problems
    Try these:
    <pre>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</pre>

    Francois

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PROBLEM WITH OPTION GROUPING (Access 2000)

    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!

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PROBLEM WITH OPTION GROUPING (Access 2000)

    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
    Francois

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PROBLEM WITH OPTION GROUPING (Access 2000)

    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!

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PROBLEM WITH OPTION GROUPING (Access 2000)

    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.
    <pre>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</pre>

    Francois

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PROBLEM WITH OPTION GROUPING (Access 2000)

    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?

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PROBLEM WITH OPTION GROUPING (Access 2000)

    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]"
    Francois

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PROBLEM WITH OPTION GROUPING (Access 2000)

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

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PROBLEM WITH OPTION GROUPING (Access 2000)

    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.

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: PROBLEM WITH OPTION GROUPING (Access 2000)

    Have you worked this one out ?
    Pat

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PROBLEM WITH OPTION GROUPING (Access 2000)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •