SQL - check if record exists before adding

thmsjlmnt3953

Registered User.
Local time
Today, 20:39
Joined
May 20, 2014
Messages
120
Hi,

I currently use

Code:
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?
 
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?
 
What about the link I posted that shows how to refer to a form control?

If DCount(...) > 0 Then
 
Hi, i have looked but still unsure how to apply it to what i want to do?
 
If DCount("[Department]", "tblDepartments", "'" & txtnewdept & "'") = 0 Then
strSQL = "INSERT INTO tblDepartments (Department) VALUES(txtnewdept)"
End If
 
Hi,
Thanks for your replies, i've tried that as you suggested however it still fails. This is the code im using

Code:
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
 
You haven't specified the field in the criteria, as shown in the link.
 
Sorry - my fault

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

Code:
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.
 
I've added an extra step to say if the Department Name already exists, otherwise this seems to work fine:-

Code:
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
 

Users who are viewing this thread

Back
Top Bottom