Combo Box Problem–Help ASAP! (Access 2000)

Topic

New Reply

I’m having a problem with my combo boxes. I have two tables, Courses, and Modules. The Courses table has a one-to-many relationship with Modules table. I’m trying to make a form that you select a course from a drop-down box, then all the modules that pertain to that course show up in a second combo box where you select the module you want. I don’t know how to get it so that it shows only the modules that are under that course. How do I do that? I noticed another thread on this board that talked about this but it didn’t give enough detail. I managed to do a parameter query, but I wanted to be able to select from a combo box – the parameter, instead of the program prompting my for a Course. How do I do this? I need some help fast. The entry clerk needs this for tonight! EEEEEEKKKKKKKK!!!

Reply | Quote

Replies

As Charlotte explained, having chosen a value from the first combo box you need to put code in the AfterUpdate event of the first combo box that requeries the second combo box.

eg.

AfterUpdate event of the Course combo box holds the following statement

ComboModules.Requery

HTH
Pat

Reply | Quote

It’s still not working. I think I’m doing something wrong in the query. Do I enter something in the criteria of the query? The first combo box–I’m taking the values from the Courses table. The second combo box–I’m taking values from the CoursesModules Query. Is that right?

Reply | Quote

What is wrong exactly?
Are you selecting the modules based upon the course selected? ( I know I’m asking the obvious).
If you are still having problems either post me your database or I will send you an example of one.
Pat

Reply | Quote

Yes, when I select a course like Language Arts, then the second combo box should list the modules that have the Language Arts number. Ultimately I would like to have a database where I go into a form and select a school from a combo box. Then I would select a button whether the student I’m entering information for is Elementary, Junior or Senior. Then it would show the appropriate list in the first combo box (Course list for Elementary, for example), then after I select a course, it would show the modules under that course.

I do billing for distance learning, so the data entry clerk enters every students materials off of their packing slips. I then print off a report that shows all the materials, students names, and costs under each school.

I’ve attached a copy of my database.

Reply | Quote

Here a working version of you sample db

Reply | Quote

If you want the price adjust enter

Private Sub Combo12_AfterUpdate()
Me.Price = Me.Combo12.Column(2)
End Sub

in the after update event of combo12

Reply | Quote

Jennifer,

Just a couple of things I would do differently are:

a) change the names your combo boxes to something more meaningful. eg Combo8 to ComboCourse and Combo12 to ComboModule. The reason is when you are in the code you don’t have to try and figure out what combo8 and combo12 really are.

I would have changed the SQL in combo12 to filter on the CourseNum, not setup Combo12’s source in the code of Combo8. The reason is if you have to change the SQL in Combo12 you would also have to change the SQL that is setup in Combo8.

These are just my opinions.
Pat

Reply | Quote

It works! Thank you so much, Francois. I looked over the code so I know what you did. I’m just a little confused as to what the code means. I’m very new at VB. I don’t know why you have LineNo and ModuleName in quotes, then the Price is separated from them with a & _ ??? Why are they separated? And why do you put & _ , what does that do? Sorry I’m so clueless.

Reply | Quote

Hi Jennifer!

If you want to have detailed info about how to do what you now have done. Take look at Microsofts Knowledgebase article Q97624 (http://support.microsoft.com/default.aspx?scid=kb;EN-US;q97624).

// Niklas

Reply | Quote

I could have done it in one line, but to read the line I have than to scroll the window to right and to see the beginning of the line back I have to scroll to left.
So I split the line saying with a space and an underscore that the line is continued on the next line.
As VB don’t like to split in the middle of doubbel quotes, I use the & to get the two parts of the string together.
Programaticly there is no reason to split. It’s just about reading the program without scrolling from left to right, and from right to left. Of course the place where you split depends form your monitor and resolution. I try, when possible, to split where there is a space, again for ease of reading,

Reply | Quote