Yes, I only want to see 1 record with the three issues instead of 3 records
with 3 issues.
> Hi Diane,
>
[quoted text clipped - 65 lines]
> >>>that I can add to the subform itself that will suppress the additional
> >>>duplicate records??
strive4peace - 30 Jun 2006 06:47 GMT
here is some code to loop through a recordset and combine
values from multiple records
'~~~~~~~~~~~~~~~
Function LoopAndCombine( _
pTablename As String, _
pIDFieldname As String, _
pTextFieldname As String, _
pValueID As Long, _
pWhere As String) As String
'crystal 6-16-06
'NEEDS REFERENCE
'Microsoft DAO Library
'Set up error handler
On Error GoTo Proc_Err
'dimension variables
Dim r As DAO.Recordset, mAllValues As String, S As String
mAllValues = ""
S = "SELECT [" & pTextFieldname & "] " _
& " FROM [" & pTablename & "]" _
& " WHERE [" & pIDFieldname _
& "] = " & pValueID _
& IIf(Len(pWhere) > 0, " AND " & pWhere, "") _
& ";"
'open the recordset
Set r = CurrentDb.OpenRecordset(S, dbOpenSnapshot)
'loop through the recordset until the end
Do While Not r.EOF
If Not IsNull(r(pTextFieldname)) Then
mAllValues = mAllValues _
& " " & r(pTextFieldname)
End If
r.MoveNext
Loop
Proc_Exit:
'close the recordset
r.Close
'release the recordset variable
Set r = Nothing
LoopAndCombine = Trim(mAllValues)
Exit Function
'if there is an error, the following code will execute
Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " LoopAndCombine"
'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume
Resume Proc_Exit
End Function
'~~~~~~~~~~~~`
if you use this, you won't have need for an issues subform
Warm Regards,
Crystal
Microsoft Access MVP 2006
*
Have an awesome day ;)
remote programming and training
strive4peace2006 at yahoo.com
*
> Yes, I only want to see 1 record with the three issues instead of 3 records
> with 3 issues.
[quoted text clipped - 68 lines]
>>>>>that I can add to the subform itself that will suppress the additional
>>>>>duplicate records??
David M C - 30 Jun 2006 12:38 GMT
Your main form should be based on a query containing the relevant fields from
the employee table. Your subform should be based on a query containing the
relevant fields from the issues table.
Your subform/mainform should be linked by the employees unique identifier
(probably something like EmployeeID).
It sounds like you are trying to display the many side of the relationship
on the main form. I can think if no reason why you would want to do this.
> Yes, I only want to see 1 record with the three issues instead of 3 records
> with 3 issues.
[quoted text clipped - 68 lines]
> > >>>that I can add to the subform itself that will suppress the additional
> > >>>duplicate records??