how to look up for a value in table?

[ghost]

Greeting,
I have a form to input a data in a table. In this form there is a filed for
employee ID which is the primary key. What I want to do is if the user inputs
the employee id which is duplicated that a message box appears and shows user
that ID is already existed in the table and it should be revised
How can I do that?

 

[Tom Wickerath]

Hi Ghost,

Try trapping for Error 3022, as shown on page 15 of the current copy of my
Access Links.doc Word document. You are welcome to download a copy, here:

http://www.accessmvp.com/TWickerath/


Something like this will catch the error when one attempts to save the record:

Option Compare Database
Option Explicit

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Select Case DataErr
Case 3022
MsgBox "This Employee ID has already been added." & vbCrLf & _
"You cannot create duplicates.", _
vbOKOnly + vbInformation, "EmployeeID Already Added..."
Me.EmployeeID = Null
Me.EmployeeID.SetFocus
Response = acDataErrContinue
Case Else
Response = acDataErrDisplay
End Select

End Sub


If you'd like to catch the error as soon as the user changes focus from your
Employee ID field to another field, then you can use the DLookup Domain
Aggregrate function in the BeforeUpdate event procedure for the text box.
Post back if that's what you'd like to do.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[ghost]

Hi Tom,
How are you doing? Hope you or fine
Thank you for your fast response.

Yes please, I need to that by using before update event and with DLookup
Domain
aggregate function.

 

[Tom Wickerath]

Try something like this for a control named EmployeeID, bound to an
EmployeeID field, in a table named Employees (you can test this code using
the Employees form in the sample Northwind.mdb database):

Private Sub EmployeeID_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError

If Not IsNull(DLookup( _
"[EmployeeID]", "[Employees]", "[EmployeeID] = " & [EmployeeID])) Then

MsgBox "The Employee ID you entered already exists. " _
& "Enter a unique ID.", vbOKOnly + vbCritical, _
"Duplicate Employee ID..."

DoCmd.CancelEvent
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure EmployeeID_BeforeUpdate..."
Resume ExitProc
End Sub


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Jeff Boyce]

In addition to Tom's solution, another approach would be to use a combobox.
If your users actually remember and use an EmployeeID (over something more
user-friendly, say, a name), having them enter it into a combobox set to
list the EmployeeIDs and AutoComplete means they'll see right off if the
EmployeeID is in the list of current employees.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Tom Wickerath]

I actually have a "how-to" article that covers the technique Jeff mentions:

Combo box to find a record
http://www.access.qbuilt.com/html/find_a_record.html


Also, I forgot to mention this, but if you do use the standard Northwind.mdb
file to test the code I presented earlier, change the Autonumber primary key
to a Number first. (I had actually copied the Employees table and data to a
new table named Employees2, made the change there to a Number / Long Interger
primary key, and referenced this new table in my code).


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________