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.
Thanks.
Code:
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
Conn.Open
'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
Beep
Else
DuplicateEntryDetected = False
End If
oRS.Close 'Tidy up
Conn.Close
Set oRS = Nothing
Set Conn = Nothing
End Function