requery control on subform

  • Thread starter Thread starter r
  • Start date Start date
R

r

I have a subform with a listbox of Codes and description field that does a
dlookup to get the associate description from the codes table. If the user
changes the value in the listbox, a macro runs to put control on the
description field and then requery it. It works fine when the subform is
loaded on it's own (not as a subform within the main form). However, when I
try to run it as part of the main form, I get an "action failed" on the
requery step. ?? Can this be done at all, is there another way to cause
the description to be updated, or do I need to change something in the macro
(how I refer to the field/subform) to make it work?

Thanks in advance.
 
Are you running the code from the subform or from the main form? When you
say "put control on the description field", do you mean set the focus to
this control? If so, it shouldn't be necessary to set the focus to the
control to requery it if you use VBA.
 
I'm not very "VBA" - I'm using macros. And yes, I meant that the control I
want to requery has the focus before I run the requery. It's odd that it
works in the subform when I have it open alone, but not when running it from
within the main form (as a subform). Also, there is a combo box on the
subform that has a macro to put focus on the description field, then to
requery. Again, works in the standalone subform, but not when it's running
inside the main form. I really don't get it.
 
I believe that the problem you are running into is that the main form is the
form with the focus. The subform is an object on the main form. The macro is
probably operating on the form with the focus. If so, you may be able to add
more than one SetFocus statements to the macro, the first one would set
focus to the subform control on the main form and the second one would set
focus to the control on the subform. Note, that you set focus to the subform
CONTROL on the main form, not the subform itself.

Actually, I don't see a SetFocus option for a macro. I do see a GoToControl
option. If you are using GoToControl, ignore the above. For this, you need
to type in the name of the control. When a form is a subform, the path to
the control changes. The new path is:

Forms!NameOfParentForm!NameOfSubformControl.Form!NameOfControlOnSubform

I suspect you currently just have:

Forms!NameOfSubform!NameOfControl

This will work when the subform is open by itself, but not as a subform. The
VBA below won't care which way the form is opened. Note, that in the example
for the subform syntax, you use the name of the subform CONTROL on the main
form. This may or may not be the same name as the subform.


You may prefer to use VBA for this. I assume you are running the macro as
the AfterUpdate for the combo box on the subform. What are all of the steps
in the macro? Are they just the set focus and requery or does it do other
things also? If it is just the set focus and requery, the equivalent for VBA
is below. To get to the VBA editor, set the On AfterUpdate to [Event
Procedure] and click the ... button. This will take you to the VBA editor
with the first and last lines of the procedure already filled in for you.
Between these lines (adjust the names for the names of your controls) enter:

Me.txtMyTextbox.SetFocus
Me.txtMyTextbox.Requery

The Requery can be done without setting the focus first if you prefer, so
you only need the second line to perform the requery.
 
Hi Wayne,

Thanks for your help.

I found an article online that said I had to first GotoControl and specify
the subform name only, then GotoControl again and specify the control name
on the subform only, and that was the only thing that worked.

Now, unfortunately, I have another problem - requerying the control isn't
working, I'm guessing because of the parent/child link. I get the message:

"You can't use the apply filter action on this window."

I don't really have a "filter" applied .. it's just filtering based on the
parent/child relationship. If you have any suggestions, I'd love to hear
them!

Thanks again.

-R

Wayne Morgan said:
I believe that the problem you are running into is that the main form is the
form with the focus. The subform is an object on the main form. The macro is
probably operating on the form with the focus. If so, you may be able to add
more than one SetFocus statements to the macro, the first one would set
focus to the subform control on the main form and the second one would set
focus to the control on the subform. Note, that you set focus to the subform
CONTROL on the main form, not the subform itself.

Actually, I don't see a SetFocus option for a macro. I do see a GoToControl
option. If you are using GoToControl, ignore the above. For this, you need
to type in the name of the control. When a form is a subform, the path to
the control changes. The new path is:

Forms!NameOfParentForm!NameOfSubformControl.Form!NameOfControlOnSubform

I suspect you currently just have:

Forms!NameOfSubform!NameOfControl

This will work when the subform is open by itself, but not as a subform. The
VBA below won't care which way the form is opened. Note, that in the example
for the subform syntax, you use the name of the subform CONTROL on the main
form. This may or may not be the same name as the subform.


You may prefer to use VBA for this. I assume you are running the macro as
the AfterUpdate for the combo box on the subform. What are all of the steps
in the macro? Are they just the set focus and requery or does it do other
things also? If it is just the set focus and requery, the equivalent for VBA
is below. To get to the VBA editor, set the On AfterUpdate to [Event
Procedure] and click the ... button. This will take you to the VBA editor
with the first and last lines of the procedure already filled in for you.
Between these lines (adjust the names for the names of your controls) enter:

Me.txtMyTextbox.SetFocus
Me.txtMyTextbox.Requery

The Requery can be done without setting the focus first if you prefer, so
you only need the second line to perform the requery.

--
Wayne Morgan
MS Access MVP


I'm not very "VBA" - I'm using macros. And yes, I meant that the control
I
want to requery has the focus before I run the requery. It's odd that it
works in the subform when I have it open alone, but not when running it
from
within the main form (as a subform). Also, there is a combo box on the
subform that has a macro to put focus on the description field, then to
requery. Again, works in the standalone subform, but not when it's
running
inside the main form. I really don't get it.
Click to expand...
Click to expand...
 
You are correct about the 2 steps needed to set focus if you are going from
the parent form to a control on the subform. Your initially description
though sounded as if you already had the focus at a control on the subform.

What is the Control Source of the control you're trying to requery? How are
you assigning the value to this control? VBA has another option, you can do
a ReCalc of the form, but I don't see that option using macros. Is the
control you're trying to requery part of the Parent/Child link?
 
Back
Top