SQL - check if record exists before adding

[thmsjlmnt3953]

Hi,

I currently use

strSQL = "INSERT INTO tblDepartments (Department) VALUES(txtnewdept)"

To insert new departments into a table, however id like it to check to see if a department name exists in tbldepartments.department to prevent duplicates being added?

 

[pbaldy]

You can use DCount() and test for greater than 0.

http://access.mvps.org/access/general/gen0018.htm

 

[thmsjlmnt3953]

Hi,
I cant seem to find reference to DCount that would check to see if what im inserting is the same as any records in the table?

 

[pbaldy]

What about the link I posted that shows how to refer to a form control?

If DCount(...) > 0 Then

 

[thmsjlmnt3953]

Hi, i have looked but still unsure how to apply it to what i want to do?

 

[Cotty42]

If DCount("[Department]", "tblDepartments", "'" & txtnewdept & "'") = 0 Then
strSQL = "INSERT INTO tblDepartments (Department) VALUES(txtnewdept)"
End If

 

[thmsjlmnt3953]

Hi,
Thanks for your replies, i've tried that as you suggested however it still fails. This is the code im using

Private Sub Command7_Click()
txtNewDept.SetFocus
If txtNewDept.Text = "" Then
err = err + 1
MsgBox "Please Enter a Department", vbOKOnly, "Error"
End If
If err >= 1 Then
End
End If
If DCount("[Department]", "tblDepartments", "'" & txtNewDept & "'") = 0 And MsgBox("Do you wish to create a new department?", vbOKCancel, "New Department") = vbOK Then
Dim strSQL As String
strSQL = "INSERT INTO tblDepartments (Department) VALUES(txtnewdept)"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "Department Added Successfully", vbOKOnly, "Sucess"
Else
MsgBox "Department not created", vbOKOnly, "Not Created"
End
End If
txtNewDept.SetFocus
txtNewDept.Value = Null
Me.Requery
End Sub

 

[pbaldy]

You haven't specified the field in the criteria, as shown in the link.

 

[thmsjlmnt3953]

Im at a loss :banghead:

 

[Cotty42]

Sorry - my fault

If DCount("[Department]", "tblDepartments", "[Department] = '" & txtnewdept & "'") = 0 Then
strSQL = "INSERT INTO tblDepartments (Department) VALUES(txtnewdept)"
End If

 

[pbaldy]

I currently use

strSQL = "INSERT INTO tblDepartments (Department) VALUES(txtnewdept)"

I'd be very surprised if that SQL executes correctly by the way. I would expect the value to be concatenated into the string.

 

[Cotty42]

I've added an extra step to say if the Department Name already exists, otherwise this seems to work fine:-

Private Sub Command7_Click()
    
    txtnewdept.SetFocus
    
    If txtnewdept.Text = "" Then
        Err = Err + 1
        MsgBox "Please Enter a Department", vbOKOnly, "Error"
    End If
    
    If Err >= 1 Then
        End
    End If
    
    RecordExists = DCount("[Department]", "tblDepartments", "[Department] = '" & txtnewdept & "'")
    If RecordExists <> 0 Then
        MsgBox "Department already exists", vbOKOnly, "Create New Dept"
    ElseIf MsgBox("Do you wish to create a new department?", vbOKCancel, "New Department") = vbOK Then
        Dim strSQL As String
        strSQL = "INSERT INTO tblDepartments (Department) VALUES(txtnewdept)"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        MsgBox "Department Added Successfully", vbOKOnly, "Sucess"
    Else
        MsgBox "Department not created", vbOKOnly, "Not Created"
        Me.txtnewdept = Null
        End
    End If
    
    txtnewdept.SetFocus
    txtnewdept.Value = Null
    Me.Requery
End Sub