Find duplicate records in Access database

[Sheol]

Hello everyone! Can somebody please help me with this... I have a VB program that inputs Name and Address. I've made a database in MS Access using ADO Control:

Table Name: Contacts
Fields are: ID, CompanyName, CompanyAddress

My only problem is that how can I find duplicate entries in the CompanyName field. I have an ADD command button when adding new Company Name from a textbox. What I would like to do is that before the program add the Company Name in the textbox to the database, I wanted to check first if there is any duplicate. Ive made a code but it wont work...:

Private Sub cmdAdd_Click()
 
    adoWC.Recordset.MoveFirst
 
    while not adoWC.Recordset.EOF
 
         if txtCompanyName.text = adoWC.Recordset("CompanyName") then
            Msgbox("Duplicate Entry")
            Exit Sub
         else
            adoWC.Refresh
            adoWC.Recordset.MoveFirst
            adoWC.Recordset.AddNew
            adoWC.Recordset("CompanyName") = txtCompanyName.Text
            adoWC.Recordset("CompanyAddress") = txtCompanyAddress.Text
            adoWC.Recordset.Update
            adoWC.Recordset.MoveFirst
            Exit Sub
        end if
 
            adoWC.Recordset.MoveNext
 
End Sub

Can somebody help me... Thanks...

[muddasirmunir]

if you just want that no body enter duplicate name of company
just make company name field in access as a primary key.

Hello everyone! Can somebody please help me with this... I have a VB program that inputs Name and Address ...

[LacrosseB0ss]

Why do you need to find duplicates? I agree with the previous poster, declaring a primary key in Access is the easiest way to prevent this. However, if there's a fair amount of data, this could corrupt some fields.

The other way to do it is nested loops. The pseudo code is below: (letters correspond to indents (loops))
1. FOR EACH ITEM
2a. store next item in a string variable (call it strCurrent).
3a. FOR EACH ITEM
4b. compare current item with strCurrent (above)
5b. if duplicate, set a boolean to true (break loops)
6c. (if needed) add to string of duplicates
7b. else, get next
8b/a. END, END

Depending on the amount of data, this may take a while to run but it's quicker then 1 guy sitting reading through everything.

The 2nd part to this is adding a new record.
1. FOR EACH ITEM
2a. compare with input, if same - break loop, error
3a. else - add to DB
4. END

Hope this helps.
- LB

[debasisdas]

No need to go for all the tricky logics . I will give you a simple solution.

When the user clicks add read value from textbox and compare it with database records using count() . If count is 0 insert the record if 1 then you exit and display a message saying the entry already exists.

[Sheol]

hi muddasirmunir, LacrosseB0ss, debasisdas thanks to all your reply and help.
i have a sample code here debasisdas is this what you mean?

Dim noDUP as integer
 
nuDUP = "SELECT COUNT(CompanyName) FROM Contacts WHERE CompanyName = txtCompanyName.text"
 
if nuDUP = 0 then
    adoWC.Refresh
        adoWC.Recordset.MoveFirst
        adoWC.Recordset.AddNew
        adoWC.Recordset("CompanyName") = txtCompanyName.Text
        adoWC.Recordset("CompanyAddress") = txtCompanyAddress.Text
        adoWC.Recordset.Update
        adoWC.Recordset.MoveFirst
else
        Msgbox("Already Exists.")
 
End if

Thanks Again...

[LacrosseB0ss]

everything here looks good except for keeping txtCompanyName.text in the quotations in your SQL statement will look for that string, not the value in the text box. You'll need to say " ... WHERE CompanyName = " & txtCompanyName.text & ";".

Other than that, looks good. By the way, nice catch debasisdas. Much easier solution. (Y)