Subforms (Access 2002)

Topic

New Reply

I have two subforms (but plan to add more) displayed on one mainform; no record source has been identified on the main form. Both subforms are displayed in continuous format. I want one of the subforms to display all records; however based on the record that has focus the second subform should only display the records that match the first subform. I tried setting criteria in the query of the second form using [forms]![firstform]![fieldname]; but it does not recognize this field when I open the mainform.

Reply | Quote

Replies

A subform is not part of the Forms collection, only main forms are. You must refer to a control on a subform by specifying the main form AND the subform, for example

[Forms]![frmMain]![sbfFirst]![txtSomething]

See Forms: Refer to Form and Subform properties and controls on the Access Web for a comprehensive overview.

Reply | Quote

Do you have any examples using these Controls

Reply | Quote

Many
But what are you looking for in particular?

Reply | Quote

Sorry; I thought that I had responded. I want to have one subform filter the values in another subform; when I click on the value a field in the first subform is in continuous format; I want the second subform to become visble and use the value in the first subform to filter the records displayed in the second subform in continuous format.

Reply | Quote

Here is a very simple example. It uses stripped down versions of the Categories and Products tables from the Northwins sample database that comes with Access.
The main form contains a subform based on the Categories table and a subform based on the Products table. Clicking in a category in the first one will display the associated products in the second one. The link is done through a hidden text box (its Visible property is set to No), no code is involved.

Reply | Quote

Thanks; this worked out fine; I took a look at it over the weekend and it works perfectly. Here my next question; can you changed the value of the subforms “Link child Fields” and the “Link Master Fields”; in the VBA Code based on if parameters?

Reply | Quote

Yes; the VBA names of the properties are LinkChildFields and LinkMasterFields (without spaces). Code looks like this:

Me.SubFormName.LinkChildFields = “OrderID”

(“Me” refers to the main form from which the code is run.)

Reply | Quote

Thanks all of this works great; I placed a command button on the subform to print the a report of subform values; however the report printed all the records from the table and not the records that where filter on the subform by using LinkChildFields and LinkMasterFields value.

Reply | Quote

You’ll have to tell the report in some way or other which records you want to print, it can’t smell that you want to print only those values displayed in the subform.

We’ll need to know more details if you want more specific help.

Reply | Quote