Allow User Only Validation

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

Re: Allow User Only Validation

On Mon, 21 Jan 2008 14:38:22 -0800 (PST), kevinjouco@hotmail.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.



>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

Re: Allow User Only Validation

On Mon, 21 Jan 2008 19:32:05 -0700, Tom van Stiphout
wrote:

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

-Tom.



>On Mon, 21 Jan 2008 14:38:22 -0800 (PST), kevinjouco@hotmail.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.
>
>
>
>>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

Re: Allow User Only Validation

On 22 Jan, 04:26, Tom van Stiphout wrote:
> On Mon, 21 Jan 2008 19:32:05 -0700, Tom van Stiphout
>
> wrote:
>
> Oops, make that:
> DLookup("UserLoginName", "tblUsers", "UserID=3D" & Me.InspectorID)
>
> -Tom.
>
>
>
> >On Mon, 21 Jan 2008 14:38:22 -0800 (PST), kevinjo...@hotmail.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.htmyou 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=3DTrue:
> >Cancel =3D Not RecordOwnedByLoginUser()
> >If Cancel then Msgbox "Yo! =A0Not 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 =3D 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 =3D UCase$(DLookup("tblUsers", "UserLoginName",
> >"UserID=3D" & Me.InspectorID))=3DUCase$(fOSUserName())
> >End Function
>
> >-Tom.
>
> >>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- Hide quo=
ted text -
>
> - Show quoted text -

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

Re: Allow User Only Validation

have them log into the DB

wrote in message
news:c3122a5d-de13-44b4-b39e-1a98d0352eb6@1g2000hsl.googlegr oups.com...
On 22 Jan, 04:26, Tom van Stiphout wrote:
> On Mon, 21 Jan 2008 19:32:05 -0700, Tom van Stiphout
>
> wrote:
>
> Oops, make that:
> DLookup("UserLoginName", "tblUsers", "UserID=" & Me.InspectorID)
>
> -Tom.
>
>
>
> >On Mon, 21 Jan 2008 14:38:22 -0800 (PST), kevinjo...@hotmail.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.htmyou 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.
>
> >>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- Hide
> >>quoted text -
>
> - Show quoted text -

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

Re: Allow User Only Validation

On Tue, 22 Jan 2008 05:01:32 -0800 (PST), kevinjouco@hotmail.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.



>On 22 Jan, 04:26, Tom van Stiphout wrote:
>> On Mon, 21 Jan 2008 19:32:05 -0700, Tom van Stiphout
>>
>> wrote:
>>
>> Oops, make that:
>> DLookup("UserLoginName", "tblUsers", "UserID=" & Me.InspectorID)
>>
>> -Tom.
>>
>>
>>
>> >On Mon, 21 Jan 2008 14:38:22 -0800 (PST), kevinjo...@hotmail.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.htmyou 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.
>>
>> >>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- Hide quoted text -
>>
>> - Show quoted text -
>
>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

Re: Allow User Only Validation

"Tom van Stiphout" wrote in message
news:986dp39j9a7vk6aeptscogsjkv0c9drjea@4ax.com...
> On Tue, 22 Jan 2008 05:01:32 -0800 (PST), kevinjouco@hotmail.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.
>
>
>
> >On 22 Jan, 04:26, Tom van Stiphout wrote:
> >> On Mon, 21 Jan 2008 19:32:05 -0700, Tom van Stiphout
> >>
> >> wrote:
> >>
> >> Oops, make that:
> >> DLookup("UserLoginName", "tblUsers", "UserID=" & Me.InspectorID)
> >>
> >> -Tom.
> >>
> >>
> >>
> >> >On Mon, 21 Jan 2008 14:38:22 -0800 (PST), kevinjo...@hotmail.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.htmyou?wp_ml=0 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.
> >>
> >> >>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- Hide
quoted text -
> >>
> >> - Show quoted text -
> >
> >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

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.

Re: Allow User Only Validation

On 23 Jan, 13:15, "paii, Ron" wrote:
> "Tom van Stiphout" wrote in messagenews:986dp39j9a7vk6aeptscogsjkv0c9drjea@4ax.com...
>
>
>
>
>
> > On Tue, 22 Jan 2008 05:01:32 -0800 (PST), kevinjo...@hotmail.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.
>
> > >On 22 Jan, 04:26, Tom van Stiphout wrote:
> > >> On Mon, 21 Jan 2008 19:32:05 -0700, Tom van Stiphout
>
> > >> wrote:
>
> > >> Oops, make that:
> > >> DLookup("UserLoginName", "tblUsers", "UserID=" & Me.InspectorID)
>
> > >> -Tom.
>
> > >> >On Mon, 21 Jan 2008 14:38:22 -0800 (PST), kevinjo...@hotmail.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.htmyou?wp_ml=0can 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.
>
> > >> >>Please Help
>
> > >> >>We have 4 Inspectors carrying inspections and a lack of Trust, which
> > >> >>means we need tovalidatethe 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 tovalidatethe 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- Hide
> quoted text -
>
> > >> - Show quoted text -
>
> > >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
>
> 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.- Hide quoted text -
>
> - Show quoted text -

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

Re: Allow User Only Validation

On Jan 23, 6:07 pm, kevinjo...@hotmail.com wrote:
> On 23 Jan, 13:15, "paii, Ron" wrote:
>
>
>
> > "Tom van Stiphout" wrote in messagenews:986dp39j9a7vk6aeptscogsjkv0c9drjea@4ax.com...
>
> > > On Tue, 22 Jan 2008 05:01:32 -0800 (PST), kevinjo...@hotmail.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.
>
> > > >On 22 Jan, 04:26, Tom van Stiphout wrote:
> > > >> On Mon, 21 Jan 2008 19:32:05 -0700, Tom van Stiphout
>
> > > >> wrote:
>
> > > >> Oops, make that:
> > > >> DLookup("UserLoginName", "tblUsers", "UserID=" & Me.InspectorID)
>
> > > >> -Tom.
>
> > > >> >On Mon, 21 Jan 2008 14:38:22 -0800 (PST), kevinjo...@hotmail.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.htmyou?wp_ml=0canask 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.
>
> > > >> >>Please Help
>
> > > >> >>We have 4 Inspectors carrying inspections and a lack of Trust, which
> > > >> >>means we need tovalidatethe 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 tovalidatethe 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- Hide
> > quoted text -
>
> > > >> - Show quoted text -
>
> > > >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
>
> > 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.- Hide quoted text -
>
> > - Show quoted text -
>
> 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

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.