Duplicate Records Problems...

[Mike Vytal]

Can someone please look at the following code and explain why it's duplicating records in my MS Access database. THANX IN ADVANCE for your help!!!!

' FOR SAVING DATA TO THE DATABASE ******************************************
'***************************************************************************

'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsAddRecord 'Holds the recordset for the new record to be added
Dim rsID 'Holds the recordset for capturing current record number
Dim strSQL 'Holds the SQL query to query the database
Dim RecordNum 'Variable that holds current record number

'ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")
'ADO recordset object to hold record for database
Set rsAddRecord = Server.CreateObject("ADODB.Recordset")
'ADO recordset object for capturing current record number
set rsID = server.createobject("ADODB.Recordset")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("WebForm.mdb")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT * FROM tbl_Request_CommDept_Print;"

'Set the cursor type we are using so we can navigate through the recordset
rsAddRecord.CursorType = 2

'Set the lock type so that the record is locked by ADO when it is updated
rsAddRecord.LockType = 3

'Open the recordset with the SQL query
rsAddRecord.Open strSQL, adoCon

'Tell the recordset we are adding a new record to it
rsAddRecord.AddNew

'Add a new record to the recordset
rsAddRecord.Fields("TitleOfJob") = request.form("TitleOfJob")
rsAddRecord.Fields("RequestDate") = request.form("RequestDate")
rsAddRecord.Fields("DueDate") = request.form("DueDate")
rsAddRecord.Fields("RequestBy") = request.form("RequestBy")
rsAddRecord.Fields("Dept") = request.form("Dept")
rsAddRecord.Fields("Email") = request.form("Email")
rsAddRecord.Fields("Print_Political") = request.form("Print_Political")
'-----------------------------
rsAddRecord.Fields("Print_JobType") = request.Form("Print_JobType")
rsAddRecord.Fields("Print_Other") = request.Form("Print_Other")
rsAddRecord.Fields("Print_Color") = request.Form("Print_Color")
rsAddRecord.Fields("Attached") = request.Form("FileName21") 'request.Form("FileName")
rsAddRecord.Fields("Attached1") = request.Form("FileName2")
rsAddRecord.Fields("Attached2") = request.Form("FileName3")
rsAddRecord.Fields("Print_Pictures") = request.Form("Print_Pictures")
rsAddRecord.Fields("Print_Layout") = request.Form("Print_Layout")
rsAddRecord.Fields("Print_Translation") = replace(request.Form("Print_Translation"),"'","")
rsAddRecord.Fields("Print_Quantity") = request.Form("Print_Quantity")
rsAddRecord.Fields("Print_ToBeMailed") = request.Form("Print_ToBeMailed")
rsAddRecord.Fields("Print_OtherPieces") = request.Form("Print_OtherPieces")
rsAddRecord.Fields("Print_PrintingPieces") = request.Form("Print_PrintingPieces")
rsAddRecord.Fields("Print_Comments") = replace(request.Form("Print_Comments"),"'","")
'-----------------------------
rsAddRecord.Fields("Mail_MailingPieces") = request.Form("Mail_MailingPieces")
rsAddRecord.Fields("Mail_MailPieceType") = request.Form("Mail_MailPieceType")
rsAddRecord.Fields("Mail_Area") = replace(request.Form("Mail_Area"),"'","")
rsAddRecord.Fields("Mail_Area_Desc") = replace(request.Form("Mail_Area_Desc"),"'","")

'Write the updated recordset to the database
rsAddRecord.Update


'Go to last record of the recordset and give an alias "NewReq"
rsID.Open "SELECT MAX(ReqNum) AS NewReq FROM tbl_Request_CommDept_Print", adoCon
'Assiqn variable "RecordNum" to the NewReq field; RecordNum stores that specific data *** Capture ReqNum ***
RecordNum = rsID.Fields("NewReq").Value
rsID.Close

 

[DanG]

I am not sure about the code but controling duplicates should be done at the table level.

You are better posting your question in the Access thread.