check for duplicates than append

[Rolls]

I have a table with two fields. Either may be null. Therefore they may not
be indexed. I want to check for the existence of a record equal to the
contents of two text boxes txtField1 and txtField2. If they are not already
present I want to append one record to a table after moving off the field.
This will prevent duplicate records from being entered. Can this be done
with a SQL statement?

 

[Guest]

Hi Rolls,

I don't know how to write an SQL only statement to do the check and append,
but I can with VBA and SQL.

If I understand right, you want to check for three conditions:

strField1 and strField2 have values,
strField1 has a value and strField2 is null or
strField2 has a value and strField 1 is null

So yo can have in your table:

strField1 strField2
John Jane
John NULL
NULL Jane


(You need a reference set for Microsoft DAO 3.6 Object Library.)

You will need to change "Table1" to the name of your table, "strField1" &
"strField2" to the names of your fields and "txtField1" & "txtField2" to the
names of the text boxes on your form.

The following code would be put in the click event of a button on the form:

(Watch for line wrap)

'---beg code-------------------------------------
Dim strSQL As String
Dim AppendSQL As String
Dim rst As DAO.Recordset
Dim RC As Integer 'RC = recordcount

'--------------------------------
'assumes both fields is Table1 are strings
'
'strField1 & strField2 - fields in Table1
'
'txtField1 & txtField1 - text boxes on form
'--------------------------------

If Not (IsNull(Me.txtField1) And IsNull(Me.txtField1)) Then
'one or both text boxes have entries

'create search string
strSQL = "Select strField1, strField2 FROM Table1"
strSQL = strSQL & " WHERE"

'create append string
AppendSQL = "INSERT INTO Table1"

'txtField1 has entry and txtField2 is null
If Not IsNull(Me.txtField1) And IsNull(Me.txtField2) Then
strSQL = strSQL & " strField1 = '" & Me.txtField1 & "'"
strSQL = strSQL & " AND strField2 IS NULL"

AppendSQL = AppendSQL & "(strField1) VALUES ('" & Me.txtField1 &
"');"


ElseIf IsNull(Me.txtField1) And Not IsNull(Me.txtField2) Then
'txtField1 is null and txtField2 has entry
strSQL = strSQL & " strField2 = '" & Me.txtField2 & "'"
strSQL = strSQL & " AND strField1 IS NULL"

AppendSQL = AppendSQL & "(strField2) VALUES ('" & Me.txtField2 &
"');"

'both txtField1 and txtField2 have entries
Else
strSQL = strSQL & " strField1 = '" & Me.txtField1 & "' AND "
strSQL = strSQL & " strField2 = '" & Me.txtField2 & "'"

AppendSQL = AppendSQL & "(strField1, strField2)"
AppendSQL = AppendSQL & " VALUES ('" & Me.txtField1 & "','" &
Me.txtField2 & "');"
End If

' Debug.Print strSQL
' Debug.Print AppendSQL

'open recordset
Set rst = CurrentDb.OpenRecordset(strSQL)
RC = rst.RecordCount
rst.Close
Set rst = Nothing

' MsgBox RC

'no records = no duplicates
If RC = 0 Then
'insert (append) new record
CurrentDb.Execute AppendSQL, dbfailonerror
MsgBox "Entries accepted!!"
Else
'Warning!! Warning!!
MsgBox "Duplicate entry found!!"
End If

Else
'both txtField1 and txtField2 are null/empty
MsgBox "No data to search on. Try again"
End If
'---beg code-------------------------------------



HTH