Joining forms to Sub forms (Using invisible)

Hi,

Often, when I join a form to a sub form I find I need to put invisible fields on the form to facilitate the join.  I am wondering is this the best way to do things??

EXAMPLE:
A Teacher has many students.

When I am calling a subform to display all the student names  (for one particular teacher) I find that I need to put the "TEACHERID" onto the main form and make this (numeric) field invisible.

I am happy to continue doing this but wonder if there is a less convoluted way.

(My example is not very clear but hopefully you will get the meaning)
Patrick O'DeaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Dale FyeConnect With a Mentor Commented:
You can do it a variety of ways, but basically there are two methods.

1.  Use the Link Master/Link Child fields properties in the Data tab of the Properties window.  Used when you your main form is bound to a data source

2.  Write the RecordSource of the subform so that it refers to one or more fields in your main form.  You are probably already doing this.

Either way, you are going to have to have one or more fields on your main form which define which records to show in the subform.  How are you selecting the teacher whose students you want to display?  If it is a combo or list box, modify the RowSource for the control to include the TeacherID as the first column in the query, then set the controls Control Count =2, Bound Column = 1, and Column widths to something like 0"; 2".  This will allow you to have the TeacherID in the form.

Then, if you are using option #1, the subform will probably refresh automatically.  If you are using option #2, you will have to use the AfterUpdate or Click event of the combo/list to requery the subform.

0
 
Patrick O'DeaAuthor Commented:
Thanks fyed,

Very comprehensive answer.

As I suspected, I am doing things a slightly convoluted way.  I will study you answer in full detail later.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.