Replies

I created my form using the Entries table. Then I made combo boxes that referred to the CourseModuleQuery. The problem is…I typed in all the code you had for the CourseCombo box, ModuleCombo box, and the GradeOptions option group. But whenever I select a grade level from the GradeOptions option group, it asks me to Enter Parameter Value for Sample Modules.LineNo. What does this mean? I went over the code several times and I can’t see where there is any errors. Do you have any idea what I did wrong?

I’m thinking it has something to do with the fact that the form is now made from the Entries table instead of the CourseModuleQuery.

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.

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

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?

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!

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!

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.

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.

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?

I’m having the same problem with my combo boxes. But I don’t know what you mean by “using the first combobox as the criteria for the saved query that populates the second combobox”. Can you explain? I have a query that takes all the fields from both tables (Courses table, and the Modules table). I have courses (e.g. Language Arts, Math, Social Studies, etc.), and then when you select a course the next combo box should show the modules that are listed under that course.

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!

Thanks Kathi! It works great!

Jennifer

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.

It has taken me a while before I could get back to working on this program…anyway, I don’t understand how I would use the DSum. I read the help file, and I just don’t understand what kind of data I’m suppose to put in there. And…can I pull things from another form? Do I put the DSum on the Applied Subform and pull data from the Apply Amount form? Remember, I have a Payments form that has an Applied subform and a button that goes to the Apply Amount form. This program is really driving me nuts!!!

But its another form that I’m referring to from a Subform. I’m trying to pull the totals from my ApplyAmount form which is a button on the Applied Subform. From the Applied Subform there is an Apply button that opens the ApplyAmount form. So when I close the ApplyAmount form, I want the totals to go to the Applied Subform. I hope you know what I mean.