Code experts help....

[gbanks]

I use this code to copy an employee name down to a table for storage. It works great.. My question is.. Is there a way to have the SQL line stop the code if the value ToVal is duplicated in the table the employee data is being saved too. So that if the user is trying to save duplicated data it will stop the process. I guess I can have the table set to a unique value but can it be done in this code.. Any help is appreciated Thanks....

Public Function EnterToNameSelection() As String
On Error GoTo EnterToName_Err
Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb()
Dim SQL$
Dim RetValue As String
Dim Toval As String

If IsNull(Forms![IDCReceiptForm]![To]) Then
MsgBox "No Employee name to add. To field is blank.", vbExclamation, "Add Employee Data Error"
GoTo EnterToName_Exit:

Else

Toval = Forms![IDCReceiptForm]![To]
RetValue = MsgBox("Warning you are about add the name " & Toval & " to the Employee table. Are you sure you want to add " & Toval & "? Click OK to Add Data. Click Cancel to Quit.", vbOKCancel + vbCritical, "Continue Adding Employee Name?")

If RetValue = 1 Then

MsgBox Toval & " Added to Employee table. Click Ok to Continue.", vbExclamation, "Completed"
Else
MsgBox "Record Not Added to Employee Table. Click Ok to Continue.", vbExclamation, "Cancelled"
GoTo EnterToName_Exit:
End If

SQL$ = "SELECT [Full Name]FROM [Employee List Table];"
Set rst = db.OpenRecordset(SQL$, dbOpenDynaset)
With rst
.AddNew
.Fields("Full Name") = Forms!IDCReceiptForm.To
.Update
End With

Set db = Nothing: Set rst = Nothing
End If

EnterToName_Exit:
Exit Function
EnterToName_Err:
MsgBox Error$
Resume EnterToName_Exit

End Function

 

[AncientOne]

What if you get two people with the same name? Do you want the option of accepting duplicates in that case? Is FullName a primary key in your table? Otherwise, you'll have to loop through the records to see if there's a match before invoking the SQL.

 

[WayneRyan]

gbanks,

Before doing the insert, use the DLookUp function to check
for the presence of the name.

Wayne

 

[IgorB]

Or recordset with NoMatch method

 

[RV]

Or,

Before doing the insert, use the

DCount function

to check for the presence of the name.

RV