Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

Crystal Reports: How Do I Remove Duplicate Records?


How can I remove duplicate records?

I have are report as such:

Emp No Name     Hire Date Status Company/Subsidiary

7791     Anna Jackson     07/01/2002 Active Access
7997     Irene Hagedorn     08/01/2002 Active Access
7798     Alice Smith     08/01/2002 Active Access
7798     Alice Smith     01/01/2003 Active iSmart

Record Number 3: Displays a hire date of employee to 'Access' Company. Record 4: shows employee was employed in iSmart, a subsidiary of the main company.

The requirement of the report needs to display only record 4 and discard record 3 in the list.

What procedures should I do?

Thank you in advance,


View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Remove Duplicate Records
Can somebody help with SQL Server query?

I have a SQL Server table with only 2 fields (Field1 & Field2). There are some duplicate records in the table and I try to delete all duplicates.

1) Here is what I use in Query Analyzer:
    from TableIndicators
    where EXISTS
        select Field1, Field2,count(Field1),count(Field2)
        from TableIndicators
        group by Field1, Field2
        having count(Field1) > 1 and count(Field2) > 1
This statement would delete the whole table.

2) When I use only sub query
    select Field1, Field2,count(Field1),count(Field2)
    from TableIndicators
    group by Field1, Field2
    having count(Field1) > 1 and count(Field2) > 1
it selects only duplicate records that is what I need.

