If statement and LinkMasterFields

[EJaques]

Hello,

I have a list box that controls the display of information on a form and its subform.

I am having problems with the code to change Child/Parent links according to what is selected in the list box and a combo box (both on the main form):

I want:
-the subform to display only the records corresponding to a specific work order when a given order is selected in the list box
-the subform to display all records if "All work orders" is selected
-the subform to display all records for a given month when "All work orders and a month is selected

The code is the AfterUpdate event of the Listbox.

Private Sub SubFormLinkRules()
'Decides and sets how subform is linked to Main form

Dim strChild As String
Dim strMaster As String

If Me.Liste21.ListIndex = 0 Then
'0 corresponds to <All work orders> in the listbox
 If Me.cmbMois = "<All months>" Then  ' Me.cmbMonth is field in the Main form
            strMaster = vbNullString
            strChild = vbNullString
      Else
            strMaster = "[cmbMonth]"
            strChild = "[Month]"   'Month is a field on the subform
    End If
        
Else
      strMaster = "[CodeCmd]" 'CodeCmd is a field on the main form and subform
      strChild = "[CodeCmd]"
       
End If

Me.frmCmdComm.LinkMasterFields = strMaster 'frmCmdComm is the subform
Me.frmCmdComm.LinkChildFields = strChild 
                                   
' After having selected <All work orders> in the list box
 ' and a value in cmbMonth if I try to select
 ' something further down the listbox I get an error.
 ' I think it is because strMaster isn't changing. But why?

End Sub

If I try setting the Child/Master links one by one (to test if they work) the subform displays the information I want; so this is not the problem.

Thank you in advance for your insight.

[ChipR]

Have you considered just changing the subformControl. Form.FIlter property?

[EJaques]

Have you considered just changing the subformControl. Form.FIlter property?

Like by saying?

Me.frmCmdComm.F orm.FilterOn = False

It doesn't work. I don't know why. I don't really want to do that either because
the user can choose to filter the subform by list boxes on the subform also.

Are Child/Master links and Filters the essentially the same !?!

[ChipR]

This MSDN page says that you can only change the LinkMasterField s and LinkChildFields in design view or Form_Open. You can point the LinkMasterField s to a text box with a calculated value, but you can't for the LinkChildFields . This may prevent you from doing what you intend.
I still use the master and child fields for very simple forms, but when it gets more complicated, I either change the recordsource or the filter dynamically to achieve the same results.

I'm not sure what you mean when you say
Me.frmCmdComm.F orm.FilterOn = False
doesn't work.

Perhaps the best way if you need to change the .Filter from within the subform is to change the underlying recordset from the main form. Then you don't have to deal with taking the existing filter string and changing it.

'called in the main form
Dim strSQL As String
strSQL = "SELECT * FROM myTable"
If ...
  strSQL = strSQL & " WHERE [month] = " & ...
Else ...
  strSQL = strSQL & " WHERE [codecmd] = & ...
End If
frmCmdComm.Form.RecordSource = strSQL