• QUERIES WITHIN A FORM (Access 2000)

    Author
    Topic
    #369305

    I started off designing part of my form where I would select a grade level (Elementary, Jr, or Sr). Then the first combo box would show the Courses listed under that grade level. When I select the Course, the next combo box shows the Modules that are listed under that Course along with the price. That works fine now, but I have totally confused myself with the rest of the form.

    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 Courses, and 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.

    Viewing 0 reply threads
    Author
    Replies
    • #581155

      All you have to do is create a form using the Entry table and use comboxes for the fields where the data is coming from the other tables.

      • #581177

        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.

        • #581313

          Can you attache the mdb so I can have a look at it ?

          • #581356

            I can’t attach it because it is too big. Do you want me to e-mail it to you. I know you might want to give out your e-mail address. If not, maybe I could put it up on my office’s web site for you to download. Would that work? Let me know.

            • #581357

              Send it to me directly. My e-mail is in my profile.

            • #581378

              I have to change your tables to make it working.
              When you work with lookup tables, you don’t store the name of the lookup but the ID numbers.
              Try to do this with the schools and the students also.
              In the table entry I changed CourseNum and ModulesNum to numeric fields.
              I also had to add a field GradeLevel to store the Grade Level.
              I rework your combo boxes row sources and the code in the after update events. These code has also to be invoked form the on current event of the form.
              I rework you testform to test2form with working combo’s.
              I added also query1 to show how to make a query that give the names of the courses and modules when you work with the ID Numbers.

    Viewing 0 reply threads
    Reply To: QUERIES WITHIN A FORM (Access 2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: