Prompt User when an Entry already Exist

[questionnaire database analyst]

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

 

[Tom Wickerath]

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
__________________________________________

:

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

 

[questionnaire database analyst]

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]

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
__________________________________________

:

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
__________________________________________

:

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
__________________________________________

:

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

 

[questionnaire database analyst]

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