Hi, I found code here to prevent a record being written to the database if it already exists. I modified it and it doesn't seem to work and I have no idea why!
The user enters info about a page on a resource book. The primary key is Autonumber. The other fields can all be duplicated (the values are mainly picked from comboboxes). What I want to do is prevent duplication only if ALL the fields are the same. The code I have is here. When it runs, there are 3 records to match the criteria but it gives oRS.RecordCount of -1!!!! I would be grateful if someone could tell me where I'm going wrong.
Private Function DuplicateEntryDetected() As Boolean
Dim Conn As New ADODB.Connection
Dim oRS As ADODB.Recordset 'Load the data
Dim strSQL As String
'When a record is added, we need to check it is not already there
strSQL = "SELECT count(*) FROM Enquiry WHERE enqBook = '" & cboBook.Text
strSQL = strSQL & "' AND enqPage = '" & txtPage.Text & "' AND enqLevel = '" & cboLevel1.Text
strSQL = strSQL & "' AND enqGrammar = '" & cboGrammar.Text & "' AND enqLexis = '" & cboLexis.Text
strSQL = strSQL & "' AND enqActivity = '" & cboActivity.Text & "' AND enqSkill = '" & cboSkill.Text
strSQL = strSQL & "'"
'Now set the connection for Book
Set Conn = New Connection
Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AppPath & "Book Access.mdb;Persist Security Info=False"
Conn.ConnectionTimeout = 600
'here we set the connection to the recordset and update the database
Set oRS = New ADODB.Recordset
oRS.Open strSQL, Conn, adOpenForwardOnly, adLockOptimistic, adCmdText
If oRS.RecordCount > 0 Then
DuplicateEntryDetected = True
DuplicateEntryDetected = False
oRS.Close 'Tidy up
Set oRS = Nothing
Set Conn = Nothing