form and subform and combo boxes

[ebiscaro]

I have spent a long time trying to do this but no chance I would really appreciate some help.

I have a form in which there is a subform. Two combo boxes control the subform. One queries the subform by WellName the other select the desired subform Es subform with geological data, subform with depth data etc. They work fine on their own but when I change the subform I would like to requery the well name so I have shown data for just one well rather than all of them. I believe the key is this requery thing but I cannot makei it happen.
Any idea?

Thank you

 

[Pat Hartman]

Me.Parent.YourComboName.Requery

 

[ebiscaro]

thank you but....

Thanks but I have no idea where I should put that code line. I would like to have this requery in the combo that changes the subform shown, is that possible? Moreover do I have a macro called Me to make it work?

thank you

 

[Pat Hartman]

Put the code in the AfterUpdate event of whatever control on the subform changes the well name. Me. is not a macro, it is an object reference and is used to refer to the current form (or report object). Me.Parent, is used in a subform (or subreport) to refer to the parent mainform (or main report).

 

[ebiscaro]

When I do as you suggested and try it gives me an error messages saying that the Macro Me. does not exist.

Anyway I paste here my code, maybe you can help if you feel like. In any case thank you a lot.

Option Compare Database
Option Explicit

Private Sub cbotable_AfterUpdate()
'Déclaration
Dim lFormulaire As Long
Dim strNomFormulaire As String
Dim ctrlSousFormulaire As Control
'Initialisation
lFormulaire = Me![cbotable]
Set ctrlSousFormulaire = Me![fsubformulaire]

'Determins the Date shown in the subform
Select Case lFormulaire
Case 1
strNomFormulaire = "fsubBHA"
Case 2
strNomFormulaire = "fsubMotor"
Case 3
strNomFormulaire = "fsubBit"
Case 4
strNomFormulaire = "fsubIADC"
Case 5
strNomFormulaire = "fsubSurvey"
Case 6
strNomFormulaire = "fsubTendency"
End Select
'Shows requested data
ctrlSousFormulaire.SourceObject = strNomFormulaire
ctrlSousFormulaire.SetFocus

Exit_Table:
Exit Sub
Err_Table:
MsgBox "Erreur: le formulaire <" & strNomFormulaire & "> est inexistant."
Resume Next
GoTo Exit_Table
End Sub


Private Sub cbowellname_AfterUpdate()
Me.Parent.cbowellname.Requery
End Sub

Private Sub Form_Activate()
Dim cbowellname As Control
Set cbowellname = Me![cbowellname]
cbowellname.Requery
End Sub

this last was my attempt but I cannot make it work???

Thank you

 

[Pat Hartman]

You don't seem to have the code in the correct form. You said you wanted to requery a field on the main form from a subform. To reference a field on the current form use - Me.YourField.Requery
To reference a field on the parent form use -
Me.Parent!YourField.Requery

 

[ebiscaro]

lets see..

thanks for still answering me. As a matter of fact you are right. What I want to do is requery the WellName field in the subform when I update the combo box called cbowellnamethat contains the list of WellName in the form header. I would like to do this cause with another combo, always placed in the header of the mainform, I change the subform accordingly to the data I want to see, but when I do that the subform shows data for all the well, not just the one selected in the cbowellname Control.
Thank you again
Enrico

P.S. when I us ethe code you gave me in the after update event of the cbowell name it gives me the message that ME is not a Macro??

 

[Pat Hartman]

ebiscaro, "where you are" determines "how you reference" objects. There are shortcuts you can use such as Me. and Parent. that allow you to reference objects in the same form or in a parent form without specifically referencing the complete path to the control. In my previous post, I told you how to reference controls in the current form (Me.) and in the parent form (Me.Parent.) when you are in the subform. When you are "in" a sub of the mainform and want to reference a control on the mainform, use "Me.". When you are "in" a sub of a subform and you want to reference a control of the subform, use "Me." but to reference a control on the mainform when you are "in" a sub of the subform, use Me.Parent! To reference something in a subform, use Me.SubformControlName!ControlName. "SubformControlName" is the name of the subform control on the current form. "ControlName" is the name of the control on the subform that you are trying to reference. When you are outside of the form's scope, you need to use a fully qualified reference as you see below.

Change the query that you use as the recordsource for the subform to include criteria. Put the name of the mainform control in the appropriate criteria field. So for example in the criteria field for the WellName column, put:

Forms!YourMainFormName!cbowellname

In the criteria cell for the other field, put:

Forms!YourMainFormName!YourOtherControlName

Then, in the AfterUpdate events of BOTH combo's put.

Me.ctrlSousFormulaire.Requery

I am assumint that "ctrlSousFormulaire" is the control name of your subform. When the value in either combo changes, the recordsource of the subform will be requeried.

 

[ebiscaro]

I will try tonight

Thanks for your patience.
I will try this as soon as I can. I must make it work!

Ciao
Enrico

 

[ebiscaro]

still nothing....

it looks like I cannot make it work. I am afraid there must be something substantially wrong. I will surely try again when I have less work. Thank you anyway for you help.
ciao
Enrico

 

[ebiscaro]

don't ask me how

Funny enough, if you are still wandering, I imported my database in access XP and magically all it works as I whised in the first place.Now I will study the code and see where the mistake was.
Thank you
Enrico