Results 1 to 7 of 7
  1. #1
    Eranka is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    150

    Showing Error Message In Access

    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?

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    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

    Code:
    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
    Substitute your control name, table name etc

    The above assumes you have an unbound control or are in data entry mode,
    Other forum users may suggest a different approach

  3. #3
    Eranka is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    150
    Why use EmployeddID Three times?

    ("EmployeeID", "YourTableName", "EmployeeID = '" & Me.EmployeeID & "'")

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Because I'm guessing what your controls & fields are called....

    Code:
    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
    OR use DCount

    Code:
    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
    I'm also assuming EmployeeID is a string
    If it's a number, replace with
    Code:
    "EmployeeID = " & Me.FormControlName

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    As I said in post 2:
    The above assumes you have an unbound control or are in data entry mode,
    Other forum users may suggest a different approach
    I was expecting the Before_Update suggestion - equally valid & for many users the preferred method.
    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.

  7. #7
    Eranka is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    150
    Thanks a lot guys. It worked.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 02-08-2023, 05:22 AM
  2. Replies: 0
    Last Post: 03-18-2017, 03:40 AM
  3. Replies: 1
    Last Post: 07-26-2016, 06:34 AM
  4. Replies: 7
    Last Post: 03-17-2016, 05:53 PM
  5. Replies: 6
    Last Post: 02-06-2015, 04:10 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums