PC Review


Reply
Thread Tools Rate Thread

Change Combo Box on Sub

 
 
Ann
Guest
Posts: n/a
 
      15th Jan 2010
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.
 
Reply With Quote
 
 
 
 
Marshall Barton
Guest
Posts: n/a
 
      15th Jan 2010
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]
 
Reply With Quote
 
 
 
 
Ann
Guest
Posts: n/a
 
      21st Jan 2010
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]
> .
>

 
Reply With Quote
 
Ann
Guest
Posts: n/a
 
      21st Jan 2010
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]
> .
>

 
Reply With Quote
 
Marshall Barton
Guest
Posts: n/a
 
      21st Jan 2010
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]
 
Reply With Quote
 
Ann
Guest
Posts: n/a
 
      21st Jan 2010
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]
> .
>

 
Reply With Quote
 
Marshall Barton
Guest
Posts: n/a
 
      21st Jan 2010
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]
 
Reply With Quote
 
Ann
Guest
Posts: n/a
 
      21st Jan 2010
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]
> .
>

 
Reply With Quote
 
Marshall Barton
Guest
Posts: n/a
 
      21st Jan 2010
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]
 
Reply With Quote
 
Ann
Guest
Posts: n/a
 
      25th Jan 2010
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]
> .
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Requerying combo box in sub sub form TRose Microsoft Access Form Coding 6 3rd Jun 2009 09:41 PM
Cascading Combo Box - filling a text box instead of a Combo box =?Utf-8?B?U2N1ZGE=?= Microsoft Access Forms 5 25th Jun 2007 06:46 PM
Re: Requering a Combo box on a Sub Sub form Graham Mandeno Microsoft Access Form Coding 1 11th Jan 2007 04:12 AM
how to use a combo box result in a sub form combo box / query =?Utf-8?B?UmFiYmllIFdpbGxpYW1z?= Microsoft Access 3 10th Jul 2006 07:32 PM
Combo Box - Hide Combo Box w/Check Box =?Utf-8?B?UGF1bA==?= Microsoft Excel Programming 5 3rd Dec 2004 11:58 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 10:37 PM.