Refreshing a sub-form from a button on a sub-sub-form of the main form.

[MilaK]

Hello,

I've made a complex sub form configuration (please see the attached image).

I have a series of sub-sub-forms with buttons next to each record. When the button is pressed I would like to query another subform "Reportable comments". How do I correctly refer to that subform from a button code of a sub-sub form?

I've tried several ways but Access can't find the object. The query is correct but the reference to the reportable comments subform is wrong.

I have seen the following website, http://access.mvps.org/access/forms/frm0031.htm
however, still can get this to work. Thanks, Mila

Private Sub cmb_info_Click()

Dim temp_rst1 As Recordset
Dim Db As Database
Dim qry As String

If Nz(Me.ID) <> "" Then

Set Db = CurrentDb

qry = "SELECT cnv_id, CreatedDate, LastReviewed, CreatedBy, CommentTxt, Interpretation FROM tbl_cnv_reportable_comments " & _
"WHERE cnv_id = '" & Me.cnv_id & "'"

 Debug.Print qry
Set temp_rst1 = Db.OpenRecordset(qry)

 '[Forms]![frm_combined_CNVs_with_Comments]![frm_cnv_reportable_comments].[Form].RecordSource.Requery
     'Me.Parent.Parent.[Form]![frm_cnv_reportable_comments].[Form].RecordSource.Requery
 
 '[Forms]![frm_combined_CNVs_with_Comments]![frm_cnv_reportable_comments].[Form].RecordSource = qry
 '[Forms]![frm_combined_CNVs_with_Comments]![frm_cnv_reportable_comments].[Form].RecordSource.Requery
 
 
   If temp_rst1.EOF Then 'emty record set
    
   [Forms]![frm_combined_CNVs_with_Comments]![frm_cnv_reportable_comments].[Form]![txt_cnv_id] = Me.cnv_id 'copy down id
    
    End If

End If

End Sub

 

[MarkK]

It is tempting to make a user interface that exactly matches the structure of your data, so you have a parent table, a child table, a child-child table, so you make form, sub-form, sub-sub-form.

But what you want to look at in your user interface (UI) is where does most of the work happen? If you find yourself making tons of functionality 2 subforms deep, then maybe you want to rethink which form should be the main form in respect to that task.

At one end of a system is the data. At the other end are the actual tasks that users need to do. Design your UI around those tasks. Give the user exactly the form they need to get their work done, and that may not look the same as how your data is structured.

Hope this helps,

 

[MilaK]

Hi,

Thanks for your response. I understand that this might not be an ideal design but is there a way to refresh a subfrom from another sub-subform? If it can't be done please let me know and I will have to figure something out.

Thanks,

Mila

 

[sneuberg]

Here's a few things to consider:

In http://access.mvps.org/access/forms/frm0031.htm please be aware that where you see subform in the model references it means the name of the subform control and not the name of the form in the subcontrol. Usually they have the same name so this isn't a problem.

The form's RecordSource doesn't have a Requery method, the form does so the reference should end with

Form.Requery

not

Form.RecordSource.Requery

If you have a later version of Access (I think 2007 and after) you can use Intellisense to help you build these references. This is what I usually do:

1. Open all of the forms involved including the subforms by themselves even if they are open in a form. This seems to help the Intellisense.
   
2. Go into the query designer, just close the Show Table dialog and start typing your reference in the criteria. After you type Forms! you should get drop downs with the applicable choices. Sometimes the Intellisense goes on strike and I find I have to restart the application to get this to work.

 

[MilaK]

i got it working!!! I've replace the query with a filter.

Thanks for your help, Mila

Private Sub cmb_info_Click()


If Nz(Me.ID) <> "" Then
 
Debug.Print Me.ID
Debug.Print Me.cnv_id

[Forms]![frm_combined_CNVs_with_Comments]![frm_cnv_reportable_comments].[Form].Filter = "cnv_id = """ & Me.cnv_id & """"
[Forms]![frm_combined_CNVs_with_Comments]![frm_cnv_reportable_comments].[Form].FilterOn = True

    If Nz([Forms]![frm_combined_CNVs_with_Comments]![frm_cnv_reportable_comments].[Form]![txt_cnv_id]) = "" Then
    
   [Forms]![frm_combined_CNVs_with_Comments]![frm_cnv_reportable_comments].[Form]![txt_cnv_id] = Me.cnv_id 'copy down id
   
    End If

End If

End Sub