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

    MAKING SUB COMBO BOXES (Access 2000)

    I need help! I'm trying to make a form to enter Students for Distance Learning. I need to be able to select a Course Name (e.g. Language Arts, Math, Social, etc.) and then from what is selected it shows the appropriate Course Module under that specific Course Name (e.g. Module Book, Assignment Booklet, Video, etc.). It also should show the price when I select the Course Module. How do I do this? I'm getting myself confused, which is easy to do. So if I am sending a student a Financial Management 3060 Module Book, I need to select "Financial Management 3060" in the one combo box to get the second combo box selections. The second combo box would list the modules under that Course. I would then select "Module Book" from the list that would then put in the price for that Module Book.

    Oops, I almost forgot. I also want to be able to select which type of student it is first (Elementary, Junior or Senior). From that selection, it lists only the Elementary Courses in the Course Name combo box.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: MAKING SUB COMBO BOXES (Access 2000)

    This sort of thing does get confusing and tricky. The basic approach is to set the rowsource for the second combo box to be dependent on (i.e. the criteria for the SQL string) the value contained in the first combo box. In other words, the Student type would be selected in the first combo box, at which point you would do a .Requery on the second combo box. When a choice from it is selected, you would do the same to the third combo box. This can get pretty tricky, and often you have to resort to code.

    I've been chewing on your problem for a while, and a more intuitive approach might be to use subforms. They are complex as well, but the idea is a bit simpler, as you can simply link on the student ID number, and then limit the kind of new records that can be added based on that. Of course your table structure may not support that approach. It would help if you gave us an idea of the table design you are working with. Hope this gets some ideas going at least.
    Wendell

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

    Re: MAKING SUB COMBO BOXES (Access 2000)

    Okay, I'll explain what I'm trying to do with this whole database. I do billing for Distance Learning. They enter all the information off of student's packing slips, etc. and then run off reports that list by Jurisdiction then by school all the students names under that school, and the materials they have ordered with the price. I then bill the total for each school.

    So I wanted to design a database that would make it easier for them to enter the information. Right now they are having to look up each material in a binder to find the price. Idealy, I would like to go into a form and pick a Jurisdiction from a list, then it would have a list of that jurisdiction's Schools that I would select from. I would then type in the student's name. Then choose whether the student is Elementary, Junior or Senior. I would then enter quantity of each material, then the category of the material (e.g. Language Arts, Math, Social/Science, etc.), then choose the list of packages under that category with the price. I might have to enter more than one package for one student. At the end of the form it would be nice if it would show a total. I think that is all. I hope this helps you understand what I'm trying to do.

    I have done a little VB coding, but I am quite new at it. I have taught myself Access, but still get quite confused because I have never found a book on Access that explains every item in the properties and tells what it does, and how it effects the database. Maybe if I knew that, I might be able to understand Access better.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: MAKING SUB COMBO BOXES (Access 2000)

    Hi Jennifer
    I have had the exact problem with a Human Resources database. To explain:
    I have a series of staff whoall have different grades, PO1, PO2, PO3 etc attached to these grades is what is called a spinal point, this translates as a person at Grade PO1 has spinal points 34,35,36, a person of Grade PO2 has points of 35,36,37 (note the same values) these are are the annual increments of that staff member.
    I wanted to have a staff form that I could press their grade and then a combo underneath it would change to the relevant spinal point.

    SOLUTION
    Create a one to many relationship between two tables, tblGrade and tblSpine, create a query with both the tables fields in it.(qrySpineGrade)
    Create a combo box on a form and use tblGrade as it's look up table. This will then provided me with a combo box with all the grades in it.

    I then went to qrySpineGrade and made a parameter query that used the combo box contents

    I created a second combo and used qrySpineGrade as the look up source selecting the spine field to show.

    Once you have done this go to the first combo and write a piece of VBA to the after_update event and form![FormName]![ComboName].Refresh

    Believe it or not it does work. If you want an example give me a shout and I will send you a copy. What version are you running on?
    Jezza
    Jerry

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: MAKING SUB COMBO BOXES (Access 2000)

    OK - I think I understand your problem - sorry too about the delay in responding, as life got really hectic with a nasty snow storm we had on Friday. Anyhow . . . .
    The problem you are attempting to solve is a fairly complex one in that you are dealing with at least 3, and maybe 4 levels of relationships. The first place you want to focus is on the table and relationships design. It seems to me you may want to use linking tables in order to give you the flexibility you will eventually need. Over time the classification of a student will change, so you will need to be able to administer those changes so you can restrict the user to the right choices at any given point in time. Do you have a complete table design? If it isn't a huge database, post it and I'll have a look at it.
    Wendell

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

    Re: MAKING SUB COMBO BOXES (Access 2000)

    Yes, that would sure help if you could send me a copy. This is so complicated and confusing. But that's what the data entry person needs it to do, so...

    I'm using Access 2000.

    Mar 19/02 - Could you still send me an example? Thanks so much, Jezza!

Posting Permissions

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