Microsoft Access: Conditional Lookup

Question

I have an excel file that has a separate sheet for 7th grade, 8th grade, and 11th grade.  On each of these sheets, there is a list of categories in column A and a list of subcategories in column B.  It looks like this (but has more data):

Whole Numbers	Operations with Whole Numbers
	Whole Number Addition and Subtraction
	Whole Number Multiplication and Division
Number Sense	Factors and Multiples
	Greatest Common Factor
	Least Common Multiple
Fraction Operations	Adding Fraction
	Subtracting Frations
	Multiplying Fractions
	Dividing Fractions
	Operations with Fractions
Decimals	Operations with Decimals

What I want to do is create a database where you can insert the student's grade and based on that number, the list of categories in the drop-down box changes appropriately.  Based on the category chosen, the list of subcategories in its drop-down box changes accordingly.  My end goal is to make it so that I do not have to sort through all of the categories (including ones that may not pertain to the specific grade) and all of the subcategories that do not pertain to the category chosen.

Thanks.

Answer 1

jmisback:

To give you a little more background on my database, I have three grades - 7th, 8th, and 11th.  Each grade has a list of courses and each course has a list of threads.  For example, a 7th grade course is Whole Numbers.  Its threads are Whole Number Addition and Subtraction, Whole Number Multiplication and Division, and Operations with Whole Numbers.  

I created a table called "Grades"  that has one field called "Grade" and three records 7, 8, and 11.  I then created 3 tables named"7th Grade Courses", "8th Grade Courses", and "11th Grade Courses."  Each of those tables has one field called "Course" and all the records are all the courses for that specific grade (Whole Numbers (7th), Number Sense (7th), Percent (7th), etc).  I then created a table for each individual course so I could list its threads.  This is probably the long way to do it, but it was the easiest for me to understand.  So, for example, I have a table titled "7th Whole Numbers Threads" and it contains one field called "Thread" and its records are "Whole Number Addition and Subtraction", "Whole Number Multiplication and Division", and "Operations with Whole Numbers."

I also have a table with a form created off of it.  This table/form has several fields on it, but the only important ones for right now are "Grade", "Course", and "Thread", which are all Combo Boxes.  Go to Design view for the Form and click the box for the first field that affects the outcome.  For me, it was "Grade."  Bring up the Property Sheet and under Event choose After Update and Choose Code Builder.  The code is as follows:

On Error Resume Next

    Select Case Grade.Value

        Case "7"

            Course.RowSource = "7th Grade Courses"

        Case "8"

            Course.RowSource = "8th Grade Courses"

        Case "11"

            Course.RowSource = "11th Grade Courses"

    End Select

This sets it so that if I choose grade 7, the only courses it will bring up are those listed on my "7th Grade Courses" table.

Next, do the same process for the Courses Combo Box.  The code for it is:

    On Error Resume Next

    Select Case Course.Value

Case "Volume (7th)"

            Thread.RowSource = "7th Volume Threads"

        Case "Whole Numbers (7th)"

            Thread.RowSource = "7th Whole Numbers Threads"

     End Select

I have a lot of code typed in the after update for Courses because you have to have each course listed - all the 7th, 8th, and 11th.  You may have noticed in the code example that I had the grade listed after the course name.  This was because the same course may appear for more than one grade, but the threads are different for each grade.

I think that should be all you need, let me know if you need more help.  I'm not sure how much help I can be since I haven't really used Access much, but I'll do the best I can ;)

Tiffany

Answer 2

Tiffany,

What is the other way?   I want to do something similiar.   A simple two dependent drop down lists.

Thanks!

JMe

Answer 3

Bill,

I actually ended up doing it a different way, but thank you for your help.

Answer 4

Tiffany

What you are asking for is called cascading combo boxes. You use the first one

to select the category. You put code in the AfterUpdate event of that combo box

to change the Rowsource of the subcategory.

Let's say the Categories table has two fields:

    CategoryID autonumber primary key

    Category text

The SubCategoryies table has:

    SubCategory ID AutoNumber Primary Key

    CategoryID Long (foreign key to Categories table)

    SubCategory Text

The first combo has the categories and has the CategoryID as its bound column.

An example would look like this:

[[vbnet]

Private Sub cboCategoryID_AfterUpdate()

    Dim strSQL As String

    strSQL = "SELECT SubCategoryID, SubCategory " _

        & "FROM tblSubCategories " _

        & "WHERE CategoryID = " & me.cboCategoryID

    Me.cboSubCategory.RowSource = strSQL

    Me.cboSubCategory.Requery

End Sub

[/vbnet]

---

Bill Mosca, MS Access MVP

http://www.thatlldoit.com

http://mvp.support.microsoft.com/profile/Bill.Mosca

http://tech.groups.yahoo.com/group/MS_Access_Professionals

<tiffany_72287> wrote in message

news:*** Email address is removed for privacy ***...

I have an excel file that has a separate sheet for 7th grade, 8th grade, and

11th grade.  On each of these sheets, there is a list of categories in column A

and a list of subcategories in column B.  It looks like this (but has more

data):

     Whole Numbers Operations with Whole Numbers

    Whole Number Addition and Subtraction

    Whole Number Multiplication and Division

     Number Sense Factors and Multiples

    Greatest Common Factor

    Least Common Multiple

     Fraction Operations Adding Fraction

    Subtracting Frations

    Multiplying Fractions

    Dividing Fractions

    Operations with Fractions

     Decimals Operations with Decimals

What I want to do is create a database where you can insert the student's

grade and based on that number, the list of categories in the drop-down box

changes appropriately.  Based on the category chosen, the list of

subcategories in its drop-down box changes accordingly.  My end goal is to

make it so that I do not have to sort through all of the categories (including

ones that may not pertain to the specific grade) and all of the subcategories

that do not pertain to the category chosen.

Thanks.