DoCmd.ShowAllRecords?

[RWalther]

I have a form containing a drop-down of Team Leaders and a subfor
displaying team members info that is linked to the main form throug
the TeamLeader ID.

I just have one problem. One person in the Team Leader list is th
overall leader and all people belong to this person. All the tea
members in the table have a field teamleaderID that links them to th
appropriate TeamLeader. I could not put two teamleaderID's in the sam
field, for the team leader and the overall leader (the same for al
records).

Now, I do not know how I can tell the link between form and subfor
that it should display all data in the case that teamleaderID #4 fo
the overall leader has been chosen from the combobox in the mai
form. In my research I found the ShowAllRecords Action and I trie
the following on After Update Event, but it does no
work:

Private Sub TeamLeaderID_AfterUpdate(

If (TeamLeaderID = 4) The

DoCmd.ShowAllRecord

End I
End Su

Greetings,

Rache

 

[Dirk Goldgar]

I have a form containing a drop-down of Team Leaders and a subform
displaying team members info that is linked to the main form through
the TeamLeader ID.

I just have one problem. One person in the Team Leader list is the
overall leader and all people belong to this person. All the team
members in the table have a field teamleaderID that links them to the
appropriate TeamLeader. I could not put two teamleaderID's in the same
field, for the team leader and the overall leader (the same for all
records).

Now, I do not know how I can tell the link between form and subform
that it should display all data in the case that teamleaderID #4 for
the overall leader has been chosen from the combobox in the main
form. In my research I found the ShowAllRecords Action and I tried
the following on After Update Event, but it does not
work:

Private Sub TeamLeaderID_AfterUpdate()


If (TeamLeaderID = 4) Then

DoCmd.ShowAllRecords

End If
End Sub

Greetings,

Rachel

I don't think you're going to be able to do this by way of the subform's
Link Master and Link Child Fields properties. Suppose you clear those
properties, and use the AfterUpdate event of TeamLeaderID to modify the
subform's recordsource. It might look like this:

'----- start of code -----
Private Sub TeamLeaderID_AfterUpdate()

Dim strCriteria As String

If IsNull(Me.TeamLeaderID) Then
strCriteria = "False"
ElseIf Me.TeamLeaderID = 4 Then
strCriteria = "True"
Else
strCriteria = "TeamLeaderID = " & Me.TeamLeaderID
End If

Me!sfTeamMembers.Form.RecordSource = _
"SELECT * FROM TeamMembers WHERE " & _
strCriteria

End Sub
'----- end of code -----

If you are going to allow team members to be added via the subform,
you'll need code in the subform's BeforeUpdate event to pick up the
TeamLeaderID from the main form, because it won't be filled in
automatically. And if the TeamLeaderID is the "overall leader", then
you need to find out when you add a member whether that member's team
leader really should be that TeamLeaderID, or whether it should be some
other team leader.