More flexibility with linked fields between form and subform

[rkaupp]

Hi all,

First of all, I am not a very skilled Access programmer, and I mostly re-use bits of code I find on help sites.

My problem is the following: I have a main form with a combo box which allows me to select a staff member, and a subform which then shows tasks for this member. I use the "link child field" and "link master field" property of the subform.

However, in the list, there is also "ALL" as a possiblity, in which case I would like to show everybody's tasks and not just one person's. I am a bit confused as to how to proceed. I have thought at using filters, however I already use them with a chechbox, which allows to see all tasks or only todo tasks(non-completed). I would think that using filters everywhere would be a good way to obtain a horrible mixture which is why I'm asking if there is another route. I use the following code for the checkbox:

Private Sub ShowCompletedTasks_Click()

    If Me.ShowCompletedTasks.Value Then
        With Me.subfrmTasks.Form
            .FilterOn = False
        End With
    Else
        With Me.subfrmTasks.Form
            .Filter = "Completed Is Null"
            .FilterOn = True
        End With
    End If

End Sub

The same code is inserted in Private Sub Form_Current().

Thank you for any help!
Rémi

 

[pbaldy]

Presuming you want the subform to display all tasks, not just the person's displayed on the main form, I would think you want to take off the master/child links (in code).

 

[rkaupp]

Sorry, I wasn't clear: the combox box in the main form allows you to select a staff member. The list includes:
ALL
Bob
Sarah
etc.

When I select "ALL", i would everybody's tasks to be visible in the subform; but when I select any other item from the list (i.e., a staff member), I would this particular staff member's tasks to be shown in the subform.

Thanks,
Rémi

 

[pbaldy]

I understand (I think). With the master/child links set, the subform will only display the tasks for the person displayed in the main form. So, rather than manipulating filters as you are, I would manipulate the master/child links.

 

[rkaupp]

OK, but how do I "manipulate" these? I am using Access 2003, where the only option I have in the suform proporty window: I can select a field fir "link child field" and another for "link master field", but I cannot "manipulate" these such as adding an IF condition. If I use this, then when I select "ALL", no task is shown.

Unless you mean doing it in VBA? As far as I know there commands Me.subfrmTasks.LinkChildFields and Me.subfrmTasks.LinkMasterFields, but I do not see how I could customize that. Thanks,
Rémi

 

[pbaldy]

I did mean in VBA, but in looking at it in help, you can only set them in design view or in the open event of the form, so I guess that solution won't work for you. I don't think manipulating the filters will overcome the master/child links, so another solution might be to take off the master/child links and set the record source of the subform in your combo update event. Pseudo-code:

If Combo = "All" Then
  Set Subform RecordSource to "SELECT Blah FROM TableName"
Else
  Set Subform RecordSource to "SELECT Blah FROM TableName WHERE Person = " & ComboName
End If

 

[rkaupp]

Thanks, I've now understood and it works. Just some issues left with the filter not being applied when I change a person (as the filters are only applied when clicking on the checkbox and not in the combo AfterUpdate sub), but I think I can sort this out myself.

Thanks a lot for you help,
Rémi

 

[pbaldy]

No problem, and welcome to the site by the way!

 

[gemma-the-husky]

in a combo box, we often use <all> for the top item. The "<" makes sure it sorts to the top - eg above Alan, say

another way of doing this, rather than having alternate query selection, is to have the criteria included in the underlying query

so effectivley you test the name that the name= the_selected_name, or the name is ALL.