Ms-access – Access 2010 – What Control Name do I use to Requery this Subform

There is really nothing that changes in terms of building a relationship between two tables. The fact that you use a GUI, a SQL DLL command, or now some relationship wizard changes nothing.

I mean who cares what the process is much to setup a relationship between two tables?

I mean, in client based you could type in something like this:

ALTER TABLE Cars
   ADD CONSTRAINT MyColorIDRelationship
   FOREIGN KEY (ColorID) REFERENCES Colors (ColorID)

So, now you use some wizard that is a few mouse clicks and this is some big deal? Really, who cares! No big woopy here, all we care is that you execute some command, or some wizard, or something here and you at the end of your day the result is your beloved relation between the two tables.

Check out the following video of mine where I "hook up" some existing related data into a web database. And I ALSO share how you can printout the related tables using the Access relationship window for a web database:

http://www.youtube.com/playlist?list=PL27E956A1537FE1C5&feature=plcp

So, at the end of the day, no need to get twisted up in some details of having to use some DDL sql command, some GUI relationships window, or some wizard. All you are doing as the above video shows is hooking up some tables and setting up a relation – nothing more, nothing less to worry about.

As for how to add child records that are to be related to a parent record? Well, in the past for the last 18 years, quite much every bird, dog, and beetle using Access would simply do this:

a) Create a main form based on the ONE main parent record table. There is NO NEED to build a query here, and in fact NO need to build a query that is a join of the two tables – this was never required and is not required. All you do is build a form based on the one simple table. We are done this part "a".

b) Create a form based on the ONE child table. There is NO NEED TO build a query here, and in fact NO need to build a query that is a join of the two tables – this was never required and is not required. All you do is build a form based on the one simple table.

c) open up first form (the form based on parent record table) and then in layout mode, now drag + drop in the child form from the nav pane.

The above a,b,c steps is how virtually EVERY parent to child setup in Access I seen done, and this long time forever setup CONTINUES to work 100% in web based applications.

In both cases (web or non web), the setup remains the same, the setup does not involve building quires, and the setup does not require ANY coding on your part.

As long as the link/master child settings are correct in the sub form control, then you are free to add child records to the child form and Access will do the rest of the dirty work of setting up and maintain the relation for you by setting the FK column in that child form for you.

So, how the basic setup works here has not changed in 18 years of using Access, and as such this does not change when building a web form here.

You don't need a query based on more than one table, and in fact JUST like in the past, the two forms will as a general rule will have their data source based only on the one table.

So in most cases there was never a need to even use a query for that one table that the form is going to be based on. This long time basic approach and setup has not change for web forms either.

In your case I would assume the main form is Consortium. You child form could be a continues form based on Principleinvegiartors. And in place of having to manually enter some PrincipleInvetigaor ID, you use a combo box based on table PrincipleInvetgioars. However, again in all cases, we are simply building forms that are based on a single base table.

I'm not sure how it's different in Access 2010 but as you can see below, for Access 2007 :

You can directly refer to a control in the Expression Builder. Just :

a) expand your form name on the left frame

b) select your subform name on the left frame

c1) If you want to refer to a specific control (i.e. a text box) simply select it from the middle frame (double click on it)

c2) If you want to refer to a form property (i.e. the subform's filter) select the tag in the middle frame, and the corresponding property in the right frame

c3) If you want to refer to an item of the subform's recordset, use the tag in the middle frame and select the item from the right frame

It should all be there.