Change Combo Box on Sub

Discussion in 'Microsoft Access Form Coding' started by Ann, Jan 15, 2010.

  1. Ann

    Ann Guest

    I am using Access 2002. I'm creating a database for education and I created
    a main form for Vendors and linked a subform for Courses they offer. Some
    Vendors only offer one class but some have as many as 75. When I choose the
    Vendor on my main form all of the Courses appear in the subform on a single
    form. For those that have only one it's not a problem but for those that
    have 75 I have to use the navigation buttons to look through them. I added a
    combo box but the list has all the Courses, 208, for all the Vendors. I
    would just like to see the classes that go with the Vendor I picked from the
    main forms combo box. This way if I pick the Vendor with the 75 Courses I
    will only see those 75 and not all 208. Since the sub is linked to the main
    by the lngVendorID I want to somehow tell the subs combo box that the
    tblVendors.lngVendorID equals the tblCourses.lngVendorID but I'm not sure how
    or where to put this. Can anyone help me? Thanks in advance.
     
    Ann, Jan 15, 2010
    #1
    1. Advertisements

  2. Ann wrote:

    >I am using Access 2002. I'm creating a database for education and I created
    >a main form for Vendors and linked a subform for Courses they offer. Some
    >Vendors only offer one class but some have as many as 75. When I choose the
    >Vendor on my main form all of the Courses appear in the subform on a single
    >form. For those that have only one it's not a problem but for those that
    >have 75 I have to use the navigation buttons to look through them. I added a
    >combo box but the list has all the Courses, 208, for all the Vendors. I
    >would just like to see the classes that go with the Vendor I picked from the
    >main forms combo box. This way if I pick the Vendor with the 75 Courses I
    >will only see those 75 and not all 208. Since the sub is linked to the main
    >by the lngVendorID I want to somehow tell the subs combo box that the
    >tblVendors.lngVendorID equals the tblCourses.lngVendorID but I'm not sure how
    >or where to put this.


    You can modify the combo box's row source query to use a
    criteria such as Forms!themainform.lngVendorID on the
    course vendor ID field.

    Then, you also need to add the line:
    Me.subformcontrol.Form.thecombobox.Requery
    whenever the vendor value changes via navigating (main
    form's Current event) AND via edit/enter the vendor control
    (control's AfterUpdate event).

    --
    Marsh
    MVP [MS Access]
     
    Marshall Barton, Jan 15, 2010
    #2
    1. Advertisements

  3. Ann

    Ann Guest

    Hi,

    Thanks for the help but it's not working for me. When I add lngVendorID
    from the main form to the combo boxes query and try to run the query I get an
    error that closes the entire database.

    "Marshall Barton" wrote:

    > Ann wrote:
    >
    > >I am using Access 2002. I'm creating a database for education and I created
    > >a main form for Vendors and linked a subform for Courses they offer. Some
    > >Vendors only offer one class but some have as many as 75. When I choose the
    > >Vendor on my main form all of the Courses appear in the subform on a single
    > >form. For those that have only one it's not a problem but for those that
    > >have 75 I have to use the navigation buttons to look through them. I added a
    > >combo box but the list has all the Courses, 208, for all the Vendors. I
    > >would just like to see the classes that go with the Vendor I picked from the
    > >main forms combo box. This way if I pick the Vendor with the 75 Courses I
    > >will only see those 75 and not all 208. Since the sub is linked to the main
    > >by the lngVendorID I want to somehow tell the subs combo box that the
    > >tblVendors.lngVendorID equals the tblCourses.lngVendorID but I'm not sure how
    > >or where to put this.

    >
    > You can modify the combo box's row source query to use a
    > criteria such as Forms!themainform.lngVendorID on the
    > course vendor ID field.
    >
    > Then, you also need to add the line:
    > Me.subformcontrol.Form.thecombobox.Requery
    > whenever the vendor value changes via navigating (main
    > form's Current event) AND via edit/enter the vendor control
    > (control's AfterUpdate event).
    >
    > --
    > Marsh
    > MVP [MS Access]
    > .
    >
     
    Ann, Jan 21, 2010
    #3
  4. Ann

    Ann Guest

    Well, I got the row source query to work. It works once which I expected
    since I didn't do the other part yet. Since I asked the question I've had to
    add the subform frmCourses to a TabCtl so now I'm confused as to how to write
    the Requery part.

    I have a main form called frmVendors, a TabCtl called Courses (it's Page
    Index is 1), a subform called frmCourses, and a Combo Box called
    cmbCourseSearch.

    I've tried writing it as Me.Courses.frmCourses.cmbCourseSearch.Requery. I
    get a compile error that stops on frmCourses with the following message:
    Method or data member not found. Any help you can offer would be greatly
    appreciated.


    "Marshall Barton" wrote:

    > Ann wrote:
    >
    > >I am using Access 2002. I'm creating a database for education and I created
    > >a main form for Vendors and linked a subform for Courses they offer. Some
    > >Vendors only offer one class but some have as many as 75. When I choose the
    > >Vendor on my main form all of the Courses appear in the subform on a single
    > >form. For those that have only one it's not a problem but for those that
    > >have 75 I have to use the navigation buttons to look through them. I added a
    > >combo box but the list has all the Courses, 208, for all the Vendors. I
    > >would just like to see the classes that go with the Vendor I picked from the
    > >main forms combo box. This way if I pick the Vendor with the 75 Courses I
    > >will only see those 75 and not all 208. Since the sub is linked to the main
    > >by the lngVendorID I want to somehow tell the subs combo box that the
    > >tblVendors.lngVendorID equals the tblCourses.lngVendorID but I'm not sure how
    > >or where to put this.

    >
    > You can modify the combo box's row source query to use a
    > criteria such as Forms!themainform.lngVendorID on the
    > course vendor ID field.
    >
    > Then, you also need to add the line:
    > Me.subformcontrol.Form.thecombobox.Requery
    > whenever the vendor value changes via navigating (main
    > form's Current event) AND via edit/enter the vendor control
    > (control's AfterUpdate event).
    >
    > --
    > Marsh
    > MVP [MS Access]
    > .
    >
     
    Ann, Jan 21, 2010
    #4
  5. Ann wrote:
    >Well, I got the row source query to work. It works once which I expected
    >since I didn't do the other part yet. Since I asked the question I've had to
    >add the subform frmCourses to a TabCtl so now I'm confused as to how to write
    >the Requery part.
    >
    >I have a main form called frmVendors, a TabCtl called Courses (it's Page
    >Index is 1), a subform called frmCourses, and a Combo Box called
    >cmbCourseSearch.
    >
    >I've tried writing it as Me.Courses.frmCourses.cmbCourseSearch.Requery. I
    >get a compile error that stops on frmCourses with the following message:
    >Method or data member not found. Any help you can offer would be greatly
    >appreciated.



    Important: A tab control has nothing to do with how
    a control on a tab page is referenced.

    You need to use a subform control's Form property to get to
    the controls in the form displayed in the subform control.

    Try using:
    Me.frmCourses.FORM.cmbCourseSearch.Requery

    --
    Marsh
    MVP [MS Access]
     
    Marshall Barton, Jan 21, 2010
    #5
  6. Ann

    Ann Guest

    I'm having a hard time with my posts appearing so I apologize if this is out
    there twice.

    Well, I got the row source query to work. It works once which I expected
    since I didn't do the other part yet. Since I asked the question I've had to
    add the subform frmCourses to a TabCtl so now I'm confused as to how to write
    the Requery part.

    I have a main form called frmVendors, a TabCtl called Courses (it's Page
    Index is 1), a subform called frmCourses, and a Combo Box called
    cmbCourseSearch.

    I've tried writing it as Me.Courses.frmCourses.cmbCourseSearch.Requery. I
    get a compile error that stops on frmCourses with the following message:
    Method or data member not found. Any help you can offer would be greatly
    appreciated.

    "Marshall Barton" wrote:

    > Ann wrote:
    >
    > >I am using Access 2002. I'm creating a database for education and I created
    > >a main form for Vendors and linked a subform for Courses they offer. Some
    > >Vendors only offer one class but some have as many as 75. When I choose the
    > >Vendor on my main form all of the Courses appear in the subform on a single
    > >form. For those that have only one it's not a problem but for those that
    > >have 75 I have to use the navigation buttons to look through them. I added a
    > >combo box but the list has all the Courses, 208, for all the Vendors. I
    > >would just like to see the classes that go with the Vendor I picked from the
    > >main forms combo box. This way if I pick the Vendor with the 75 Courses I
    > >will only see those 75 and not all 208. Since the sub is linked to the main
    > >by the lngVendorID I want to somehow tell the subs combo box that the
    > >tblVendors.lngVendorID equals the tblCourses.lngVendorID but I'm not sure how
    > >or where to put this.

    >
    > You can modify the combo box's row source query to use a
    > criteria such as Forms!themainform.lngVendorID on the
    > course vendor ID field.
    >
    > Then, you also need to add the line:
    > Me.subformcontrol.Form.thecombobox.Requery
    > whenever the vendor value changes via navigating (main
    > form's Current event) AND via edit/enter the vendor control
    > (control's AfterUpdate event).
    >
    > --
    > Marsh
    > MVP [MS Access]
    > .
    >
     
    Ann, Jan 21, 2010
    #6
  7. Ann wrote:

    >I'm having a hard time with my posts appearing so I apologize if this is out
    >there twice.



    Can you see my reply to your past that you can't see?

    It sounds like you are using a web interface to these
    newsgroups and the interface is having its usual
    difficulties. If so, I strongly recommend that you use a
    standard mail program such as Outlook Express to connect
    directly to the newsgroups.

    --
    Marsh
    MVP [MS Access]
     
    Marshall Barton, Jan 21, 2010
    #7
  8. Ann

    Ann Guest

    I'm having a hard time with my replies posting so if this is a repeat I
    apologize.

    I made the change to the row source for the combo box and will see the
    correct data the first time I click the dropdown. I would expect this
    because I haven't done the second and third part yet. Since I posted I've
    had to add the subform to a Control Tab and I'm having a hard time with the
    following: Me.subformcontrol.Form.thecombobox.Requery.

    I have a main form called frmVendors, a Combo Box on it called
    cboVendorSearch, a Tab Control called TabCtl9 with a Page called Courses
    (it's Page Index is 1), a subform called frmCourses on the Tab Control, and a
    Combo Box on the subform called cmbCourseSearch.

    When the cboVendorSearch changes it should only show the Courses in the
    cboCourseSearch for that one vendor. I'm confused on how to get
    Me.subformcontrol.Form.thecombobox.Requery to work now that I have a Tab
    Control.

    I've tried many different ways to write this and place it on the On Current
    of the main form and the cboVendorSearch AfterUpdate. Can anyone tell me how
    to do this.

    Also, is there information anywhere stating how to reference other controls
    on other subforms and also those that are on Tab Controls? I see so many
    different ways of referencing I'm very confused.


    "Marshall Barton" wrote:

    > Ann wrote:
    >
    > >I am using Access 2002. I'm creating a database for education and I created
    > >a main form for Vendors and linked a subform for Courses they offer. Some
    > >Vendors only offer one class but some have as many as 75. When I choose the
    > >Vendor on my main form all of the Courses appear in the subform on a single
    > >form. For those that have only one it's not a problem but for those that
    > >have 75 I have to use the navigation buttons to look through them. I added a
    > >combo box but the list has all the Courses, 208, for all the Vendors. I
    > >would just like to see the classes that go with the Vendor I picked from the
    > >main forms combo box. This way if I pick the Vendor with the 75 Courses I
    > >will only see those 75 and not all 208. Since the sub is linked to the main
    > >by the lngVendorID I want to somehow tell the subs combo box that the
    > >tblVendors.lngVendorID equals the tblCourses.lngVendorID but I'm not sure how
    > >or where to put this.

    >
    > You can modify the combo box's row source query to use a
    > criteria such as Forms!themainform.lngVendorID on the
    > course vendor ID field.
    >
    > Then, you also need to add the line:
    > Me.subformcontrol.Form.thecombobox.Requery
    > whenever the vendor value changes via navigating (main
    > form's Current event) AND via edit/enter the vendor control
    > (control's AfterUpdate event).
    >
    > --
    > Marsh
    > MVP [MS Access]
    > .
    >
     
    Ann, Jan 21, 2010
    #8
  9. Marshall Barton, Jan 21, 2010
    #9
  10. Ann

    Ann Guest

    Hi Marshall,

    I hope this post appears. Since my posts weren't working I Googled looking
    for help and found information about TabCtls that stated exactly what you
    have written.
    I managed to get everything working properly andwanted to thank you for your
    help, it was greatly appreciated.

    "Marshall Barton" wrote:

    > Ann wrote:
    > [Snip copy of previous identical posts]
    > ... I'm having a hard time with the following:
    > Me.subformcontrol.Form.thecombobox.Requery
    > ...
    >
    > http://www.mvps.org/access/forms/frm0025.htm
    >
    >
    > --
    > Marsh
    > MVP [MS Access]
    > .
    >
     
    Ann, Jan 25, 2010
    #10
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. XMan

    Combo Box Depending on Combo Box

    XMan, Oct 15, 2003, in forum: Microsoft Access Form Coding
    Replies:
    4
    Views:
    253
    Adrian Jansen
    Oct 17, 2003
  2. Guest

    main form, sub form, sub sub form ...etc

    Guest, Oct 31, 2006, in forum: Microsoft Access Form Coding
    Replies:
    8
    Views:
    493
    Guest
    Nov 5, 2006
  3. Graham Mandeno

    Re: Requering a Combo box on a Sub Sub form

    Graham Mandeno, Jan 11, 2007, in forum: Microsoft Access Form Coding
    Replies:
    1
    Views:
    178
    Guest
    Jan 11, 2007
  4. TRose

    Requerying combo box in sub sub form

    TRose, Jun 2, 2009, in forum: Microsoft Access Form Coding
    Replies:
    6
    Views:
    4,008
    Marshall Barton
    Jun 3, 2009
  5. Renraf

    Call a Public Sub-routine on a Sub-sub-form

    Renraf, Nov 3, 2009, in forum: Microsoft Access Form Coding
    Replies:
    2
    Views:
    640
    Klatuu
    Nov 4, 2009
Loading...

Share This Page