Change Combo Box on Sub

[Ann]

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.

 

[Marshall Barton]

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

 

[Ann]

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.

 

[Ann]

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]

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

 

[Ann]

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]

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.

 

[Ann]

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

 

[Marshall Barton]

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

 

[Ann]

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.