# MS Access requery subform from another subform



## v-six (Sep 12, 2004)

Hi people that are smarter than me (I know there's lots of you here!)

I'm trying to requery a combo box on a subform (or the whole subform) via the _after update_ event of a field in another subform (they share the same parent form). I can force the parent form to requery as simple as this:

Private Sub ItemClass_AfterUpdate()
Forms![frmItemTypes].Requery
End Sub

But I can't seem to figure out what to add to that to requery another subform in frmItemTypes.

I thought this might work, but it doesn't seem to be that easy:

Private Sub ItemClass_AfterUpdate()
Forms![frmItemTypes]![frmItemTypesSubformItemDistinctions].Requery
End Sub

Thanks for any suggestions, and let me know what details I forgot to include!


----------



## AlbertMC2 (Jul 15, 2010)

Hi

Try:
Me.Parent![frmItemTypesSubformItemDistinctions].Form.Requery

Note [frmItemTypesSubformItemDistinctions] is not the name of the subform but the name of the container on the parent form that holds the sub form.


----------



## v-six (Sep 12, 2004)

Thanks for the reply.

That's kind of along the lines of what I've been trying, but the other subform doesn't update. What you've got there seems exactly right, I just don't understand what I'm missing.

I bet your comment is in the right direction about not using the subform name, but it seems like I'm using the right name... The container for the subform is what you get by selecting the box around the subform in design view, correct? And the name of that container is what's listed under Other > Name ? The requery just doesn't seem to be executing at all.


----------



## AlbertMC2 (Jul 15, 2010)

Hi

It seems you are getting the right name as that is where you get it.

Don't know why it's not re-querying (?)
Maybe try:

Me.Parent![frmItemTypesSubformItemDistinctions].Form.[Combobox].Requery

Where [ComboBox] is the name of your combobox on the subform frmItemTypesSubformItemDistinctions


----------



## v-six (Sep 12, 2004)

Oi, I tried it like that, and arranged a few other ways, still no dice 

Does the parent form need linked fields to the child forms in a certain way in order for this to work?


----------



## AlbertMC2 (Jul 15, 2010)

Hi



> Does the parent form need linked fields to the child forms in a certain way in order for this to work?


No, it does not.

What is "ItemClass"? Is this the control on your 2nd subform? or is it your 2nd subform?


----------



## AlbertMC2 (Jul 15, 2010)

Hi again

I am attaching a stupid, simple database (Access 2007) so you can see the requering.
I have requeried a combobox and a subform from another subform. There is no parent-child "link" between the main form and the subforms. 
I have also renamed the subform containers in the main form so you can see the difference.
Run the frmMainRequery form to see it working. just add a new record in either Subform 2 or 3.
Subform2 requeries the combobox in subform1 as well as requeries subform3.
Subform3 does not requery anything when a record is added.


----------



## v-six (Sep 12, 2004)

Well, thanks for showing me that it _should_ work :smile:

Copying how you did it in your example, I used this for the event trigger:
Private Sub ItemDistinction_AfterUpdate()
Me.Parent![frmItemTypesSubForm].Form.[ItemDistinctionID].Requery
End Sub

I got 
"Run-time error '438': object doesn't support this property or method"
... at least that's better than the nothing that was happening.

_ItemDistinctionID_ is the control source of the combo box I'm targeting. Note, the subform frmItemTypesSubForm is running in _Continuous Forms_ mode. I don't know if that would affect this. Here's an image of the properties for the combobox that I'm targeting, maybe the way the row source is built is getting in the way?

Whether or not I get this sorted out, I appreciate how far you've looked into it with me—


----------



## v-six (Sep 12, 2004)

Also, here's the whole thing so you've got a good idea of what the deuce I'm doing.










The Subform labelled _Available Distinctions_ should requery the combo box in the left-most subform (screen size, in that example). The _Available Item Types_ requery event is working just fine, but its target is less complex. I'm thinking it has something to do with how I put together the combobox that won't requery.

Here's the relationships on the tables involved:









Whew.


----------



## AlbertMC2 (Jul 15, 2010)

Instead of "ItemDistinctionID" have you tried to use the name of the combobox?


----------



## v-six (Sep 12, 2004)

Yes, when I did that, instead of the error message, nothing happens


----------



## AlbertMC2 (Jul 15, 2010)

I am totally confused.
I see a main form called frmItemTypes which I presume is the left hand comboboxes?
Then 2 other forms:
1. "Available Item Types"
2. "Available Distinctions"
What are their container names?

Or have I got the main form and subforms mixed up?
What type of control is "ItemDistinction"? or is it the subform container?


----------



## v-six (Sep 12, 2004)

Well, it's working now

I'm not completely sure what I did. The only thing I can think of is I changed the Name of the combobox I was trying to target so that it DIDN'T match the name of the control source. I thought I'd done that already? But I'm not complaining as long as it keeps working. If I still had manager status I'd stop into your team room and put in a good word... except I don't so you just get a thank you :grin:


----------



## v-six (Sep 12, 2004)

AlbertMC2 said:


> I am totally confused.


I am too, even though it's all set... If you still want answers to the questions, let me know.


----------

