Taking up Len's suggestion you can restrict the list box's Rowsource. You
could for instance add a combo box cboSex to the form with a RowSourceType
property of Value List and a RowSource property of F;M. In its AfterUpdate
event procedure requery the list box with:
Me.lstPlayers.Requery
Change the list box's RowSource property so it references the control as a
parameter:
SELECT PlayerID, FirstName & " " & LastName
FROM Players
WHERE (SEX = Form!cboSex OR Form!cboSex IS NULL)
ORDER BY LastName, FirstName;
You can add as many other controls as you wish to the form to restrict the
list further in the same way by including further references to parameters in
the WHERE clause, e.g. to restrict the list to players of or below a certain
age at the time of a tournament by entering the age and tournament date in a
text boxes, on the basis of a DoB (date of birth) column in the Players table
if you have one:
SELECT PlayerID, FirstName & " " & LastName
FROM Players
WHERE (Sex = Form!cboSex OR Form!cboSex IS NULL)
AND (GetAge(DoB, Form!txtTournamentDate) <= Form!txtAge
OR Form!txtAge IS NULL)
ORDER BY LastName, FirstName;
You'd use the following function to get the player's current age:
Public Function GetAge(varDob, Optional varDateAt)
Dim intYears As Integer, intMonths As Integer
' return date at current date if no second argument
' passed into function
If IsMissing(varDateAt) Or IsNull(varDateAt) Then
varDateAt = VBA.Date
End If
If Not IsNull(varDob) Then
' get difference in years
intYears = DateDiff("yyyy", varDob, varDateAt)
' get difference in months
intMonths = DateDiff("m", varDob, _
DateSerial(Year(varDob), Month(varDateAt), Day(varDateAt)))
' adjust result if date of birth falls later in year
' than date at which age to be calculated
If varDateAt < DateSerial(Year(varDateAt), Month(varDob),Day(varDob))
Then
intYears = intYears - 1
intMonths = intMonths + 12
End If
GetAge = intYears & " year" & _
IIf(intYears <> 1, "s", "") & ", " & _
intMonths & " month" & _
IIf(intMonths <> 1, "s", "")
End If
End Function
If no tournament date is entered in the form then the current date will be
used to calculate the player's age.
Ken Sheridan
Stafford, England
Ken, thanks, I've just reread what you wrote and I think it will do
the trick. My concern with the multi-list box for players is that the
list is potentially long and it might be easy to make mistakes by
clicking the wrong player, not clicking a player, having difficulty
when scrolling the list and so forth.
But at a pinch it will do.
A better solution would be an unbound form with a multi-select list box of
players, a combo box to select the tournament and a button to inset the rows
[quoted text clipped - 86 lines]
