Change caption - Datasheet Subform

[Gary Dolliver]

Hi all,
I am looking to create a dynamic subform, that when the main form is loaded,
the captions and fields of the subform will adjust, based on a field
(Order_Product_ID) in the table (Orders) that controls the main form. The
subform is contolled by another table (Items), which links to the Orders
table and main form through the field Order_ID.
Based on which Order_Product_ID is loaded into the main form, I would like
the captions and fields to change. For example, if the Order_Product_ID is
1, I would like Caption1 to be "Step1" and would link to the field Step1 in
the Items table. If Order_Product_ID is 2, then Caption1 would read "Step5"
and link to the field Step5 in the Items table. There would then be many
other options put in as well.
I have tried using the onLoad event for the subform, based on a lookup of
the Order_Product_ID, but have not yet gotten it to work.
Any thoughts on this? Help is appreciated, thanks!
-gary

 

[kingston via AccessMonster.com]

Have you considered just copying the subform 5 times, making the changes, and
then changing the subform control's Source Object? In essence, the subform
would be changed based on the Order_ID. You could also use a tab control
with 5 tabs (and subforms) - one for each step.

 

[Gary Dolliver]

Hi,
Thanks for the advice. Unfortunately, it would not quite work as I would
end up needing to create about a hundred subforms as there are many items
that all go through separate steps. Is there any hope for me?
Thanks again
-gary

 

[kingston via AccessMonster.com]

OK, back to your original question. If you really want to do it this way, I
suggest that you use the main form's OnCurrent event to check
Order_Product_ID and then make appropriate adjustments to the subform.

Hi,
Thanks for the advice. Unfortunately, it would not quite work as I would
end up needing to create about a hundred subforms as there are many items
that all go through separate steps. Is there any hope for me?
Thanks again
-gary

Have you considered just copying the subform 5 times, making the changes, and
then changing the subform control's Source Object? In essence, the subform

[quoted text clipped - 17 lines]

 

[Gary Dolliver]

Hi again,
Thanks for your replies. Unfortunately, I am still unsuccessful and am not
sure how to go about this.
On the subform, I tried using an onLoad to check for the Order_Product_ID
and then change the caption and linking field, but have had no luck - also
not getting any error messages - only the original sub form appears.
So I tried to do it the way you first suggested. However, I am not sure
what to write in the Control Source for the correct subform to be determined.
I have tried using an "=IIF" statement, but keep getting errors in regards
to the forms not following object naming rules.
Can you please possibly provde some sample code for each example?
Thank you again!
-gary

OK, back to your original question. If you really want to do it this way, I
suggest that you use the main form's OnCurrent event to check
Order_Product_ID and then make appropriate adjustments to the subform.

Hi,
Thanks for the advice. Unfortunately, it would not quite work as I would
end up needing to create about a hundred subforms as there are many items
that all go through separate steps. Is there any hope for me?
Thanks again
-gary

Have you considered just copying the subform 5 times, making the changes, and
then changing the subform control's Source Object? In essence, the subform

[quoted text clipped - 17 lines]

Any thoughts on this? Help is appreciated, thanks!
-gary

 

[kingston via AccessMonster.com]

I'm not positive that this will work - I'll have to check it after the
weekend but it should be clear what the code is supposed to do.
Using the main form's OnCurrent event:

If Me. Order_Product_ID=... Then '(Or use Select Case...)
DoCmd.Echo False
Me.Subform.SourceObject = "" '(Or set it to a dummy form)
DoCmd.OpenForm SubForm, acDesign
Forms!SubForm.RecordSource = NewSource
Forms!SubForm!FieldLabel1 = NewLabel
Forms!SubForm!Field.ControlSource = NewField
...
Me.Subform.SourceObject = SubForm
DoCmd.Echo True
...

Hi again,
Thanks for your replies. Unfortunately, I am still unsuccessful and am not
sure how to go about this.
On the subform, I tried using an onLoad to check for the Order_Product_ID
and then change the caption and linking field, but have had no luck - also
not getting any error messages - only the original sub form appears.
So I tried to do it the way you first suggested. However, I am not sure
what to write in the Control Source for the correct subform to be determined.
I have tried using an "=IIF" statement, but keep getting errors in regards
to the forms not following object naming rules.
Can you please possibly provde some sample code for each example?
Thank you again!
-gary

OK, back to your original question. If you really want to do it this way, I
suggest that you use the main form's OnCurrent event to check

[quoted text clipped - 12 lines]