Error checking : Finding Duplicates

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
Back
Top