Hi All
Hi i have form to enter employee details. When i try to enter a new EmployeeID it should check my table and if the id already exisits it should show an error message "data already exisits". How to do this part?
Hi All
Hi i have form to enter employee details. When i try to enter a new EmployeeID it should check my table and if the id already exisits it should show an error message "data already exisits". How to do this part?
Here's one way of doing this using the After_Update event for your form control.
It will show a warning message & clear the control
Substitute your control name, table name etcCode:Private Sub EmployeeID_AfterUpdate() If Nz(DLookup("EmployeeID", "YourTableName", "EmployeeID = '" & Me.EmployeeID & "'"), "") <> "" Then MsgBox "This Employee ID is already in use, vbCritical, "Error" Me.EmployeeID = "" End If End Sub
The above assumes you have an unbound control or are in data entry mode,
Other forum users may suggest a different approach
Why use EmployeddID Three times?
("EmployeeID", "YourTableName", "EmployeeID = '" & Me.EmployeeID & "'")
Because I'm guessing what your controls & fields are called....
OR use DCountCode:Private Sub FormControlName_AfterUpdate() If Nz(DLookup("EmployeeID", "YourTableName", "EmployeeID = '" & Me.FormControlName & "'"), "") <> "" Then MsgBox "This Employee ID is already in use, vbCritical, "Error" Me.FormControlName = "" End If End Sub
I'm also assuming EmployeeID is a stringCode:Private Sub FormControlName_AfterUpdate() If DCount("*", "YourTableName", "EmployeeID = '" & Me.FormControlName & "'") > 0 Then MsgBox "This Employee ID is already in use, vbCritical, "Error" Me.FormControlName = "" End If End Sub
If it's a number, replace with
Code:"EmployeeID = " & Me.FormControlName
why use the AfterUpdate event to check? If the form/table permits duplicate entries a new record might get created with a field that contains an empty string. I would use the BeforeUpdate event, and if found to already exist, undo the change. If the form control is bound, one could also attempt to alter the id using a value that already exists, and the suggested code would change this to "", possibly losing the old value.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
As I said in post 2:
I was expecting the Before_Update suggestion - equally valid & for many users the preferred method.The above assumes you have an unbound control or are in data entry mode,
Other forum users may suggest a different approach
However I'm assuming it's the PK field so I don't think this applies
If the form/table permits duplicate entries a new record might get created with a field that contains an empty string.
Thanks a lot guys. It worked.