Results 1 to 5 of 5
  1. #1
    jabarlee is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    49

    Duplicate entry does not throw error message

    Hello everyone.



    I have a bound form in which the user can change his own username. The [Username] field is unique (not PK). In the "submit changes" button sub, a have some code to set the new username as a TempVars value for later use in my application, but this part is probably irrelevant to my problem.
    The problem is that when the user inputs an already registered username, it does not throw an error, so I can grab it and do some minimal error handling. The form closes (as it should), the record does not gets updated with the duplicate username (as it should), but I don't get an error of any kind.
    Code:
    Private Sub btn_Ok_Click()
    
    
        If Me.Dirty Then
            If Me.txt_Username.OldValue <> Me.txt_Username.Value Then
                TempVars("Username") = Me.txt_Username.Value
                DoCmd.Save
                DoCmd.Close acForm, Me.Name
                Exit Sub
            Else
                DoCmd.Save
                DoCmd.Close acForm, Me.Name
                Exit Sub
            End If
        End If
    
    
    
    End Sub
    Any ideas on what I should do?
    Many thanks in advance.

  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
    Try adding code to the before update event of the textbox in which you should do a DCount of that username in the table.
    If DCount>0 then display a message stating the username already exists and clear the textbox.
    If no match then allow code to proceed
    Last edited by isladogs; 04-25-2018 at 07:49 AM.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    so r52 has posted a way to deal with it.... but there is no error thrown because there is no error. Without the table property being either PK or at least Indexed, No Duplicates - then there is no system error if a duplicate occurs. There is only a business error in your eyes - which maybe should be handled someway in the ELSE clause, maybe a pop up message or whatever......

  4. #4
    jabarlee is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    49
    Well, an update.
    First of all thank you for your answers.
    NTC, as I mentioned - probably not clear enough - the field is unique, (indexed, no duplicates).

    before I tried ridder's suggestion, I noticed that in my other forms I had
    Code:
    DoCmd.RunCommand acCmdSaveRecord
    instead of
    Code:
    DoCmd.Save
    . So I tried that, and I got the error I was looking for.
    If I am correct the second is used to save objects like forms, controls, tables, queries, etc. While the first one is the proper for saving records?

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I was going to say there is no error because DoCmd.Save saves an object, which in this case, isn't specified, so it will be the active object, which is not a record. Then I noticed the relevant question on this. Additionally, I think some of us here would say RunCommand methods have generally been used less and less and aren't recommended. I think it's to avoid any dialog boxes that might appear because RunCommand simulates ribbon menu commands and won't accept parameters like direct DoCmd methods will. DoCmd also provides intellisense help, plus it accepts parameters if there are any and doesn't present dialogs.
    Me.Dirty = False will also save a record.
    So I think there's no error because there's no record update.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 4
    Last Post: 01-14-2016, 02:18 PM
  2. Duplicate Record Error Message!!
    By forumer in forum Forms
    Replies: 6
    Last Post: 03-14-2014, 07:25 AM
  3. Customise the duplicate values error message
    By lsmcal1984 in forum Forms
    Replies: 2
    Last Post: 10-09-2013, 04:36 PM
  4. Duplicate Entry Error
    By Dee300 in forum Forms
    Replies: 3
    Last Post: 08-16-2011, 07:29 PM
  5. Error Message re: data entry of date
    By Pro-not in forum Access
    Replies: 9
    Last Post: 10-22-2010, 10:14 AM

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