Referencing a Control on a Subform

[andybuck86]

Hi All,

I have probably a simple NOOB question that I hope you can help me with please.

I have a multi select list box which I am trying to use to open another form and apply a filter based on the user's multiple selections.

List box - lstCatergories
Main Form - frmSuppliersSummaryCategories
Subform on Main Form - frmSuppliersSummaryCategoriesSubForm

The Subform contains the text box CategoryID

Here's my code which nearly works...

Private Sub cmdFilterSuppliers_Click()

  On Error GoTo Err_cmdFilterSuppliers_Click

  Dim strWhere      As String
  Dim ctl           As Control
  Dim varItem       As Variant

  'make sure a selection has been made
  If Me.lstCategories.ItemsSelected.Count = 0 Then
    MsgBox "You Must Select at Least 1 Category"
    Exit Sub
  End If

  'add selected values to string
  Set ctl = Me.lstCategories
  For Each varItem In ctl.ItemsSelected
    strWhere = strWhere & ctl.ItemData(varItem) & ","
  Next varItem
  'trim trailing comma
  strWhere = Left(strWhere, Len(strWhere) - 1)
  'open the form, restricted to the selected items
  DoCmd.OpenForm "frmSuppliersSummaryCategories", acNormal, , "CategoryID IN(" & strWhere & ")"

Exit_cmdFilterSuppliers_Click:
  Exit Sub

Err_cmdFilterSuppliers_Click:
  MsgBox Err.Description
  Resume Exit_cmdFilterSuppliers_Click

End Sub

My problem is that the control "CategoryID" is on the subform and I'm having massive problems trying to reference it. This is the part of my code that won't work:

DoCmd.OpenForm "frmSuppliersSummaryCategories", acNormal, , "CategoryID IN(" & strWhere & ")"

However; if I open the SubForm directly it works perfectly. I.e.

DoCmd.OpenForm "frmSuppliersSummaryCategoriesSubform", acFormDS, , "CategoryID IN(" & strWhere & ")"

To summarise... How do I reference the control "CategoryID" on the subform "frmSuppliersSummaryCategoriesSubform"?

Massive thanks in advance for any help

Andy

 

[jdraw]

Please see http://access.mvps.org/access/forms/frm0031.htm

 

[andybuck86]

Thanks a lot for the link but I still can't see to get this working...

According to the link I should be using...

DoCmd.OpenForm "frmSuppliersSummaryCategories", acNormal, , "Forms!frmSuppliersSummaryCategories!frmSuppliersSummaryCategoriesSubForm.Form!CategoryID IN(" & strWhere & ")"

I.e. Forms!Mainform!Subform1.Form!ControlName

However; when I run this, the form opens but it does not display the subform at all

Any suggestions?

Thanks again

Andy

 

[MLUCKHAM]

What I do to keep it nice and simple is to use the OpenArgs option at the end of the OpenForm method. You can pass any number of open arguments that you want. This can then be reference on the form you are opening by the Me.Args property. I often pass multiple arguments seperated by a semi-colon. I then use the split command on the OnLoad method of the form to split out the arguments from the Me.Args property and do something useful (like filter my form or toggle the readOnly mode etc).

Of course you can try and implement a modification to the where clause (which is what you are trying to do) however, remember this is a modification to the SQL data set not the form. So the where clause needs to be based on the SQL field names not the field names on the form.

Have a look at OpenArgs at the end of OpenForm method - it forces you to keep the code attached to the form object which I think is better.

 

[andybuck86]

Thanks MLUCKHAM

Just did some research on the open args and think this will be much better