Are there are any ideas why the whole statement (#1) does not work?


Remove Duplicate (or Triplicate Or More) Records
This thread is sort of a continuance of thread:
How do I remove duplicate, triplicate records (some are 4 or more the same)
I want to delete the entire records that are repeated.
Note that I mean delete from the db all together( using update I think is the method)

Grouping Records In Data Reports / Crystal Reports

I want to group records in my Data Report based on 2 particular fields. Just see the example below.

Below is the Table Structure and its values

UnitID Part Quantity
1 xx 5
1 xy 10
2 xx 5
2 xy 10

Now I want to result to show the summation of quantities based on the part only.

so the result should be

Part Quantity
xx 10
xy 20

It would be fine if someone can tell me how to do this in Crystal report/ VB data reports


How To Remove Time From Crystal Reports....please Help
i am populating a crystal reports from a VB 6 program. All fields including the dates goes into the report correctly. However, in all of the dates fields, in addition to the date .... a time 12:00 AM is also shown. I am using short date format.....could someone help me with how to make sure that only the date and not the time appears in the reports.


Remove Blank Lines In Address Labels : Crystal Reports 9
I'm using crystal reports 9 for address labels with VB6 / access

The problem is I have 4 address fields and a postcode. If only two lines of address are used when I come to print my address labels there is a gap between the address and postcode.
As a go between stage I did

strSQL = strSQL & " SELECT tblStudent.FirstName + ' ' + tblStudent.Surname as FullName, tblStudent.Address1 + chr(10) + chr(13) + tblStudent.Address2 + chr(10) + chr(13) + tblStudent.Address3 + chr(10) + chr(13) + tblStudent.Address4 + chr(10) + chr(13) + tblStudent.Postcode AS address"

But still when I iterated through the records to remove the blank lines it wasnt all that effective.

Is there an easy way to do this without iterating? In a word mail merge you just select toremove blank lines but i coudnt see anything in CR.

Dan Bayley
Affordable website design UK
Free Google & Yahoo sitemap generator

Edited by - DanInManchester on 8/27/2003 1:51:25 AM

Selecting Records With Crystal Reports
I have a VB project that creates records in an Access 97 database. I am using Crystal Reports to do the reporting. The database holds information about clients. One table holds name and address, others (with a one-to-many relationship) hold supporting information such as family contacts.

I have created a report and placed the .rpt file in the same directory as the database. I am having trouble creating the select command. What am I doing wrong? The report prints for the entire database with just the PrintReport command. Nothing prints when I try to limit the report to one client. Here is my code:

DOLReport1.ReportFileName = "C:DiscOfLifeDevelopment estDOL.rpt"
DOLReport1.SelectionFormula = "{ClientTable.CTKey}= " & sCurrentDOLClientKey
DOLReport1.Destination = crptToWindow

Any suggestions are appreciated,


Get Records For Subreport: Crystal Reports
I am looping through objects on a Report to find the subreports and need to get the correct records for the found subreports, but it's not working. I've opened my recordset and it is returning the correct records (when checked in Access), but the subreport is not returning those returns all instead. Can someone help me out? Thanx.

If newObject.Kind = crSubreportObject Then
'Found a subreport, create an new instance
Set crxSubObj = newObject
'Open the subreport; treat it like the main report
Set crxSub = crxSubObj.OpenSubreport
'assign the second recordset to the subreport
If crxSub.Subreportname = "FilterReport.rpt" Then crxSub.Database.SetDataSource rs2
End If

It correctly meets the IF criteria, but the records that show up aren't right...

Crystal Reports Return Min And Max Records
Hello all,
This question should be easy but for some reason it eludes me. I am trying to create a Crystal report (version 8.5) that looks up records in 2 tables based on a record ID. I have a primary table that holds “header” information and another table that holds the detail with a one to many relationship. I want to be able to query all the records on both tables and display the information. The detail table has a status, requesting department and a date_time field. Using the status and date_time fields, I want to use a function that looks up the maximum date_time and displays the (current) status. I then need to be able to display the requesting department based on the minimum date_time.

I would appreciate any help. Thanks!

Crystal Reports 9.0 -msg Indicationno Records
hi all
i have pass parameters to fetch the records. But if the passed parameter values doesnt contains any records to display the blank crystal reports alone displaying. i want to be informed if there is no records.

Crystal Reports - Faster Records

Hopefully someone out there can help me.

I have a VB6 front end application that is connected to an external database (Sage - Tetra CS/3) via an ODBC link.

All my reports are designed in Crystal Reports.

Basically all that my front end application does is send additional criteria to my reports.

At present each report currently snatches about 50 - 55 records per second. Is there anyway in which I can speed this rate up.

Please someone help !


Crystal Reports 4.6 : Why All The Records Are Not Displayed?
Hello ! struck up with this problem

I am using the following code in my VB6 Appln to display the crystal report:

With crptSalarySlip
        If cboEmpName.Text <> "" Then

            .ReportFileName = App.Path & "Reportssalaryslip.rpt"
            .Connect = App.Path & "DatabasePayroll97.mdb"
            .Action = 1
        End If
End With

The Report looks fine at design time...
But all the records aren't displayed in the on the crystal report....
Please let me know wat could be the flaw..
Thnx in Advance!!

Edited by - neerajs on 1/16/2005 8:51:26 PM

Suppressing Printing Of Crystal Reports With No Records
Would someone mind providing a brief example of how to avoid printing Crystal reports inside VB for which there is no data? I have a report that prints every few minutes. At times, there is no new data for the report. The problem is that the report prints a blank with just text objects when there is no new data to be reported. I would prefer that the report does not print at all.

I built a native .rpt using Crystal and checked the File...Report Options....Suppress printing if no records check box. This works fine inside Crystal.

I am not calling this .rpt file from inside my VB app. Instead, I added the report as a designer named CrystalReport1.dsr. The suppress printing check box is not available from inside the designer or at least I cannot find it.

Is there some programatic way in VB to check to see if the data is empty before attempting a report print?

I am using Crystal 8.0 .


Crystal Reports 7 Only 5 Records On Each Page - Anyone Online Now?
I am using crystal reports 7 with vb and access.
i have to print only 5 records per page.
how can i set a page break?
ie., after 5 records it have to print the next 5 in a new page.


Select Records By Date (Crystal Reports)
Hi! I have a problem I don't know what have I need to select some records from a Crystal report, I need to select some range of dates and it always send me a message telling me that there is an error in the formula.
Can you help me it drives me crazy!!

I will appreciate all your comments.

Thank you

This is the code:

Dim DocName As String
Dim LinkCriteria As String

CrystalReport4.Connect = "DSN = hoy;UID=;PWD=;DSQ="

a = "Date(1999,01,01)"
b = "Date(1999,01,30)"

Thank you!!!
c2 = "="
c1 = "{}"
c3 = "<="
y = "and"

Set rep = CrystalReport4
rep.SelectionFormula = "" & c1 & " " & c2 & " '" & a & "''" & y & " " & c1 & " " & c3 & " '" & b & "'"
rep.ReportFileName = "d:personalvb_accesssalidas.rpt"

Select Case Err.Number
Case 20515: 'Resume Next
MsgBox ("There is an error in the selection")
Resume Next
End Select
On Error GoTo errorhandler
rep.Action = 2

Crystal Reports And Selecting Top 10 Records To Display?
Hi, I am stuck and starting to up holes in the walls of my office using my head only!!!!

I have started using Crystal report 9 and I am trying to create a report to show the top X number of accounts.
Why? Because I have a points report which I want to create a top X build up of points.

I say X because I want to pass that X number into the report. So it will give me back the top X number of records.
What I have done is select my data which gives me everything back. But how do you only get the top X (say 10for example) records from the data.

Am I making sense?

Crystal Reports. How To Group Records On 15 Minutes
Dear All,

Can anyone suggest me a way to group the records on 15 minutes.

Thanks & Regards,

Crystal Reports Parameter Condition Selecting Records
Hi Guys,

I have created a Crystal Report using SQL Query by entering the SQL Statment directly. This SQL Statement contains a parameter. Everything works fine on the report. I get perfect data on the report when I run it and give the parameter field. Now my aim is if the parameter field is left blank then I should run the report for all the values in a particular field. I dont know how to put the condition and where. I am using standalone Crystal Reports 8.5

Can anyone please explain to me how to do this ?

Duplicate Remove
i have a duplicate remove but what i want to do is if i have to listbox with listbox 1 with 1234 and 123 in it and also listbox 2 with 1234 and 342 in it how can i get it so it will find the 1234 in listbox 2 and remove it?

Remove Duplicate Value...
does any one knows how to remove duplicate values from combobox...???

Need it urgent...???


Remove Duplicate Lines
how would i go about removing duplicate lines from a text file. (a big ish text file around 10,000 lines)

The duplicate lines are always next to each other so i suppose that makes it a lot easier.


It would only be required to remove the 2 in italics the 2 later on is ok. Speed isn't really needed either as long as it doesn't take like an hour or something silly.

Remove Duplicate From A Grid
I have a MSFlexGrid with 4 columns. I would like to delete duplicates (4 same columns). Is there a fast way or do I have to do a loop, check each one and delete them?

Remove Duplicate Lines
I have a list of item codes for a data entry and trying to make a simpel vb app to help remove duplicates like:
The entries are in a multiline textbox
123449112 <- Orignal
123449112 <- Duplicate

So it removes only duplicates and keeps at leaste one of each code.

Duplicate Remove Function
Heres My Dup Remove Function, It Can Only Handle About, 1000 itmes, without freezing, any ideas making it handle more?? id like to get it to 5000+


Private Sub cmdDup_Click()
Dim Search1 As Long
Dim Search2 As Long
Dim KillDupe As Long
KillDupe = 0
For Search1& = 0 To List1.ListCount - 1
For Search2& = Search1& + 1 To List1.ListCount - 1
KillDupe = KillDupe + 1
If List1.List(Search1&) = List1.List(Search2&) Then
List1.RemoveItem Search2&
Search2& = Search2& - 1
End If
Next Search2&
Next Search1&
End Sub

Remove Duplicate Listbox Entries

I have searched but cannot find anything that works

I am wanting to remove any duplicate text from the list box....

heres an example of my code (does not work)


Private Sub Command8_Click()

Dim itext As String

For i = 0 To List1.ListCount - 1
itext = List1.List(i)
If List1.List(i) = List1.List(i) Then
MsgBox "2x song", , "ERROR"
End If

End Sub

All help much appreciated!


Remove Duplicate Entries From A Collection
I'm trying to delete any duplicate entries from a collection thats been passed to a function. The idea i have in mind is to first add all the items in the collection to an array, then compare the array against the collection for duplicate values, but i'm not sure how to do this. I have:

Function DeDupe (Col as Collection) as Integer

For x = 1 To Col.count
ReDim Preserve checkarray(1 To x)
checkarray(x) = Col.item(x)

For x = LBound(checkarray) To UBound(checkarray)

For y = 1 To Col.count
If checkarray(x) = Col.item(y) Then
MsgBox "Value: " & Col.item(y) & " removed", vbDefaultButton1, ""
Col.Remove (y)
End If


End Function

but all this does is remove the first value in the collection then throw a subscript out of range error.

Any ideas?

RichTextBox - Remove Duplicate Lines
I tried to do this using some code for removing duplicates in a listgbox, but I couldnt get it to work.

Anyone here have some code offhand that takes care of this?

Thanks ahead of time.

Remove Non Duplicate Rows From An Excel Column
Hi guys, I hope somebody can help me, How through visual basic or a formula can I remove from a column all the non duplicate data in an excel file?

I have a range of data and what I need is to find the duplicate data cells to work with it removing the non duplicated lines.

Any idea?

Thanks so much

Listbox And Textbox. Remove Duplicate Entries

I have a form that has a textbox and a listbox. These are filled dynamically from user input. I need to be able to look at each item the listbox and determin if it exits anywhere in the textbox (This textbox has multipul entries). And if it does, I need to remove it from the textbox. I do not want to clear the textbox, just remove the duplicated item.

Any Ideas how to do this?

Remove Duplicate Entries From Text File....
hi, say i have a textbox with numbers like this:


how would i make a program which removes duplicate entries from the textbox? i've more or less got a similar bit of code working which works for a listbox, but i'm kinda stuck with how to do it on a textbox. can anyone help?

Remove Duplicate Strings From Array Improvement?
I wrote a function to replace duplicate strings in an array with "". Since I'm going to have to convert it over to VBScript for ASP use, can this be improved for faster performance?

VB Code:
Private Function ChopArray(ByRef Arr() As String)    Dim i As Integer 'outer loop counter    Dim j As Integer 'inner loop counter    Dim s As String 'temp store for current value        'loop through the array    For i = LBound(Arr) To UBound(Arr) Step 1        s = Arr(i)        'if its not an empty string, and we're not at the last item in the array,        'then loop through the rest of the array to remove the duplicated strings        If (Len(s) > 0) And (i <> UBound(Arr)) Then            For j = (i + 1) To UBound(Arr) Step 1                If Arr(j) = s Then Arr(j) = ""            Next        End If    Next End Function

Using Listview.finditem To Remove Duplicate Files....
I was wondering how to use the listview.finditem before loading items into the list, then form an if statement on either not adding if it is allready there and adding if it isn't..... can anyone tell me how this is done? or if there is an easier way to prevent duplicate items plz let me know, thanks

How Can I Remove Duplicate Lines In A Text File?
I have made a function that removes duplicate lines in a text file but it does not always work here it is:

option Explicit

public Function RemoveDuplicateLines(FileName as string) as Integer

Open FileName for input Access Read as #1
Open App.Path & "integrity.tmp" for Output as #2

Dim LineIn(1 to 2) as string
Dim Impurities as Integer
Dim FirstPass as Boolean
Dim Last as Integer

FirstPass = true

If EOF(1) = true then GoTo endof
'If it's the first time input 2 lines and compare them
'otherwise compare the last line with the next line
If FirstPass = true then

Line input #1, LineIn(1)
Last = 1

If EOF(1) = true then print #2, LineIn(1): GoTo endof

Line input #1, LineIn(2)
Last = 2

If EOF(1) = true then print #2, LineIn(2): GoTo endof

'Compare the lines
If LineIn(1) = LineIn(2) then

print #2, LineIn(1)

'Increment the defects
Impurities = Impurities + 1


print #2, LineIn(1)
print #2, LineIn(2)

End If

else 'It's not the first pass

If Last = 1 then

Line input #1, LineIn(2)
If EOF(1) = true then print #2, LineIn(2): GoTo endof

'Compare Line(1) with Line(2)

If LineIn(2) = LineIn(1) then
Impurities = Impurities + 1
print #2, LineIn(1)

print #2, LineIn(1)
print #2, LineIn(2)

End If

Last = 2

ElseIf Last = 2 then

Line input #1, LineIn(1)
If EOF(1) = true then print #2, LineIn(1): GoTo endof
'Compare Line(2) with Line(1)

If LineIn(1) = LineIn(2) then
Impurities = Impurities + 1
print #2, LineIn(2)

print #2, LineIn(2)
print #2, LineIn(1)

End If

Last = 1

End If

End If



Close #1
Close #2

Kill FileName
FileCopy App.Path & "integrity.tmp", FileName
Kill App.Path & "integrity.tmp"

RemoveDuplicateLines = Impurities

End Function

The problem with it is that it does two lines at a time so if my file is like this:


Then my output is like this:


Because 'd' was not reconized as a duplicate line. However if the file looked like this:


Then it would work because 'c' is an odd number.

My code looks complex because I though that it would work like this:

a &lt;-- check this line with next line
b &lt;-- check this line with next line
b &lt;-- check this line with next line
c &lt;-- etc..

But my code does this:

a &lt;---\___ Check these two lines
b &lt;---/
b &lt;---\___ Check these two lines
c &lt;---/

Can someone help me out??? Please!!

Duplicate Records!
I have a form in Access which has event date and then also an event time slot such as "Morning, Afternoon, Evening" . I need to make it so that you cannot have a duplicate time booking, i assume i will need to code some VBA but if anyone could tell me what it would be much appreciated. Thanks

Duplicate Records
Hi, I'm using the oracle db.
I'd like to find the duplicate records in table A.
Can somebody tell me the query?
Thank you!

Duplicate Records
I am using VB.NET with Microsoft Access 2000 to design a program for a company.  In testing, we have found that occassionally an error will pop up that states that the program is attempting to duplicate a record that already exists in the table.  The thing is, the only primary key in the table is autonumbered, so it should not be duplicating records.  The only way to fix the problem is to create a clean database and a clean table, then copy the information from one database to the other.  Is there any way that anyone knows of to circumvent this problem, or am I going to have to get the company to get a new database program?

Duplicate Records

I'm using Excel 2003 and I need a code to sorts out all names that are being repeated on the sheet. The routine should report how many times each name is being repeated on the sheet.
John Smith
Alfred Jordan
Danny Global
John Smith
John Smith
Alfred Jordan

After the run:

John Smith 3
Alfred Jordan 2
Danny Global 1

Thank you so much



How To Duplicate Records
hi again!!

help me on this on please!!!

i have a access db.i have created and populated a datatree with various options like add a node,rename it i want to implement the copy-paste option.
is there any way to copy an entire record at runtiime and have it updated ih the database.

the process is,i first select a node that i want to copy and then select a diff node where i want to paste i do the paste operation i need to have a new and identical record created in the database.

help me on this on please!!!
thank u

have a nice day

Script To Remove Duplicate Values From Sql Server Table
i know this isn't really a vb question -

anybody got a procedure in sql server to check for any duplicate numeric values in a table and update them to make them unique?

table was created with no primary key -

i was thinking something along the lines of select distinct field from table ordered by - set variable = to max value + 1 of field in question.

loop through the table and set field = variable value + 1 where field value = variable value.

obviously variable would need to be incremented by one through each loop.

i just can't figure the exact script!

thanks in advance and apologies for sidetracking a bit but i figured there would be guys on here who could do this easily enough.

Prevent Duplicate Records
I have a sheet called RentRoll that the user enters the information from a userform I created (BuildingName, SuiteName, SuiteNumber, SuiteSquareFootage). When the userform loads, it fills any information that might be in the table. Once all the information is entered, it is saved to the table (sheet).

How do I prevent duplicates from being entered? I have thought about loading all the entries into an Array, or using a find/lookup.

The Building name can be duplicated and suite names can be duplicated, but the combination of building name, suite name, & suite number can not be duplicated.

Thanks in Advance...

PS, check out the top of the forum for an IMPORTANT request/announcement.

Stop Duplicate Records
In my app I have a "addPDF" button. I can add the pdf alright to the database, but each time I hit the add button it adds the same pdf to the database again causing duplicated records. I know I need to do a search first to see if the record is in the database already, but I'm not sure I am doing the search correctly.

Call CreateConnection(objConn)

Set objRs = New ADODB.Recordset

With objRs
.ActiveConnection = objConn
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Source = "select * from pdf where pdf_id = '" & txtPresentationID.Text & "'"
End With

'generate id
gID = generateID()

With objRs


If Not objRs.EOF Then

sSQL = "select * from pdf where pdf_cp_id = '" & txtPresentationID.Text & "' and pdf_name = '" & TxtFile1.Text & "'"
If .Fields!pdf_name = TxtFile1.Text Then
'do nothing
.Fields!pdf_name = TxtFile1.Text 'file name of pdf
End If

.Fields!pdf_cp_id = frmIntro.txtPresentationID.Text
.Fields!pdf_on = Check2.Value
.Fields!pdf_id = gID

End If

frmIntro.txtpdfID.Text = checkstr(.Fields!pdf_id)

End With

Set objRs = Nothing
Call CloseConnection(objConn)

Checking For Duplicate Records?
Is there a way to go through a recordset and check each row for duplicates without having to constantly open and close the table? I'm thinking not, but I'm also not sure.

What I'm also needing to do is open a recordset, grab the first row, and check the rest of the RS for that same record based on two key fields. If a duplicate (or duplicates) is found, I need to move that duplicate into another table with, what I assume, is an INSERT statement. Is this all possible in a stored proc? If not I could do it in VB, but I'm just not sure how to proceed with it all.

Yep...I'm totally lost on a Friday afternoon.

Prevent Duplicate Records
I have an application that polls a 400 table every 10 seconds for new data from multiple locations. I use the .addnew to pull the data into a local table. How can I check for new data and write without rewriting the old data again. I have a primary key set on the Access table, so when I pull in using vb a get an error. Deleteing the old data is not really an option, since this runs every 10 seconds. The 400 data is cleaned out if it is older than two days old. I am pushing into a local network table to avoid having Client Access on every machine and using a system dsn.

SQL Problem (Duplicate Records) Please Help.
Here's my problem I'm using SQL Server 7.0 Well I happen to stupidly copy a record and paste it into the same database. Since I didn't set the Primary Keys, it pasted if fine.. a exact duplicate. Now I want to delete the duplicate/modify it, and it won't since ".. Too many records are affected by update."

How can I fix this??

Also is there a way to make a auto increment primary key for the SQL Server 7.0 like one in MS Access?

Thanks for the help

Deleting Duplicate Records
Hi all,

I've been working on converting an access database (data and UI) to a full VB/SQL Server app. Yesterday morning I pulled over all the data.... and today found out that there are duplication (and some with more than 2) records. I can't simply pull the data and start over because the data is live. I also found out that I set the primary key to the wrong field, which allowed the duplicate records to begin with. My question is this: is there any way I can (easily) go through and delete the dupes? There are over 50,000 records and as it stands right now, I'm having to go through one by one in the query analyzer and delete any duplicates I find (ouch!).

Thanks for the help!

Filter Duplicate Records

i have a problem here. i have a database that has multiple duplicate names because it depends on the number of occurences so basically, one name may appear several times but the contents of the records are different. for example, person AAA has 3 occurences so therefore the name AAA appears 3 times. what i need now is to list all names that have occurences in a listbox but without the duplicates. so if AAA appears 3 times, only 1 AAA is listed in the listbox. i know how to populate the listbox but i don't know how to go about filtering it.

BTW, i'm using MS access as my db.

thanks in advance!

SQL To Show Duplicate Records
How can I find records that only differ by a comma at the end?
Str1 = "Test1"
Str2 = "Test"
I need some sql that will display all such records.

The records aren't stored in strings but in a database. The sql I'm using to find duplicates is:
rsDupCust.Open "SELECT Name FROM Customer WHERE ((Name In (Select Name " _
& "from Customer as tmp group by [Name] having count(*)>1 )))", Cn, adOpenStatic, _
But this wont show any customer names that only differ by a comma

How Do I Eliminate Duplicate Records?
I am running SQL Server 7.0.

How do I eliminate duplicate records from a table? I want to run a sql script to find all duplicates and only keep one duplicate record.

There is no primary key after this I wish to also set a composite key with about 4 fields. (I can do this myself.)

I'm trying to figure how to do it and run it from query analyzer, but it might take some time.

I appreciate your help.

Selecting Duplicate Records
I'm using Access as my database, i'm trying to select duplicate records from the database. I've thought of using the 'Distinct' keyword, but i want to select or view the records which are not filtered by the distinct keyword. Let say I have 10 records in my database and 4 of them are identical, if i used the distinct keyword it'll show me a total of 7 records, correct? But i want to view the other 3 records which has not been included, how am going to that? Please help

Deleting Duplicate Records
I created a table with values from another table were duplicated. The
duplication was based off two fields, MemberID and MemberNo that,
combined, should be unique. I am now trying to match the values from
the duplicate table to the production table to remove the duplicate
values. I have tried several statements in VB6 but none seem to delete
any records. Here is the last code I wrote, can someone please tell me
what I am doing wrong?

Dim cnndups As Connection

Dim rsDups As Recordset

Dim rsMembers As Recordset

Dim strSQL As String

Dim strSQL1 As String

Set cnndups = New Connection

Set rsDups = New Recordset

Set rsMembers = New Recordset

cnndups.CursorLocation = adUseClient

cnndups.Open gblConnectionString

strSQL = "Select * from dups"

rsDups.Open strSQL, cnndups, adOpenDynamic, adLockOptimistic


Do While Not rsDups.EOF

strSQL1 = "Select * from tblMembers where keyNumber = 2 AND
keyMemberID ='" & rsDups!keyID & "'"

rsMembers.Open strSQL1, cnndups, adOpenDynamic,


rsMembers.Delete adAffectCurrent






Copyright © 2005-08, All rights reserved