Error checking : Finding Duplicates

[Guest]

Basic concept:

private sub AppendData()
on error goto err_AppendData

dim stRptName as string
dim stCriteria as string
dim stSQL1, stSQL2 as string

stRptName = "Orientaion Training"
stCriteria = "[staffid]="&me![staffid]
stSQL1 = "Select from Training, [staffid] where [staffid]=forms!staff!staffid"
stSQL2 = "Select from [Training Courses],[TrnCrsId] and select from Staff,
[Staffid]"

if(docmd.runsql (stSQL1)=true)
{docmd.openreport stRptName,acPreview,,stCriteria}
else
{
docmd.runSql (stSQL2)
docmd.openrpt stRptName, acpreview,,stcriteria
}
endif

exit_AppendData:
exit sub

err_AppenData:
msgbox err.description
resume exit_AppendData

end sub

What I want to do is when a new hire is added to the database, the
orientation training courses get added to the training table, and open a
report, but I want to make sure that there is no duplicate staff entries as
well. I know that the code is not exact but the idea is there. Also I am not
too verse in the record set area, but when searching the help files, the
..NoMatch and .FindFirst came up alot.

 

[Guest]

It would be better if you check for duplicate staff id's in the Before Update
event of the staffid control:

If Not IsNull(DLookup("[staffid]", "StaffTable","[staffid] = '" &
&me![staffid] & "'")) Then
MsgBox "Staff Id already exists"
Cancel = True
End If