[anandrosan1]

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.

[andrewsw]

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

[anandrosan1]

andrewsw, 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

[anandrosan1]

andrewsw, 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.