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

    QUERIES WITHIN A FORM (Access 2000)

    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.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: QUERIES WITHIN A FORM (Access 2000)

    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.
    Francois

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

    Re: QUERIES WITHIN A FORM (Access 2000)

    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.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: QUERIES WITHIN A FORM (Access 2000)

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

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

    Re: QUERIES WITHIN A FORM (Access 2000)

    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.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: QUERIES WITHIN A FORM (Access 2000)

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

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: QUERIES WITHIN A FORM (Access 2000)

    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.
    Francois

Posting Permissions

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