Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Prompt User when an Entry already Exist

Discussion in 'Information Technology' started by questionnaire database analyst, Nov 7, 2005.

  1. questionnaire database analyst

    Guest

    Hi,

    I want to allow users to add unique username into the database for clients.
    Because the username is currently not a primary key (and I don't want to make
    that the primary key), clients with the same username could be entered into
    the system. Is there any ways to aviod that? Do I need to write some code
    relating to EOF to always look up the whole column in the table? Thanks *10000
     
  2. Loading...


  3. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    You can set a unique index for the field in question, in table design view.
    The JET database engine will return an error message when a commit operation
    is attempted, if a user attempts to enter a duplicate value. The user will
    not be informed of the problem until they attempt to commit the record.
    Commits are done in several ways: navigating to a new record, selecting a
    record in a subform from a main form, or vice-versa, closing the form,
    clicking on a Save button, etc.

    If you want earlier notification, so that one doesn't finish filling out a
    bunch of fields before learning of the error, then you can use the
    BeforeUpdate event procedure for the textbox in question. An example is
    provided in the Northwind sample database (Northwind.mdb) for the Customers
    form, to prevent the duplicate entry of the text-based CustomerID value. You
    can either use DLookup, or you can open a recordset in VBA code to search the
    table for the value.


    Tom

    http://www.access.qbuilt.com/html/expert_contributors.html
    __________________________________________

    "questionnaire database analyst" wrote:

    Hi,

    I want to allow users to add unique username into the database for clients.
    Because the username is currently not a primary key (and I don't want to make
    that the primary key), clients with the same username could be entered into
    the system. Is there any ways to aviod that? Do I need to write some code
    relating to EOF to always look up the whole column in the table? Thanks *10000
     
  4. questionnaire database analyst

    Guest

    Thanks for your revise and I think setting the duplicate value in the table
    works... however, the message that pops up is kinda nasty... is that any
    other ways to prevent duplicate value?

    Because I don't have Northwind with me right now, I don't understand how the
    Dlookup or VBA code would work. Can you demonstrate that for me? thanks so
    much... :) thanks*1000
    "Tom Wickerath" wrote:

    > You can set a unique index for the field in question, in table design view.
    > The JET database engine will return an error message when a commit operation
    > is attempted, if a user attempts to enter a duplicate value. The user will
    > not be informed of the problem until they attempt to commit the record.
    > Commits are done in several ways: navigating to a new record, selecting a
    > record in a subform from a main form, or vice-versa, closing the form,
    > clicking on a Save button, etc.
    >
    > If you want earlier notification, so that one doesn't finish filling out a
    > bunch of fields before learning of the error, then you can use the
    > BeforeUpdate event procedure for the textbox in question. An example is
    > provided in the Northwind sample database (Northwind.mdb) for the Customers
    > form, to prevent the duplicate entry of the text-based CustomerID value. You
    > can either use DLookup, or you can open a recordset in VBA code to search the
    > table for the value.
    >
    >
    > Tom
    >
    > http://www.access.qbuilt.com/html/expert_contributors.html
    > __________________________________________
    >
    > "questionnaire database analyst" wrote:
    >
    > Hi,
    >
    > I want to allow users to add unique username into the database for clients.
    > Because the username is currently not a primary key (and I don't want to make
    > that the primary key), clients with the same username could be entered into
    > the system. Is there any ways to aviod that? Do I need to write some code
    > relating to EOF to always look up the whole column in the table? Thanks *10000
    >
     
  5. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    > however, the message that pops up is kinda nasty

    Yes, it is. You can trap for the error number and use a custom message. Here
    is an example for a form that has just one field with a unique index:

    Private Sub Form_Error(DataErr As Integer, Response As Integer)

    If DataErr = 3022 Then 'Unique Index violation

    MsgBox "You have attempted to enter a username that already exists." _
    & vbCrLf & "Please enter a new username.", _
    vbCritical, "Duplicate Value Detected..."
    Me.txtUserName.SetFocus
    Response = 0
    End If

    End Sub


    If you have more than one such field with a unique index, then the error
    message would be less helpful, since you could only say a duplicate exist in
    one of the following fields: Username, AnotherField, YetAnotherField, etc.

    Here is the DLookup method used in the Northwind sample:

    http://support.microsoft.com/?id=209487

    Note that while the title includes "ACC2000" and "Primary Key Fields", the
    technique should work equally well for other versions of Access and on
    non-key fields.


    Tom

    http://www.access.qbuilt.com/html/expert_contributors.html
    __________________________________________

    "questionnaire database analyst" wrote:

    Thanks for your revise and I think setting the duplicate value in the table
    works... however, the message that pops up is kinda nasty... is that any
    other ways to prevent duplicate value?

    Because I don't have Northwind with me right now, I don't understand how the
    Dlookup or VBA code would work. Can you demonstrate that for me? thanks so
    much... :) thanks*1000
    __________________________________________

    "Tom Wickerath" wrote:

    You can set a unique index for the field in question, in table design view.
    The JET database engine will return an error message when a commit operation
    is attempted, if a user attempts to enter a duplicate value. The user will
    not be informed of the problem until they attempt to commit the record.
    Commits are done in several ways: navigating to a new record, selecting a
    record in a subform from a main form, or vice-versa, closing the form,
    clicking on a Save button, etc.

    If you want earlier notification, so that one doesn't finish filling out a
    bunch of fields before learning of the error, then you can use the
    BeforeUpdate event procedure for the textbox in question. An example is
    provided in the Northwind sample database (Northwind.mdb) for the Customers
    form, to prevent the duplicate entry of the text-based CustomerID value. You
    can either use DLookup, or you can open a recordset in VBA code to search the
    table for the value.


    Tom

    http://www.access.qbuilt.com/html/expert_contributors.html
    __________________________________________

    "questionnaire database analyst" wrote:

    Hi,

    I want to allow users to add unique username into the database for clients.
    Because the username is currently not a primary key (and I don't want to make
    that the primary key), clients with the same username could be entered into
    the system. Is there any ways to aviod that? Do I need to write some code
    relating to EOF to always look up the whole column in the table? Thanks *10000
     
  6. questionnaire database analyst

    Guest

    Thank you so much! It works perfect!!!!

    I just have another question here regarding “The data has been changedâ€. I
    have a form that has multiple tabs. The 1st tab contains main form
    information while the 2nd tab and 3rd tab have two different sub forms. The
    problem is that if I change something in the 1st tab and then go to the 2nd
    or 3rd tab (the subforms) and change something, the following error message
    box pops up:

    “The data has been changed. Another user edited and saved the changes
    before you attempted to save your changes. Re-edit the record.â€

    I have found that if I refresh the form after I change something on the 1st
    tab, the error message would not appear when editing the subforms. However, I
    really do not like the refresh feature. And, users have been complaining
    that they DO NOT want to click any button when moving between tabs.

    I have tried the following commands and it didn’t work (error message pops
    up):
    1) RunCommand acCmdSaveRecord
    2) Me.Dirty = True
    3) Me.requery

    Thanks for answering the questions. :) Thanks*10000


    "Tom Wickerath" wrote:

    > > however, the message that pops up is kinda nasty

    >
    > Yes, it is. You can trap for the error number and use a custom message. Here
    > is an example for a form that has just one field with a unique index:
    >
    > Private Sub Form_Error(DataErr As Integer, Response As Integer)
    >
    > If DataErr = 3022 Then 'Unique Index violation
    >
    > MsgBox "You have attempted to enter a username that already exists." _
    > & vbCrLf & "Please enter a new username.", _
    > vbCritical, "Duplicate Value Detected..."
    > Me.txtUserName.SetFocus
    > Response = 0
    > End If
    >
    > End Sub
    >
    >
    > If you have more than one such field with a unique index, then the error
    > message would be less helpful, since you could only say a duplicate exist in
    > one of the following fields: Username, AnotherField, YetAnotherField, etc.
    >
    > Here is the DLookup method used in the Northwind sample:
    >
    > http://support.microsoft.com/?id=209487
    >
    > Note that while the title includes "ACC2000" and "Primary Key Fields", the
    > technique should work equally well for other versions of Access and on
    > non-key fields.
    >
    >
    > Tom
    >
    > http://www.access.qbuilt.com/html/expert_contributors.html
    > __________________________________________
    >
    > "questionnaire database analyst" wrote:
    >
    > Thanks for your revise and I think setting the duplicate value in the table
    > works... however, the message that pops up is kinda nasty... is that any
    > other ways to prevent duplicate value?
    >
    > Because I don't have Northwind with me right now, I don't understand how the
    > Dlookup or VBA code would work. Can you demonstrate that for me? thanks so
    > much... :) thanks*1000
    > __________________________________________
    >
    > "Tom Wickerath" wrote:
    >
    > You can set a unique index for the field in question, in table design view.
    > The JET database engine will return an error message when a commit operation
    > is attempted, if a user attempts to enter a duplicate value. The user will
    > not be informed of the problem until they attempt to commit the record.
    > Commits are done in several ways: navigating to a new record, selecting a
    > record in a subform from a main form, or vice-versa, closing the form,
    > clicking on a Save button, etc.
    >
    > If you want earlier notification, so that one doesn't finish filling out a
    > bunch of fields before learning of the error, then you can use the
    > BeforeUpdate event procedure for the textbox in question. An example is
    > provided in the Northwind sample database (Northwind.mdb) for the Customers
    > form, to prevent the duplicate entry of the text-based CustomerID value. You
    > can either use DLookup, or you can open a recordset in VBA code to search the
    > table for the value.
    >
    >
    > Tom
    >
    > http://www.access.qbuilt.com/html/expert_contributors.html
    > __________________________________________
    >
    > "questionnaire database analyst" wrote:
    >
    > Hi,
    >
    > I want to allow users to add unique username into the database for clients.
    > Because the username is currently not a primary key (and I don't want to make
    > that the primary key), clients with the same username could be entered into
    > the system. Is there any ways to aviod that? Do I need to write some code
    > relating to EOF to always look up the whole column in the table? Thanks *10000
     

Share This Page