From Main form on event after update re-query combo box on sub sub form 1

[puforee]

My main form name is Shows, the sub form name is Show Dates Subform and the sub sub form name is Breed Information. The control on the sub sub form that I want to requery is a combo box named BreedSelDD.

All forms are open but the Sub Sub Form is held NOT visible until needed. (There will be other sub sub forms that will react the same way be being visible when needed.)

So on an after update event of a combo box on my main form I want to re-query the BreedSelDD combo box on the sub sub form.

I have been using various forms of this:

EventSelectDD After update
Private Sub EventselectDD_After Update()

DoCmd.Requery Forms![Shows]![Show Dates subform].Form![Breed Information].Form![BreedSelDD]

End Sub

By various forms I mean using quotes before and after or using ( ) before and after appropriately.

Nothing seems to work, they all error out.

I have always had problems relating to controls on sub-forms but I can usually fight my way to a solution. This time I can't

Where am I going wrong here? I will need this for several other sub sub forms.

Thanks,

 

[MajP]

In order that you can debug this easily, I would do it in bites. Now you will know where the issue lies.

Dim sFrm as access.form
Dim ssFrm as access.form
Dim ctrl as access.combobox
Set sFrm = Me. [Show Dates Subform].form
Set ssFrm = sFrm.[Breed Information].form
Set Ctrl = ssFrm. BreedSelDD
Ctrl.requery

This would be one line as

 me.[Breed Information].Form.[BreedSelDD].requery

but harder to debug

If you are calling the code from same form you can replace
Forms!YourFormName with just Me
Also do yourself a favor and never put spaces in ANY name of any object. You are just begging for problems that are not necessary
Breed_Information
Show_Dates_Subform

Also the above example assumes that the name of your subform controls are the same as the name of the subforms inside. This usually happens by default, but not necessarily.
Because you put spaces in your form names, I doubt the subform control has the same name. To select the subform control the easiest is to drag pointer from the outside of the control to the side of the control.

 

[puforee]

Thanks MajP,
I was wondering if I should use me. instead of DoCmd. I will give this a run when I get home tonight.

And, I do get my subform names the way you suggested. That in itself assures me I am at least doing that right.

Sorry for the spaces. I have been getting better but......

 

[Andrzejek]

MajP, if all Forms are loaded (and not necessary visible), wouldn't it be enough to simply do:
[tt]
Breed_Information.BreedSelDD.Requery
[/tt]
(assuming spaces are gone from form and control's names)

Just wondering...

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[MajP]

Thanks. I will edit my post. I was typing too fast.

 

[MajP]

MajP, if all Forms are loaded (and not necessary visible), wouldn't it be enough to simply do:
Breed_Information.BreedSelDD.Requery

Andy,
Two things.
1) I always let intellisense do the heavy lifting. If I have a control on a form I could just write
Breed_Information.
instead of
me.breed_information.
but intellisense will ensure I do not make a typo and it is faster. I will just type "me.br" and about that time intellisense will give me the correct verbiage and i hit return. Which is faster and more accurate than the "shortcut" of skipping the "me."
2) Not sure if you made a typo but you are missing ".form". MS calls the control you put on a form a subform, but that is a bad name. It is a subformReport Control. The thing inside is really the subform or a subreport. Or at least people often use the name to me both things. To get the thing inside you have to use the name of the subform control followed by form. (if it is a report you have to use ".report")
me.Breed_information.form
returns the form inside of the subform control called breed_information. If you forgot to add the property "form" and wrote
Me.Breed_Information.BreedSelDD.Requery
you get an error since
Me.Breed_Information
returns the subform control (the container). That container does not have a property called "breedselIDD". Only the form inside of the subform control does.

 

[Andrzejek]

Thanks MajP for the explanation.
I, too, use the intellisense to make my work 'error proof' (in VBA, VB6, VB.NET) but instead of typing 'me.br' I just type 'br' and hit Ctrl-Space to 'awake' intellisense. And if there is only one 'br'(something) on my Form, IDE does not even display intellisense, it just fills the name. Very handy shortcut.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[puforee]

MajP,

I decided to use me.[Breed Information].Form.[BreedSelDD].requery first...assuming I had the correct names. And...drum roll...it worked first time. So, my question is...what is the rule of thumb for using DoCmd vs Me. ?

And thank you so much. I use multiple form/subforms/etc. quite often so I will learn from this experience. And I will keep the testing code also...a good way to check each part of the code.

And to the Andrzejek...thanks for joining the conversation. I did not know about the Ctrl-Space trick.

And a star for you MajP.

 

[Andrzejek]

There are a lot of tricks in IDE
Another one I use a lot is Shift-F2 Place your cursor in the variable's name and hit Shift-F2, you will get to the declaration of that variable. The same goes for Subs, Functions, Enums etc. Ctrl-Shift-F2 gets you back to where you were
Enjoy.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[MajP]

So, my question is...what is the rule of thumb for using DoCmd vs Me.

Not quite the right question. Should be "what is the difference for using Docmd and for acting directly on the object?" Two different approaches to accomplishing certain things. Sometimes you can get the job done both ways other times you cannot. Sometimes one way is a lot easier. I will give some examples.

The Docmd basically carries out any action you could do from the user interface. Running menu choices, opening/closing forms, refreshing. Basically carrying out access actions. Sometimes it is limited because the parameters need to be value types (strings, longs, doubles) and not objects.

Or you can work with objects themselves. Objects have properties and methods.

Example Re-querying a control. It can be done either way.

To requery a control you need to get a reference to that control and use the requery property. lots of ways to get a reference.
If it is being called from the current form's class module
me.controlName.requery
or on another form
forms!someformname.somecontrolName.requery
or a variable representing a control
dim x as access.combobox
set x = me.somecombo
x.requery


There is also a requery docmd method
docmd.Requery(ControlName)
There is some limitation. The argument has to be a name of a control. It is a string. Since it is only a name, you can only use a name of an object on the current active object. There is no way to tell it to look for "txtB" on some other form. You could not reference a control on another form or subform. This is where Docmd can get you in trouble. It is very dependent on the active object. One common problem I see people do is simply typing Docmd.close. Depending on what they are doing the wrong form or report closes because at that time the focus was on something they did not expect.
Now going back to your original post, you could not use the docmd because you are limited to providing a name of a control on the active object.
What you did returns an object and not a string: Forms![Shows]![Show Dates subform].Form![Breed Information].Form![BreedSelDD]
So in this case it is really hard to requery using the docmd anything that is not on the form from where it is called.
You might by able to make this work by first setting focus to the subsubform and then calling it by name. But that is more work.

There are certain things that can only be done with docmd like things that effect how access behaves.
docmd.setwarnings
docmd.runcommand

One example where it is a lot easier is opening a form.
The Docmd.openform method provides a lot of flexibility to opening a form.
Opening a form directly takes a lot of extra code and there are limitations to what you can do.