Duplicate records on subform

[Guest]

I have a subform that gets its data from a query. That query consists of 2
linked tables. When the query is run and the two tables are joined, the
output consists of duplicate Empl_Names. That is, Ellen has 3 records
because there are 3 issues. On my subform, it displays this employee with
her 3 issues but it also displays the record 3 times. In the query, I have
used DISTINCT and that works until I join the 2 tables. Is there some code
that I can add to the subform itself that will suppress the additional
duplicate records??

 

[strive4peace]

Hi Diane,

use 2 forms

the main form will be based on Employees and the subform
will be based on Issues. The 2 forms will be linked using
LinkMasterFields and LinkChildFields for the subform control
on employeeID

you said this is already a subform ... what is you mainform
based on and how does this relate? It is always best to use
a seperate form/subform for each table if you want to be
able to edit it.

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Guest]

Crystal,
My main form is based on the employees name and id info and it relates to
the subform with the issues that are added for that employee. The form
doesn't need to be edited, it is just an information form that shows all
issues for each employee. the problem comes when there is more than one
issue for that person. It displays all of the issues on the form but if
there are 3 issues, there are 3 records there. I can't seem to suppress the
records from displaying more than once. I am trying to figure if there is
something on the subform that makes the records only apprear once.

Thank you,

diane

 

[strive4peace]

Hi Diane,

are you just trying to determine if the employee has ANY issues?

"if there are 3 issues, there are 3 records there."

so, if an employee has 3 issues, you want to see just one
record?

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Guest]

Yes, I only want to see 1 record with the three issues instead of 3 records
with 3 issues.

 

[strive4peace]

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

*

 

[Guest]

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