Requery a field on a subform

[antcraw]

Hi,
I have an option group on a main form with four choices (radio
buttons). I also have a txt field that takes the corresponding value
from the option group, depending on which radio button is selected.
Last but not least, I have a subform on this main form, which has a
query as its control source. I would like to see a field on the subform
updated depending on the value stored in the txt field on the main
form, ultimately based on the selection in the option group. I've
attempted to requery the subform through the after_update of the txt
field, without success. Is this even possible?

How could I approach this?

Any suggestions/solutions are greatly appreciated.

Regards,
A. Crawford

 

[Klatuu]

First, I question your database design. It should not be necessary to store
the value in the parent table and in the child table unless it is a field
that is used to link the two tables, those being primary and foreign keys.
Now the real problem comes into play. It is certainly possible to do what
you want to do and no requery is required; however, this a one signficant
problem. That is that when you assign the value to the control on the
subform, it is going to change the value for the subform's current record.
This is assuming the subform is a datasheet or continuous form. If it is a
single record form, there will be no problem. What will happen, is the user
can click the option button, but there is no assurrance the subform will be
positioned on the correct record.

All that being said, the correct syntax to populate a control on the subform
with the value in a text box on the mainform follows. I assume you are
updating the mainform control in the After Update event of the option group.
It would also go there:

Me.txt = Me.MyOption Group
Me.MySubFormControl.Form!subtextbox = Me.txt

Note that MySubFormControl is the name of the subform control on the main
form, not necessarily the same as the name of the form that is the Source
Object of the subform control.

 

[antcraw]

First, I question your database design. It should not be necessary to store
the value in the parent table and in the child table unless it is a field
that is used to link the two tables, those being primary and foreign keys.
Now the real problem comes into play. It is certainly possible to do what
you want to do and no requery is required; however, this a one signficant
problem. That is that when you assign the value to the control on the
subform, it is going to change the value for the subform's current record.
This is assuming the subform is a datasheet or continuous form. If it is a
single record form, there will be no problem. What will happen, is the user
can click the option button, but there is no assurrance the subform will be
positioned on the correct record.

All that being said, the correct syntax to populate a control on the subform
with the value in a text box on the mainform follows. I assume you are
updating the mainform control in the After Update event of the option group.
It would also go there:

Me.txt = Me.MyOption Group
Me.MySubFormControl.Form!subtextbox = Me.txt

Note that MySubFormControl is the name of the subform control on the main
form, not necessarily the same as the name of the form that is the Source
Object of the subform control.

Actually, I am trying to just requery the text field on the subform in
the after_update event of the option group, not populate it with the
value from the main form. The text field on the subform has a
calculation that I would like to see executed/refreshed depending on
the choice in the option group on the main form.

Sorry for the confusion.

TIA.
Regards,
A. Crawford

 

[Klatuu]

Okay, that make sense.
This should do it in the After Update event of the option group.
Remember, "MySubFormControl" is the name of the subform control on the form,
not the name of the form that is the Source Object of the subform control.
They can be the same (the wizard does that goofy little act), but in any
case, you refer to the control.

Me.MySubFormControl.Form!subtextbox.requery

 

[antcraw]

Okay, that make sense.
This should do it in the After Update event of the option group.
Remember, "MySubFormControl" is the name of the subform control on the form,
not the name of the form that is the Source Object of the subform control.
They can be the same (the wizard does that goofy little act), but in any
case, you refer to the control.

Me.MySubFormControl.Form!subtextbox.requery

I entered the above line into the after_update event of the option
group and got a compile error for the Form! part of that code saying
"Method or data member not found." So I tried to take out the
MySubFormControl and just put it as:

Me.Form!subtextbox.requery

That produced the run-time error 2465 with the message "Microsoft
Access can't find the field 'subtextbox' referred to in your
expression."
So I adjusted the code to:

Me.sfrm_quote.setfocus
Me.Form!subtextbox.requery

and that did the trick as the target text field on the subform is now
updating correctly.
Would that make sense in your opinion and why am I getting the error if
I leave the MySubFormControl in?

Thanks in advance.
Regards,
A. Crawford

 

[Klatuu]

Well, the problem is you took my code too literally. I did not know the name
of the subform control, so I put that in to indicate which object to
reference. If the name of the subform control is sfrm_quote, then the
correct syntax should be

Me.sfrm_quote.Form!subtextbox.requery