I have a Collections table that includes four member ID fields and a collection ID field, and a CollectionTypes table with type ID and type name field, both of which are included in a query. I need to pass a member ID to the query and get back a recordset with Collections table ID and collection names where that member ID appears in ANY of the four member ID fields. After fetching that collection recordset, I use the member IDs it returns to get the member's full name using another query (which works fine). I will use the results to populate a listbox. My problem is that when I run the first query, it returns a recordset with a row for each record in the CollectionTypes (and the same four member IDs in each. I know I could accomplish this with four queries and then combine the results, but hope there is a way to do it in one.
Here is my query code...
Code:
PARAMETERS [lngCurrentMemberID] Long;
SELECT tblCollections.ID AS COLLID, tblCollections.strPrefix, tblCollections.lngMember1ID,
tblCollections.lngMember2ID, tblCollections.lngMember3ID, tblCollections.lngMember4ID,
tblCollectionTypes.txtCollectionTypeName AS TYPENAME,tblCollectionTypes.ID,
tblCollectionTypes.txtCollectionTypeName
FROM tblCollections, tblCollectionTypes
WHERE [lngCurrentMemberID] IN (tblCollections.lngMember1ID, tblCollections.lngMember2ID,
tblCollections.lngMember3ID, tblCollections.lngMember4ID);
...and the corresponding VB:
Code:
Set qdfGetCurrentMemberCollections = currDB.QueryDefs("qryGetCurrentMemberCollections")
qdfGetCurrentMemberCollections.Parameters!lngCurrentMemberID = Me.ID
Set rstGetCurrentMemberCollections = qdfGetCurrentMemberCollections.OpenRecordset()
If rstGetCurrentMemberCollections.RecordCount > 0 Then 'Iterate through recordset
If Not (rstGetCurrentMemberCollections.EOF And rstGetCurrentMemberCollections.BOF) Then
rstGetCurrentMemberCollections.MoveFirst
Do Until rstGetCurrentMemberCollections.EOF
lngCollectionMember1ID = 0
lngCollectionMember2ID = 0
lngCollectionMember3ID = 0
lngCollectionMember4ID = 0
strMember1Fullname = ""
strMember2Fullname = ""
strMember3Fullname = ""
strMember4Fullname = ""
strCollectionTypeName = ""
strCollectionItemPrefix = ""
lngCollectionID = rstGetCurrentMemberCollections!COLLID
strCollectionTypeName = rstGetCurrentMemberCollections!txtCollectionTypeName
If IsNull(rstGetCurrentMemberCollections!strPrefix) Then
strCollectionItemPrefix = "UNDEFINED"
Else
strCollectionItemPrefix = rstGetCurrentMemberCollections!strPrefix
End If
If IsNull(rstGetCurrentMemberCollections!lngMember1ID) Then
strMember1Fullname = "-"
Else
strMember1Fullname = strGetNameByID(rstGetCurrentMemberCollections!lngMember1ID)
End If
If IsNull(rstGetCurrentMemberCollections!lngMember2ID) Then
strMember2Fullname = "-"
Else
strMember2Fullname = strGetNameByID(rstGetCurrentMemberCollections!lngMember2ID)
End If
If IsNull(rstGetCurrentMemberCollections!lngMember3ID) Then
strMember3Fullname = "-"
Else
strMember3Fullname = strGetNameByID(rstGetCurrentMemberCollections!lngMember3ID)
End If
If IsNull(rstGetCurrentMemberCollections!lngMember4ID) Then
strMember4Fullname = "-"
Else
strMember4Fullname = strGetNameByID(rstGetCurrentMemberCollections!lngMember4ID)
End If
lbAssociatedCollections.AddItem CStr(lngCollectionID) & ";" & strCollectionItemPrefix & ";" & _
strCollectionTypeName & ";" & strMember1Fullname & ";" & strMember2Fullname & ";" & _
strMember3Fullname & ";" & strMember4Fullname
rstGetCurrentMemberCollections.MoveNext
Loop
End If
End If
Ideas???