requery of a query which defines the values in a subform

[Garyhou]

I have an unlinked subform on a main form whose data is defined by a query. The query has its criteria defined by a field on a 2nd subform on the main form. What I am trying to do is to have the data displayed in the unlinked subform change when I select the next record in the 2nd subform. This would be easily accomplished if I could get the query to requery. I have tried various ways to code a requery but have been unsuccessful. Any assistance would be much appreciated.

Thanks

 

[Remou]

Can you post one or two of the ways you tried? It makes it easier to answer, if form names etc are available.

 

[Garyhou]

The name of the unlinked subform is nfrm_ammendments, the name of its underlying query is nqry_ammendment.

I tried various requery types like docmd.requery "nfrm_ammendments" or
docmd.requery "nqry_ammendment"

I tried Forms!Nfrm_ammendments.Requery and Nfrm_ammendments.requery which also does not work.

Garyhou

 

[Remou]

How about:
Forms![MainFormName]![SubformControlName].Form.Requery
Or
Me.[SubformControlName].Form.Requery

It is important that you use the name of the subform control, not the name of the form contained. These are usually the same, but not always.

 

[Garyhou]

This does not appear to work, I think because I don't have a control on the subform, therefore there is no control to reference in the code string.

 

[Remou]

In what way does it not work? Is there an error or does nothing happen? When you say you do not have a control on the subform, what do you mean? Is it a subform in datasheet view? Perhaps you could post the relevant code and the SQL of the query?

Here is a page of information on referring to subforms:

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

 

[Garyhou]

Let me try to explain form a different perspective: I have a main screen that is tracking purchase orders. Each purchase order has numerous line items which I am currently displaying in a subform, linked by the PO id. Each line item has numerous ammendments containing ammendment no, delivery dates, etc which I am displaying in a second subform linked by the line item id. What I am trying to do is to be able to click on a line item in the first sub form and have all the ammendments related to that line item appear in the second subform.
Here is the sql of the query for the ammendment data:
SELECT tbl_RPO_amendment.[Amendment No], tbl_RPO_amendment.[Amendment Date], tbl_RPO_amendment.[Line ID], tbl_RPO_amendment.qty, tbl_RPO_amendment.[delivery date], tbl_RPO_amendment.[shipped date], tbl_RPO_amendment.shipped
FROM tbl_RPO_amendment
WHERE (((tbl_RPO_amendment.[Line ID])=[forms]![tfrm_rpo_entry]![nfrm_line_items].[form].[id]));

This works fine when the main form opens for the 1st line item no. but I haven't figured out how to get the query to requery when the line item number changes as I click on different ones.

 

[Remou]

Ok, I think I get it, you want to do something very like the Customer Orders Form from the Northwind sample database? In the Northwind sample, this kind of set up is created with Link Child and Master fields.

 

[Remou]

... and this code in the On Current event of subform 1:

Private Sub Form_Current()
' This code created by Form Wizard.
    Dim strParentDocName As String

    On Error Resume Next
    strParentDocName = Me.Parent.Name

    If Err <> 0 Then
        GoTo Form_Current_Exit
    Else
        On Error GoTo Form_Current_Err

    'Edited:
        Me.Parent![nfrm_ammendments].Requery
    End If

Form_Current_Exit:
    Exit Sub

Form_Current_Err:
    MsgBox Err.Description
    Resume Form_Current_Exit
End Sub

 

[Garyhou]

Thank you REMOU. That solved the problem!!!