Ed, the function below is untested, but it should give the general idea.
You can use it to list the members of a family in in a query like this:
SELECT Families.*,
ConTagName([FamilyID]) AS FamilyMembers
FROM Families;
Function ConTagName(FamilyLookUp As Variant) As Variant
Dim MyConnection As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSql As String
Dim strConcat As String
Dim lngLen As Long
Const strcSep = ", "
If IsNumeric(FamilyLookup) Then
Set MyConnection = CurrentProject.Connection
strSql = "SELECT TagName FROM tblAllIndividuals " & _
WHERE FamilyID = " & FamilyLookup & ";"
rs.ActiveConnection = MyConnection
rs.Open strSql
With rs
Do While Not rs.EOF
strConcat = strConcat & rs![TagName] & strcSep
.MoveNext
Loop
End With
rs.Close
End If
lngLen = Len(strConcat) - Len(stcSep)
If lngLen > 0 Then
ConTagName = Left(strConcat, lngLen)
Else
ConTagName = Null
End If
End Function
Notes:
====
1. The function accepts a variant so it can cope if called from a new
record where FamilyID could be null.
2. It returns a variant, so it can return Null if there are no family
members listed.
3. It chops off the comma after the last family member.
4. You may need to add a field to your Individuals table to determine the
order the family members should be presented in. (If you do, just add an
ORDER BY clause to the query above.
5. The structure you have allows a person to belong to one family only.
That might be want you want, but it may not cope with chidren who spend
half their time with Dad, and half with Mum. Going beyond that might add
unnecessary complexity, but if you want to investigate how it could be
done, see:
People in households and companies - Modelling human relationships
at:
http://allenbrowne.com/AppHuman.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
OldManEd said:
Jeanette,
Same run time error, "No value given for one or more required
parameters". Here is the complete function where I'm trying to create a
string of nicknames separated by a comma:
++++++++++++++
Function ConTagName(FamilyLookUp As Long) As String
Dim varConcat As Variant
varConcat = Null
Dim MyConnection As ADODB.Connection
Set MyConnection = CurrentProject.Connection
Dim rs As New ADODB.Recordset
rs.ActiveConnection = MyConnection
rs.Open "SELECT * FROM tblAllIndividuals WHERE tblIndividuals.FamilyID =
""" & FamilyLookUp & """" ' I tried both 3 and four ("). Did I miss
something in the spaces?
With rs
If .RecordCount <> 0 Then
Do While Not rs.EOF
varConcat = varConcat & rs![TagName] & ", "
.MoveNext
Loop
End If
End With
ConTagName = varConcat
End Function
++++++++++++
As before, it works without the WHERE part.
Ed
Jeanette Cunningham said:
Hi Ed,
there is a syntax problem where you are passing the variable.
WHERE (((tblIndividuals.FamilyID) = FamilyLookUp))"
Note I removed the parentheses for clarity, they are not needed in vba
query statements
change to
WHERE tblIndividuals.FamilyID = """ & FamilyLookUp & """
that is 3 quotes " " " - expanded for clarity - before and after the
variable
So we get
rs.Open "SELECT * FROM tblIndividuals " _
& "WHERE tblIndividuals.FamilyID = """ & FamilyLookUp & """"
Jeanette Cunningham
I have two related tables: families and individuals.
The family table has a [FamilyID] field with type AutoNumber. This is a
type 'Number' in the Individual table and is the link between the two
tables.
I want to pass a value for this field to a function and, then, on to a
SQL 'WHERE' statement. In other words, I want to create a recordset for
a family in order to extract some date from the related individuals
table.
The following rs.Open line cause a run time error, "Run time error, No
value given for one or more reqired parameters.
<Function FindFamily(FamilyLookUp as long) as string>
rs.Open "SELECT * FROM tblIndividuals WHERE
(((tblIndividuals.FamilyID) = FamilyLookUp))"
<end function>
The Function runs ok without the 'WHERE' in the SQL statement, but then
I get every record which I don't want.
Can someone please explain where I have gone wrong trying to find
specific records in the individual table.
