Avoiding duplicate entries

[paul]

I check for duplicates in the entry of organisationname
using the following code:

Private Sub OrganisationName_BeforeUpdate(Cancel As
Integer)
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim Response As Integer

On Error GoTo ErrorHandler

If (Me![OrganisationName] <> Me!
[OrganisationName].OldValue) Or IsNull(Me!
[OrganisationName].OldValue) Then
cnn.Open CurrentProject.Connection

rst.Open "SELECT * FROM pritblOrganisation WHERE
[OrganisationName] = " & Chr(34) & Me!OrganisationName &
Chr(34), cnn, adOpenForwardOnly
If Not rst.EOF Then
Response = MsgBox("A Record Already Exists for
this Organisation!" & Chr$(10) & Chr$(13) & "Do You Want
to Cancel the Entry?", 20, "Duplicate Organisation Name")
If Response = vbYes Then
Me.Undo
End If
End If
rst.Close
cnn.Close
End If
'End If
GoTo Done
ErrorHandler:
MsgBox Err.Description
Done:
End Sub

This works fine, but when I add a share-level password to
the database, open it and check whether this code works,
I get a message "Invalid Password" instead of the
expected mesaage of "Duplicate Organisation Name". I
could forget the share-level security password, but the
database requires restricted access. Any suggestions?

 

[Brendan Reynolds]

I'm assuming we're talking about Jet user-and-group-level security here? If
so, this code would be expected to fail if the user does not have read
permission on the "pritblOrganisation" table. If you don't want to give the
user read permission on the table directly, you can create an RWOP (Run With
Owner Permission) query based on the table, give the user read permission on
the query, and change the code to refer to the query rather than the table.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.