Subform to be filtered when the main form opens

[qprjohn121]

Hi


My app is working with 'Organisations' and 'Contacts' (one Organisation can have many Contacts).

When the user selects a particular organisation from a list on one form, I want to open a new form that contains some Organisation info plus a list of Contacts that belong only to that Organisation. I want to do this in VBA but can't get it to work.

I pass the selected Organisation primary key to the new form with:

DoCmd.OpenForm "frmViewOrganisationContacts", , , , , acDialog, OpenArgs:=Me.txtOrganisation_PK

In the new form that opens, I have tried:

Private Sub Form_Open(Cancel As Integer)
Dim mySQL As String
mySQL = "SELECT vwContact.* FROM vwContact WHERE Organisation_FK = " & Me.OpenArgs & " ;"
'Me.frmViewContacts.Form.RecordSource = mySQL
'Me.frmViewContacts.Requery
End Sub



but I get the error: "The expression you entered refers to an object that is closed or does'nt exist."

I have tried using some of the 'later' events in the form loading process, to give the subform 'frmViewContacts' time to load before attempting to requery,but to no avail.

Any ideas? Thanks

[pbaldy]

I would use this technique to filter the main form to the desired organization:

BaldyWeb wherecondition

I'd keep the subform in sync with the main form by using the master/child link properties of the subform control. If you want to do what you're doing, you'd probably want to use the open event of the subform, referring to the parent's OpenArgs.

[qprjohn121]

Thanks for your suggestions.

I got it to work by referencing Parent.OpenArgs in the subform's Open event:

mySQL = "SELECT vwContact.* FROM vwContact WHERE vwContact.Organisation_FK = " & Me.Parent.OpenArgs & " ;"
Me.RecordSource = mySQL
Me.Requery

[pbaldy]

Happy to help.