Update Subforms - No Parent/Child

rblair11

Registered User.
Local time
Today, 06:06
Joined
Nov 4, 2005
Messages
36
I have a main form with 2 subforms. Subform 1 shows the results of query 1, subform 2 shows the results of query 2.

Query 2 uses query 1 as a starting point. Query 1 is updated with an option group on the main form. In other words, when I select an option on the main form, qdef for query 1 is updated and query 2 will also be affected.

If I select an option the subforms don’t update. If I close and reopen the main form, the subforms show the correct information so the queries are being updated but the subforms are not showing the updated information.

I’ve been trying to requery the subforms using the following code in the OptionGroup_Click() event:

Forms![MainForm].[Subform1].Requery
Forms![MainForm].[Subform1].Requery


By the way, I don’t have any parent/child relationships.

Am I missing something simple.
 
Forms![MainForm].[Subform1].Form.Requery
Forms![MainForm].[Subform1].Form.Requery
 
You should be able to do Me.NameofSubform.Requery

Larry
 
forms

Without "seeing" it I would (suggest) the following. Remove the second form based on query 2 from your main form. Instead put it as a subform in your form that uses query 1. Link the subform to the form using query 1 using parent/child link.

In the option group on click use the forms![mainform]![mysubform].Requery.
Now the forms will update the information.

Hope this helps
 
I'm sorry I should have mentioned that I've already tried the following suggestions and they didn't work either:

Forms![MainForm].[Subform1].Form.Requery
Forms![MainForm].[Subform1].Form.Requery
Me.NameofSubform.Requery

I will try the parent/child link when I get a chance. By the way, why are none of the other methods I've tried working? I don't think I understand the parent/child thing.

Thanks for your help!
 
What I gave you *will* work if the forms are called out properly.

Forms![MainFormName].[Subform1ControlName].Form.Requery

or if executed from the MainForm then:

Me.[Subform1ControlName].Form.Requery

It is important that the Subform1ControlName be the name of the SubFormControl and not the SubForm. Forms are displayed on other forms by means of a SubFormControl. It is the name of this control we need to reference the SubForm. You know you are on the SubFormControl when the data tab shows the LinkChild/MasterFields. The LinkChildMasterFields do not need to be completed for the Requery to work. The SubFormControl name can be the same as the SubForm, but it is not a requirement of Access.

Are you throwing any errors? Set your forms to Break on *every* error and see what is going on.

Here's a good link for your favorites collection: Refer to Form and Subform properties and controls
 
Last edited:
Still having problems

No, I am not throwing any errors. I have tried every combination that has been suggested here and in other forums.

Is there a way to manually requery the subforms when in form view so that I can check to make sure a requery is what I really need?

By the way, when in design view, subform properties, the "source object" on the "data" tab and the "Name" on the "other" tab are the same (qryBodyFocusSummaryByDateSpec_subform). I think this would relieve the "subformcontorl" verses "subform" issue. Just so I'm clear though, the subformconrol is in the "name" property, correct?

Short of attaching the database, what information would be helpful to all of you to help me? Here is some code:

Code:
Private Sub OptionGroupSelectName_Click()

Dim xxxx
Dim xxxx
.
.
MyDb.QueryDefs.Delete "qryBodyFocusSummaryByDateSpec"

Select Case Me.OptionGroupSelectName.Value

    Case 1
        DefStringSpec = "xxx"
    Case 2
        DefStringSpec = "xxx"
    Case 3
        DefStringSpec = "xxx"
.
.

Set qDef = MyDb.CreateQueryDef("qryBodyFocusSummaryByDateSpec", DefStringSpec)
Me.[qryBodyFocusSummaryByDateSpec_subform].Form.Refresh
Me.[qryBodyFocusSummaryCountSpec_subform].Form.Refresh

End Sub

By the way, xxByDateSpec is query 1 and xxCountSpec is query 2 (refer to original post).

Also, when I created the subform, I selected to use an existing query as referenced above. I did not add any sql code to the recordsource, etc. I am simply relaying on the values passed from the option group to the DefStringSpec string to set the query defination of query 1. Everything trickles down from there.

Thanks for your help everyone!
 
You could try writing the SQL straight into the RecordSource of the subform.

Code:
Me.ctlCustomersSubform.Form.RecordSource = " SELECT *" & _
                                           " FROM tblCustomers" & _
                                           " ORDER BY CompanyName DESC;"

Hope that helps.

Regards,
Chris.
 
It's not that don't want to try the recordsource method, but I really would like to get the method I'm working on to be successful. I think it would be a better learning expierence. Do you think my initial concept is flawed?

Someone please let me know if I need to abondon my first plan and use the recordsource method. If I do, fine. But I hate to spend more time going in an oppsoite direction if I'm really close.

Otherwise I will try the recordsource method.

Thank you everyone for your input.
 
I've been wondering if you just need MyDb.QueryDefs.Refresh after you Create the QueryDef again.
 
G’day RG.

I tried the query refresh also but with no joy.

Interesting problem though. :cool:

It appears that the form, when opened as a sub form, continues to work based on a copy of the query but when opened directly uses the new query correctly. However, if I do a compact then the sub form works correctly. :confused:

The only way I’ve been able to get it to work correctly is to write the SQL string directly to the Record Source of the sub form.

Don’t know if rblair11 has found anything but I can’t do it any other way.

Regards,
Chris.
 
G'day to you Chris,

Thanks for the update. 'tis a puzzlement!
 
Success (No Refresh or Requery)

Here's the code that finally works. I had to give up on the Requery/Refresh route. Thanks to all of you for your help.

Code:
Private Sub OptionGroupSelectName_Click()

Dim MyDb
Dim qDef
Dim DefStringDateSpec As String
Dim RSDateSpec As String
Dim RSCountSpec As String



MyDb.QueryDefs.Delete "qryBodyFocusSummaryByDateSpec"

Select Case Me.OptionGroupSelectName.Value

    Case 1        
        RSDateSpec = "appropriate SQL text"
        RSCountSpec  = "appropriate SQL text"
        DefStringDateSpec = RSDateSpec

    Case 2
        RSDateSpec = "appropriate SQL text"
        RSCountSpec  = "appropriate SQL text"
        DefStringDateSpec = RSDateSpec

    Case 3
        RSDateSpec = "appropriate SQL text"
        RSCountSpec  = "appropriate SQL text"
        DefStringDateSpec = RSDateSpec

End Select
    
Set qDef = MyDb.CreateQueryDef("qryBodyFocusSummaryByDateSpec", DefStringDateSpec)
    
Me.qryBodyFocusSummaryByDateSpec_subform.Form.RecordSource = RSDateSpec
Me.qryBodyFocusSummaryCountSpec_Subform.Form.RecordSource = RSCountSpec


End Sub

Any comments regarding the approach I used? And I still don't understand why the format for Me.Subform.Form. Am I referring to the control source here?

Thanks again!
 

Users who are viewing this thread

Back
Top Bottom