Form Dependencies

[dpscott]

Hello All,

I have an "Overall" form that has many sub forms. The first sub form is a project information form. The second sub form is a sample information form.

The project information could have many sample information records, so I would like when I select a project record in the project information form, the sample information form displays only the sample records associated with that project record.





Any Help would be appreciated.

[ItsMe]

Are you familiar with the RecordSource property of Subform Controls/Containers?

Are you familiar with and able to write VBA?

[dpscott]

No to the first, and yes to the second.

[ItsMe]

I would write VBA to cause the Subforms to be dynamically dependent on the previous (parent) subform.

I would use the RecordSource property. Another option is to use the Filter property. I do not like the filter property because this property may be able to be changed by the user if they have access to intrinsic Filter tools.

There are a couple drawbacks to this approach. You need to build a where clause by concatenating the variables into a WHERE Clause. There is a limit to how many variables you can add onto a WHERE clause.

If a Subform's recordsource uses a Named Query Object you can cause the Recordsource to be Dynamicaly dependant on declared variables using something like
Me.SubFormContainer2.Recordsource = "SELECT * FROM queryname WHERE [FieldName] = " & MyLongInteger

What this approach is doing is using a standard search form technique. The entire technique is not illustrated here. Just giving you a taste to see if this may be appropriate.