Please Help

We have 4 Inspectors carrying inspections and a lack of Trust, which
means we need to validate the Inspections so that they are only able
to enter their user id when they have carried out the inspection and
not enter the other Inspectors ID accidently or on purpose.

We have produced the form with the necessary inspection details that
includes the Inspector field that requires protecting. At this time
the Inspectors are on a Combo Box Drop Down Menu and the question is
once the individual selects their user id from Drop Down Menu is it
possible to write some code that asks the user to enter their User id
password to validate the record and stop the record from being saved
until the correct user password is entered.

For Example

User 1 password "BB"
User 2 password "CC"
therefore if User 1 is selected from the DDMenu and enters "BB" when
asked this would allow the record to be saved and validated, however,
if User 1 entered "CC" the record would be stopped from saving and not
validated until User 1 entered the correct validation password

On Mon, 21 Jan 2008 14:38:22 -0800 (PST), kevinjouco (AT) hotmail (DOT) com
wrote:

Yes. If I understand you correctly all users can see all records, but
they can only edit (save) their own. Prompting for a password for each
record save will get real old real quick. There may be a better way.
I'm assuming each user logs into Windows with his own
username/password. Using the code described here
http://www.mvps.org/access/api/api0008.htm you can ask Windows who is
logged in.
I'm also assuming this is a standard form showing one inspection at a
time, and that the InspectorID is already written to the record at the
time it was created. Then it's just a simple matter of looking up the
InspectorLoginName given the InspectorID, and you can know if a record
was created by him/her or not. Let's call that function
RecordOwnedByLoginUser. Then you could write code in the BeforeUpdate
like you suggested to cancel the update if it's not a match. You do
that by setting Cancel=True:
Cancel = Not RecordOwnedByLoginUser()
If Cancel then Msgbox "Yo! Not your record!", vbCritical

Disadvantage: user can edit the record, and only when he tries to save
it, gets what he deserves.
I would prefer a different approach where you automatically lock the
record if it's not the user's. You do that in the Form_Current event,
with one line of code:
Me.AllowEdits = RecordOwnedByLoginUser()

The function RecordOwnedByLoginUser compares the UserName of the
current record in the form (given by its InspectorID) with the Windows
user login. So in the form's module I write:
private function RecordOwnedByLoginUser() as boolean
'This assumes a tblUsers table with UserID (used as InspectorID on
your form) and UserLoginName
RecordOwnedByLoginUser = UCase$(DLookup("tblUsers", "UserLoginName",
"UserID=" & Me.InspectorID))=UCase$(fOSUserName())
End Function

-Tom.

On Mon, 21 Jan 2008 19:32:05 -0700, Tom van Stiphout
<no.spam.tom7744 (AT) cox (DOT) net> wrote:

Oops, make that:
DLookup("UserLoginName", "tblUsers", "UserID=" & Me.InspectorID)

-Tom.

On 22 Jan, 04:26, Tom van Stiphout <no.spam.tom7... (AT) cox (DOT) net> wrote:
Thanks Tom for your excellent response and although this approach
would be ideal it is not practical as 3 out of the 4 of the inspectors
log on the same pc with same log on and use it at the same time
throughout the day, therefore we would need to apply the control to
the inspector field only if it is possible.

Would be grateful of any further suggestions

have them log into the DB

<kevinjouco (AT) hotmail (DOT) com> wrote

On 22 Jan, 04:26, Tom van Stiphout <no.spam.tom7... (AT) cox (DOT) net> wrote:
Thanks Tom for your excellent response and although this approach
would be ideal it is not practical as 3 out of the 4 of the inspectors
log on the same pc with same log on and use it at the same time
throughout the day, therefore we would need to apply the control to
the inspector field only if it is possible.

Would be grateful of any further suggestions

On Tue, 22 Jan 2008 05:01:32 -0800 (PST), kevinjouco (AT) hotmail (DOT) com
wrote:

Then rather than calling the Windows API code that I referenced, you
pop up a form when your Access app starts, asking for who the user is
(username/password dialog). Save the UserName value in a global
variable and make a minor change to the RecordOwnedByLoginUser
function.
When a user is finished with his edits, he closes the app. Next user
starts it again, and enters his own name and password.
Note that there also is a KB article to auto-close an Access
application after a certain amount of inactivity, just in case User1
forgot to close the app.

-Tom.

"Tom van Stiphout" <no.spam.tom7744 (AT) cox (DOT) net> wrote

With multiple inspectors using the same PC, you may need to add a device
such a Barcode or Security card reader, Biometric scanner ect. Access can
use the device to verify the user. User ID and Passwords are easily shared
and will most likely posted near the work station. With a device the
inspector would enter the information, press save and be prompted to
activate the reader.

On 23 Jan, 13:15, "paii, Ron" <n... (AT) no (DOT) com> wrote:
Thanks for the Replies they are all appreciated and obviously
effective, unfortunately, I sense that I will get into trouble with
applying the code for Tom suggestion, as my technical skills on Access
are at best basic and Ron's suggestion is not an option at this time.

I was really looking for a simple basic solution - as none of the
individuals concerned have used access to any great degree other than
data input, and there role only involves data input and restrictions
are applied to the Database to stop anyone tampering. The trust issue
is regarding innocent mistakes rather than malicious intent, therefore
the inspector field is the only one that requires protection (to stop
the inspector from selecting the wrong name from the drop down list).

Maybe a simple look up, matching the Inspector to a designated
password, will suffice, similar to excel, if this is possible

Would be grateful of any further suggestions

On Jan 23, 6:07 pm, kevinjo... (AT) hotmail (DOT) com wrote:
My first choice would be to drop the computer in the garbage and start
all over.

My second choice would be to pay someone to redesign and recreate the
db.

My third choice would be to make four or however many copies of the
form calling them Fred, Tom, Prudence, Jezebel etc. Then I'd edit each
form so that Fred's form input Fred's UserId, and Tom's input Tom's
UserId etc. Then I'd ask Fred to use the Fred form , and Tom to use
the Tom form and Prudence to use the Prudence form and Jezebel up to
my apartment for a drink.