Field Level Password

[Jaybird]

This is a repost. Sorry. I never got any responses to my earlier question.

OK, I'm trying to use a popup form to identify the user entering into a
field. I've got a field named [QA] that I want to secure against anyone
without the correct password. Only Qualified Quality personnel are allowed
to enter information here. It's kind of like a virtual stamp. We use an
actual stamp now. I'm trying to re-create the log electronically. Anyhow,
when the user attempts to enter the field [QA] this event procedure is fired:

Private Sub QA_Enter()
DoCmd.OpenForm "frmEnterPassword", acNormal, , , , , "[QA]"
End Sub

This opens up a form which is supposed to verify that the password entered
matches the UserName of the person entering it. If they match, the UserName
is passed on. If they don't, the form just hangs there. It's triggered by
clicking on the CloseForm button. Here's the code:

Private Sub CloseForm_Click()
On Error GoTo Err_CloseForm_Click

If Len(Me.UserName) = 0 Or Len(Me.txtPassword) = 0 Then Exit Sub
strPassword = DLookup("[Password]", "Users", [UserName] = Me.UserName)
If strPassword = Me.txtPassword Then
Forms![HT Load Info].Controls(Me.OpenArgs) = Me.UserName
DoCmd.Close

Exit_CloseForm_Click:
Exit Sub
End If

Exit Sub

Err_CloseForm_Click:
MsgBox Err.Description
Resume Exit_CloseForm_Click

End Sub

It only kind of, sort of works... Depending on which record is being
entered into, sometimes the password for the previous record will work while
the current user password won't. I thought perhaps my cavalier use of the
name UserName was causing some confusion, but I changed the name in all of
code and the textbox without positive results. Perhaps the big brains at
this forum can help?

 

[Jamie Collins]

OK, I'm trying to use a popup form to identify the user entering into a
field <<snipped>>

Why reinvent the wheel? Why not use Jet's user level security? e.g.
ANSI-92 Query Mode SQL DCL:

CREATE USER Tester1 TheirPassword TheirPID
;
CREATE GROUP Testers GroupsPID
;
ADD USER Tester1 TO Testers
;
CREATE TABLE Test
(
public_column INTEGER NOT NULL UNIQUE,
private_column INTEGER
)
;
REVOKE ALL PRIVILEGES ON Test FROM Testers
;
REVOKE ALL PRIVILEGES ON Test FROM Public
;
CREATE VIEW PublicTest
AS
SELECT public_column
FROM Test
;
GRANT ALL PRIVILEGES ON PublicTest TO Public
;
CREATE VIEW PrivateTest
AS
SELECT public_column, private_column
FROM Test
;
GRANT ALL PRIVILEGES ON PrivateTest TO Testers
;

Jamie.

--

 

[Jaybird]

I agree 100% with you. The problem is that our network is set to bypass the
logon. Crazy? Yep. Not a thing I can do about it but shake my head. I do
appreciate your response and for showing me how to administer this technique.
When we finally get out of the stone throwing stage of database management,
I'm sure I will apply something like it.