Linking Subform

[Guest]

I have a main form with 2 subforms linked together. Main form is A, which is
linked to 1st subform B. Subform C is linked to the first text box of
Subform B. I have 5 text boxes and 1 combo box in subform B. The combo box
is the last item on subform B. The combo box is titled Stop. If the project
was stopped, you will select "Yes" from the drop down box. My question is, I
need to be able to type in multiple dates for when the project was stopped.
Because I already have a Subform tied to Subform B, how can I create
something to tie multiple dates back to the "Stop" Combo box?

 

[John W. Vinson]

I have a main form with 2 subforms linked together. Main form is A, which is
linked to 1st subform B. Subform C is linked to the first text box of
Subform B. I have 5 text boxes and 1 combo box in subform B. The combo box
is the last item on subform B. The combo box is titled Stop. If the project
was stopped, you will select "Yes" from the drop down box. My question is, I
need to be able to type in multiple dates for when the project was stopped.
Because I already have a Subform tied to Subform B, how can I create
something to tie multiple dates back to the "Stop" Combo box?

This sounds link a data storage question more than a forms question. Don't
forget - forms, subforms, and combo boxes don't contain data!!! They are just
windows, tools to manage data stored in Tables.

You probably need a new Table to store stop dates. This table won't be "tied
to the combo box"; the combo box just has two values, Yes and No. "Yes"
doesn't identify anything about which project was stopped. I presume this new
table will have a ProjectID field and a StopDate field; you could display it
as a sub-subform on subform B or as a subform on the mainform with appropriate
master/child link fields (which I can't recommend because I don't know your
structure or relationships).

John W. Vinson [MVP]

 

[Guest]

I'm not clear as to whether subform C is within subform B, i.e. B is in
single form view and C in continuous form view, or both are within the main
parent form with C correlated with the current row of B, i.e. both are in
continuous form view. If the former there is no reason why subform B should
not have two subforms embedded in it. If the latter there is similarly no
reason why there should not be a third subform in the main parent form
correlated with the current row of B.

For the first scenario, usually a subform will be embedded in and linked to
its parent form (which can be a main form or a subform in single form view)
by the relevant keys in the underlying tables, the foreign key in the
subform's table (as the LinkChildFields ) and the primary key of the parent
form's table (as the LinkMasterFields property). So, if we assume the
primary key of subform B's underlying table is ProjectID then the stop dates
would be entered in a table with a ProjectID column as a foreign key and a
date/time column for the stop dates. A subform based on this table would be
created within subform B and linked on the ProjectID columns.

With correlated subforms in a single parent form one, B in your case, is
linked to the parent form in the usual way as above. The other, C in your
case, is correlated with the first subform via a hidden control in the main
parent form which references a control in the first subform. This allows
both subforms to be in continuous form view. So a hidden control on the main
parent form would reference the ProjectID of subform B with a ControlSource
such as =sfrB.Form.ProjectID. A third correlated subform based on the table
of stop dates would have the name of the hidden text box on the main parent
form as its LinkMasterFields property and ProjectID as its LinkChildFields
property.

Whichever of the above scenarios is the case you can either hide/show or
enable/disable the stop dates subform by means of code in subform B's Current
event procedure and the AfterUpdate event procedure of its Stop combo box
depending on whether its value is 'Yes' or not. This will mean that dates
can only be entered into the stop dates subform if 'Yes' is selected. There
is the possibility that a user could select 'Yes', enter some dates, then go
back and select 'No'. Ideally you should cater for this, either by not
enabling 'Yes' to be selected if stop dates for that record have been entered
until they have been deleted, or by automatically deleting any stop dates for
that record (subject to user conformation) if 'No' is selected.

Ken Sheridan
Stafford, England