Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Modules / DAO / VBA / June 2006

Tip: Looking for answers? Try searching our database.

Duplicate records on subform

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Diane - 28 Jun 2006 17:23 GMT
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 - 29 Jun 2006 12:47 GMT
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

 *

> 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
[quoted text clipped - 4 lines]
> that I can add to the subform itself that will suppress the additional
> duplicate records??
Diane - 29 Jun 2006 14:46 GMT
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

> Hi Diane,
>
[quoted text clipped - 30 lines]
> > that I can add to the subform itself that will suppress the additional
> > duplicate records??
strive4peace - 29 Jun 2006 17:59 GMT
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

 *

> Crystal,
>   My main form is based on the employees name and id info and it relates to
[quoted text clipped - 44 lines]
>>>that I can add to the subform itself that will suppress the additional
>>>duplicate records??
Diane - 29 Jun 2006 20:18 GMT
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??
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.