DB Query - search

  • Thread starter Thread starter JBad
  • Start date Start date
J

JBad

hi again -
i wud want the user to enter the employee code. That ecode would be checked
in the table for a duplication and based on the result, if the ecode entered
is a unique/new one, the rest of the data is allowed to be entered else if
there is a duplication, an error message is shown.
cud someone tell me the easiest way to accomplish the above task? do i have
to do VBA coding or something?
thanks..
jb
 
Lets say that your table which contains the EmpCode field is called
TblEmployee

In Design View of the form with which you are inputting your data, Click on
the EmpCode field. Click the Properties button. On the Events tab,
choose Event Procedure next to After Update
Click just right of that to open a code page.
Above the line that says
End Sub

add

If NZ(DLookup("[EmpCode]","TblEmployee","[EmpCode]=" & Me.[EmpCode]),0) <> 0
Then
MsgBox Me.[EmpCode] & " already used. Try something else."
Me.[EmpCode].Undo
End IF



the NZ(DLookup bit checks EmpCode field in TblEmployee for the contents of
the Empcode field in your form (that's the Me.[EmpCode] bit)

If it can't find it, then it returns the value of 0 so you know that your
newly entered Empcode hasn't been used yet.
If it finds your code, then a MsgBox pops up and the contents of the EmpCode
field are deleted for the user to try again (Me.[EmpCode].Undo)

Evi
 
Back
Top