Requery on a subform

[jminelli]

Dear Members,
I’m experiencing big troubles with a requery command.
Here it is a simplification of my issue: I’ve built a general form containing the name of the persona, the category of his expertise (for instance economics, human resources…etc). I want to link each category to different options (areas), like, economics= finance, macroeconomics and so on…I want the combo box of areas depend on the category choice. I know how to link those two combo boxes in the same form, but not in case of a subform in the general one. So, what is the command to requery the category combo box, after update event, to the subform?

Many thanks,
J.M.

 

[rainman89]

take a look at this link. it will show you the correct syntax to requery a combo box in a subform

http://www.mvps.org/access/forms/frm0031.htm

 

[jminelli]

Hey rainman, thank you, but I don't understand wich one to use...could you help me more? I'm sorry I don't get, but I'm at myfirt steps in access.

 

[rhett7660]

Ok...

I tried the link you suggested. I still can't get it to work.

Main form = frmMainData
Sub form = frmSubComponent
control on frmSubComponent I would like to requery = cboLocation

Form I close that activates this requery is frmAddLocation

I have tried the following:

Private Sub Form_Close()
Me!frmSubComponent.Form!cboLocation.Requery
End Sub

or

Private Sub Form_Close()
Me!frmMainData.Form!frmSubComponent.Form!cboLocation.Requery
End Sub

or
Private Sub Form_Close()
[Forms]![frmMainData].[Form]![frmSubComponent].[cboLocation].Requery
End Sub

among others. I can't seem to get it to work.

 

[Kiwiman]

Howzit

When you put a subform on your form, access will ask you to specify a name to give the subform. By default this name is the name of the subform itself. However this may have been changed when inserting the subform, or later.

E.g I inserted a subform (frmCalendar) onto a form, and gave it a name called Jack.

To reference this a control on this form i would use the following:

me.[b]Jack[/b].Form!mycontrol

not

me.[b]frmCalendar[/b].Form!mycontrol

Check the name of the control holding the subform to see if it is what you expect it to be.

Also note your code below has the code executing on Form_Close

 

[rhett7660]

Kiwiman

That is correct re: code executing on Form_Close. I have another form that opens so I can add additional locations as they arise. When it closes I would like it to requery the box it is associated with. I am trying your suggestion now and I will let you know.

Thanks


Also. I looked at the properties of the subform and it is also tabbed: TabCtl16 would this make a difference?

 

[Kiwiman]

you can ignore tabctrls when it comes to referencing controls even though it is a control in itself. Just treat it like it is not there. Just the container holding the form itself is the one.

 

[rhett7660]

Ok.. I did the following code:

Private Sub Form_Close()
Me!frmSubComponent.Form!cboLocation.Requery
End Sub

I am still getting the following error:
Run-time error 2465
microsoft office access can't find the field 'frmSubcompnent' referred ito in your expression.

 

[Kiwiman]

Howzit

It looks like teh right syntax

in vb editor, after your Private Sub

type me.
once you type the . a list of options will come up does the "frmSubComponent" show up? - this should be the name of the container that holds thh subform. If not you want to find the name of the container.

An example I just created below and definitely works is

Me.subSubform.Form!Model.Requery

where subSubform is the subform container that holds subform "frmModel", and "Model" is combo box.

 

[rhett7660]

Ah... no the subform was not showing up. Let me check something.

 

[rhett7660]

It is showing the following under caption and under source object: frmSubComponent

However this subform is on a tabbed interface. I don't know if that will make a difference or not again, but I thought I would put it in there.

 

[Kiwiman]

in design view of your main form click on the subform container, view the properties. Go to name - that is the value you want

 

[Kiwiman]

Howzit

Sorry, the name is on the "Other" tab

 

[rhett7660]

Here you go:

frmSubComponent

I also attached a pic just to make sure we are talking about the same thing.

 

[Kiwiman]

Howzit

That looks like the right value, can't work out why yours does not work.

I have loaded a small database. If you DL it open form frmMain and click on the command4 button. THis requeries the combo, and puts the value of the combo in the text box on the main form.

Check the code below it

 

[Kiwiman]

Howzit

had a closer look at your question. I think the problem is that the combobox you are trying to requery from (frmAddLocation) is in another form entirely, not a subform of frmaddlocation. As long as the form is open try

Forms![frmMainData].[Form]![frmSubComponent].Form![cboLocation].Requery

Sorreee...

 

[rhett7660]

Kiwiman..

That did the trick. Thank you very much. No need to be sorry! Thank you! I was so close!