Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access 2007 - VB Code checking for duplicates

Posted on 2011-02-20
8
Medium Priority
?
795 Views
Last Modified: 2012-05-11
My OS is windows 7.  The code I have here was developed by the Gurus here on this Forum.  This is a small database.  And it extracts the database,concaternates into a string, so that I could copy and paste it into an email client.
However the database also contains the names of spouses who use the husband's or wive's email address, thus there will be duplicates in the present code.
For ease of reference, I think it is should be implemented from the Else line.
I would like the code, when it first reiterates for the first record, to add the name to the list, but after that from the 2nd record to the end of the file, to first check if this email is already in the List, if Yes, do not add it, if No, then add.

Those are my novice thoughts of how the program should run, but I am sure the Experts would be able to suggest a better version of it.  Thank you.
Private Sub mailemailreport_Click()
Dim rst As DAO.Recordset
Dim strDistList As String
Set rst = CurrentDb.OpenRecordset("Seniors Club")


Do While Not rst.EOF
    strDistList = strDistList & rst!Email & ";"
    rst.MoveNext
Loop
If MsgBox("Print Email distribution list to file?", vbYesNo, "Print?") = vbYes Then
  Dim lFile As Long
  '/get the next available File handle
  lFile = FreeFileHandle
  '/delete the file if it exists
  If Dir(CurrentProject.Path & "\EmailList.txt") <> "" Then Kill CurrentProject.Path & "\EmailList.txt"
  '/Now open the file
  Open CurrentProject.Path & "\EmailList.txt" For Output As #lFile
  '/write to that file
  Print #lFile, strDistList
  '/now close the file
  Close #lFile
Else
    strDistList = Left(strDistList, Len(strDistList) - 1)

    Me.Text46 = strDistList
End If

    

rst.Close
Set rst = Nothing

End Sub

Open in new window

0
Comment
Question by:jegajothy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 34937205
Personally i'd use a dictionary as the control mechanism ... try this change

Chris
Private Sub mailemailreport_Click()
Dim rst As DAO.Recordset
Dim strDistList As String
Dim dict As Object

    Set dict = CreateObject("scripting.dictionary")
    dict.comparemode = vbTextCompare
    Set rst = CurrentDb.OpenRecordset("Seniors Club")


Do While Not rst.EOF
    If Not dict.exists(rst!Email) Then
        dict.Add rst!Email, rst!Email
        strDistList = strDistList & rst!Email & ";"
    End If
    rst.MoveNext
Loop
If MsgBox("Print Email distribution list to file?", vbYesNo, "Print?") = vbYes Then
  Dim lFile As Long
  '/get the next available File handle
  lFile = FreeFileHandle
  '/delete the file if it exists
  If Dir(CurrentProject.Path & "\EmailList.txt") <> "" Then Kill CurrentProject.Path & "\EmailList.txt"
  '/Now open the file
  Open CurrentProject.Path & "\EmailList.txt" For Output As #lFile
  '/write to that file
  Print #lFile, strDistList
  '/now close the file
  Close #lFile
Else
    strDistList = Left(strDistList, Len(strDistList) - 1)

    Me.Text46 = strDistList
End If

    

rst.Close
Set rst = Nothing

End Sub

Open in new window

0
 

Author Comment

by:jegajothy
ID: 34937516
in response to chris, I tried your code, and it gave me one result, which was the wrong one.  a query run to find duplicates revealed  7 duplicates.  So I am not sure how the code ran.  How do I step thru the code.  in the design, I clicked on the step into, but after I closed it and then hit the command button, it just ran thru the entire code. thank u.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 34937609
Obviously I don't have your database so I cannot test in situ but the dictionary mechanism removes any duplicates.  I am using the same rst!Email parameter that you used so I don't see how the datum can be wrong ... and if there is only the one result was there only the one unique datum in the data set?

Chris
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:jegajothy
ID: 34937718
in reply to Chris, there are more than 1 unique datum.  Out of a total of 112 records,only 7 records are duplicates.  
While I am no Guru, but looking at the following syntax,     dict.comparemode = vbTextCompare
vbTextCompare has not been assigned or declared anywhere.
Thank u.
0
 

Author Comment

by:jegajothy
ID: 34937724
Attached is the database for u to take a look.  Please use the Form, where the code is in the command button. Thank u. email-database-Feb-21.accdb
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 2000 total points
ID: 34938049
The greater than and less than symbols were affecting the comparison

The following removes them on the assumption that is ok.

Chris
Private Sub btnDistList_Click()
Dim rst As DAO.Recordset
Dim strDistList As String
Dim dict As Object

    Set rst = CurrentDb.OpenRecordset("Seniors Club")
    Set dict = CreateObject("scripting.dictionary")
Stop
Do While Not rst.EOF
    If IsNull(rst!Email) Then
    Else
        If Not dict.exists(Replace(Replace(rst!Email, "<", ""), ">", "")) Then
            dict.Add Replace(Replace(rst!Email, "<", ""), ">", ""), Replace(Replace(rst!Email, "<", ""), ">", "")
            strDistList = strDistList & rst!Email & ";"
        End If
    End If
    rst.MoveNext
Loop

    strDistList = Left(strDistList, Len(strDistList) - 1)

    Me.txtDistList = strDistList

rst.Close
Set rst = Nothing
End Sub

Open in new window

0
 

Author Closing Comment

by:jegajothy
ID: 34938866
thank u this works.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 34938974
Glad to help but I should also express my appreciation at your providing the sample database as this made understanding the issue easy.

Chris
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question