 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / January 2008how to look up for a value in table?
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? 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 __________________________________________ > 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? 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. > Hi Ghost, > [quoted text clipped - 40 lines] > > that ID is already existed in the table and it should be revised > > How can I do that? 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 __________________________________________ > 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. 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.  SignatureRegards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ > 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? 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 __________________________________________ > 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 [quoted text clipped - 11 lines] > > that ID is already existed in the table and it should be revised > > How can I do that? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.