MAKING SUB COMBO BOXES (Access 2000)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote