3 Replies - 2261 Views - Last Post: 22 January 2013 - 03:19 AM Rate Topic: -----

#1 anandrosan1  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 23
  • Joined: 20-December 12

How to avoid duplication record entry in access from vb6?

Posted 19 January 2013 - 06:01 PM

HI all. I am using Vb6 and Access 2007. I am adding records to the access table name "subjectcode" from vb6. The details of subjectcode table are below.

Subjectcode table : Heading(Degree,Branch,Year1,Year2,Semester,Subjectcode,Subjectname,Theory_Practical, Major_Allied_Elective) values (Bsc,computerscience,2001,2004,1,RACS1,Vb6 programming,Theory,Major)

Note : The primary key in the above table is Degree,Branch,Year1,Year2,Semester,Subjectcode
And the code i used to add entry to the access table from vb6 are given below :

If degree = "" Or branch1 = "" Or year1 = "" Or year2 = "" Or semester = "" Or subcode.Text = "" Or subname.Text = "" Or theory.Text = "" Or major.Text = "" Then
MsgBox "Fields can't be empty ! All are mandatory!"
    Else
        rs.Open "select * from subjectcode", con, 1, 3
        rs.AddNew
        rs!degree = degree
        rs!branch = branch1
        rs!year1 = year1
        rs!year2 = year2
        rs!semester = semester
        rs!Subjectcode = subcode.Text
        rs!Subjectname = subname.Text
        rs!Theory_Practical = theory.Text
        rs!Major_Allied_Elective = major.Text
        rs.Update
        MsgBox "Successfully Saved !", vbOKOnly + vbInformation, "info"
        rs.Close
End If



And the screenshot of that Add form of vb6 is here: http://tinypic.com/r/w7c7if/6
The record is added when the same entry is not exist. And if the record is already exist it should say "Record Already exists" and i don't know how to do that. Could you guys give me idea please.

Is This A Good Question/Topic? 0
  • +

Replies To: How to avoid duplication record entry in access from vb6?

#2 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2885
  • View blog
  • Posts: 9,575
  • Joined: 12-December 12

Re: How to avoid duplication record entry in access from vb6?

Posted 19 January 2013 - 06:20 PM

You need to first run a select query to retrieve a record based on the same information that you will be inserting. Or, more precisely, based on those fields which together confirm that the record will be a duplicate. Then check the RecordCount; if it is > 0 then the record already exists. (Hint: use rs.MoveLast, or just .MoveFirst, so that the RecordCount does not return -1).

BTW Using ADO to add a single record is overkill. You just need to construct and execute an SQL INSERT statement.

BTWW Using a multi-field (composite) primary key such as Degree,Branch,Year1,Year2,Semester,Subjectcode makes the database harder to maintain. You could use a separate AutoNumber field and make this combination of fields a unique index within the table, to prevent duplicate entries.

This post has been edited by andrewsw: 19 January 2013 - 06:22 PM

Was This Post Helpful? 1
  • +
  • -

#3 anandrosan1  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 23
  • Joined: 20-December 12

Re: How to avoid duplication record entry in access from vb6?

Posted 19 January 2013 - 11:47 PM

View Postandrewsw, on 20 January 2013 - 06:50 AM, said:

You need to first run a select query to retrieve a record based on the same information that you will be inserting. Or, more precisely, based on those fields which together confirm that the record will be a duplicate. Then check the RecordCount; if it is > 0 then the record already exists. (Hint: use rs.MoveLast, or just .MoveFirst, so that the RecordCount does not return -1).

BTW Using ADO to add a single record is overkill. You just need to construct and execute an SQL INSERT statement.

BTWW Using a multi-field (composite) primary key such as Degree,Branch,Year1,Year2,Semester,Subjectcode makes the database harder to maintain. You could use a separate AutoNumber field and make this combination of fields a unique index within the table, to prevent duplicate entries.

Thank you so much friend for the reply. Yes friend i got you but my situation and someone's necessity make me to use more than one field as primary key. Will update you soon n again thanks friend :)
Was This Post Helpful? 0
  • +
  • -

#4 anandrosan1  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 23
  • Joined: 20-December 12

Re: How to avoid duplication record entry in access from vb6?

Posted 22 January 2013 - 03:19 AM

View Postandrewsw, on 20 January 2013 - 06:50 AM, said:

You need to first run a select query to retrieve a record based on the same information that you will be inserting. Or, more precisely, based on those fields which together confirm that the record will be a duplicate. Then check the RecordCount; if it is > 0 then the record already exists. (Hint: use rs.MoveLast, or just .MoveFirst, so that the RecordCount does not return -1).

BTW Using ADO to add a single record is overkill. You just need to construct and execute an SQL INSERT statement.

BTWW Using a multi-field (composite) primary key such as Degree,Branch,Year1,Year2,Semester,Subjectcode makes the database harder to maintain. You could use a separate AutoNumber field and make this combination of fields a unique index within the table, to prevent duplicate entries.

Thank you so much friend.It worked perfectly and as usual your hint was much helpful. Sorry for the late reply friend.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1