Prompt User when an Entry already Exist

permalink · #1 (**permalink**) 07-Nov-2005, 11:26 AM

Hi,

I want to allow users to add unique username into the database for clients.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/6819-prompt-user-when-entry-already-exist.html
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6819

*

permalink · #2 (**permalink**) 07-Nov-2005, 11:26 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6819
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/ex...tributors.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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6819

permalink · #3 (**permalink**) 07-Nov-2005, 11:26 AM

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:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6819
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6819

> 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/ex...tributors.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
>

permalink · #4 (**permalink**) 08-Nov-2005, 12:52 PM

> 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/ex...tributors.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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6819
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/ex...tributors.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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6819

permalink · #5 (**permalink**) 08-Nov-2005, 12:53 PM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6819
>
> 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/ex...tributors.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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6819
> 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/ex...tributors.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