Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms Programming / May 2007

Tip: Looking for answers? Try searching our database.

Restricting entry on tab pages

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
BruceM - 11 May 2007 20:55 GMT
I have a database for problem reporting, with user-level security in place
(test version so far).  Each record involves four steps: Problem
Description, Response, Follow-up, and Final Approval.  I want to make sure
that only the person who wrote the Problem Description can edit it; the same
for Response, Follow-up, and Final Approval.
Each section (step) is on its own page of a tab control.  The following
function, which I call at the tab controls Change event and at the form's
Current event, seems to work, but it seems convoluted.  I keep thinking I
must be missing something that would simplify this.

Public Function EditAllow(frm As Form)

 Dim ctl As Control
 Dim strTabCapt As String

 strTabCapt = frm.tabCAR.Pages(frm.tabCAR.Value).Caption    ' The tab
page's caption

 Select Case strTabCapt
   Case "Problem Description"
     If frm.CurUser = CurrentUser Then
         ' The CurrentUser is inserted into the CurUser field
         ' when the Problem Description is initially entered
       For Each ctl In frm.Controls
         If ctl.Tag = "PD" Then
           ctl.Locked = False
           Else
             Select Case ctl.ControlType
               Case acTextBox, acComboBox, acSubform
                 ctl.Locked = True
             End Select
         End If
       Next ctl
       Else
         For Each ctl In frm.Controls
           Select Case ctl.ControlType
             Case acTextBox, acComboBox, acSubform
               ctl.Locked = True
           End Select
         Next ctl
     End If
   Case "Response"
     If frm.OwnerSig = CurrentUser Then
      etc. etc.
 End Select

End Function

Case "Response", Case "Follow-up", and Case "Final Approval" are the same as
Case "Problem Description", so I didn't repeat the code.  I left out a few
other things that are not relevant to the question at hand, including a
provision that allows a member of the Admins group to edit any controls.

Again, each step is on its own page of the tab control.  If the person
currently logged on is the same person who entered the information at a
particular step (Problem Description, Response, etc.), that person can edit
the controls on that tab page.  The reason for checking the tag property is
that there are controls on the form but not on the tab page that need to
stay locked.

These four lines occur several times:
           Select Case ctl.ControlType
             Case acTextBox, acComboBox, acSubform
               ctl.Locked = True
           End Select

I would think it is possible to turn those into a function or constant or
something, and use a single line of code in place of the four lines in the
function, but I can't quite figure out how to go about that.  I haven't had
any luck getting it to work as a function.  Before I try creating a constant
or a string or something I would like to know if I am on the right track.

Any ideas to streamline this would be appreciated.

I will be away until Monday morning, so please don't think me rude if you
respond and I don't acknowledge it right away.
BruceM - 14 May 2007 13:11 GMT
After some more experimentation I have come up with the following.  To sum
up the situation, I want to restrict editing of controls on a tab page to
the person who made the initial entry.  I am using user-level security, with
each user having a customized login.  CurrentUser is stored when the entry
is completed on each of four tab pages.

Public Function LockCtl()

 Dim ctl As Control

 For Each ctl In Me.Controls
   Select Case ctl.ControlType
     Case acTextBox, acComboBox, acSubform
       ctl.Locked = True
   End Select
 Next ctl

End Function

Public Function EditAllow()

 Dim ctl As Control
 Dim strTabCapt As String

 strTabCapt = Me.tabCAR.Pages(Me.tabCAR.Value).Caption    ' Current tab
page caption

 Select Case strTabCapt
   Case "Problem Description"
     '  CurrentUser who completed ProblemDescription fields is stored in
CurUserInit
     If Me.CurUserInit = CurrentUser Then
       For Each ctl In Me.Controls
         If ctl.Tag = "PD" Then    ' Applies to controls on the "Problem
Description" tab only
           ctl.Locked = False
           Else
             Select Case ctl.ControlType
               Case acTextBox, acComboBox, acSubform
                 ctl.Locked = True
             End Select
         End If
       Next ctl
       Else
         Call LockCtl
     End If
   Case "Response"
       '  CurrentUser who completed Response fields is stored in
Me.Responder
       If Me.Responder = CurrentUser Then
           For Each ctl in Me.Controls
               If ctl.Tag = "Resp" Then    ' Applies to controls on the
"Response" tab only
                   ctl.Locked = False
                   etc.    ' The code repeats, with a different tag and
different CurrentUser field for each tab page
   End Select

EndFunction

I have figured out how to use a function (LockCtl) to replace part of the
code in EditAllow, but it still seems clumsy in that there is a lot of
repeated code.  The code for each tab page is the same, with two exceptions:
CurrentUser is stored in a different field for each tab page, and the Tag is
different for each tab page.

The code seems to work as intended, but I tend to be concerned when there is
a lot of repeated code, in that future code modifications need to occur in a
number of different places, which increases the chances of hard-to-find
errors.

>I have a database for problem reporting, with user-level security in place
>(test version so far).  Each record involves four steps: Problem
[quoted text clipped - 73 lines]
> I will be away until Monday morning, so please don't think me rude if you
> respond and I don't acknowledge it right away.
SteveS - 14 May 2007 21:42 GMT
Hi Bruce,

Here is an attempt to modify your code. It is *untested*, but maybe it will
help a little.

'----------------------------------
Public Function EditAllow(frm As Form)

 Dim ctl As Control
 Dim strTabCapt As String

 ' The tab   Page's caption
 strTabCapt = frm.tabCAR.Pages(frm.tabCAR.value).Caption    

 Select Case strTabCapt
   Case "Problem Description"
     If frm.CurUser = CurrentUser Then
       ' The CurrentUser is inserted into the CurUser field
       ' when the Problem Description is initially entered
       Call CtlUnLock(frm, "PD")
     Else
       Call CtlLock(frm)
     End If
   Case "Response"
     If frm.OwnerSig = CurrentUser Then
       Call CtlUnLock(frm, "R")
     Else
       Call CtlLock(frm)
     End If
   Case "Follow-up"
     If frm.FollowUpSig = CurrentUser Then
       Call CtlUnLock(frm, "F")
     Else
       Call CtlLock(frm)
     End If
   Case "Final Approval"
     If frm.FinalApprovalSig = CurrentUser Then
       Call CtlUnLock(frm, "FA")
     Else
       Call CtlLock(frm)
     End If
 End Select

End Function

'------
Public Sub CtlUnLock(frm As Form, WhatTag As String)
 Dim ctl As Control

 For Each ctl In frm.Controls
   If ctl.Tag = WhatTag Then
     ctl.Locked = False
   Else
     Select Case ctl.ControlType
       Case acTextBox, acComboBox, acSubform
         ctl.Locked = True
     End Select
   End If
 Next ctl
End Sub

'------
Public Sub CtlLock(frm As Form)
 Dim ctl As Control

 For Each ctl In frm.Controls
   Select Case ctl.ControlType
     Case acTextBox, acComboBox, acSubform
       ctl.Locked = True
   End Select
 Next ctl
End Sub

'-------------------------------------------

HTH
Signature

Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

> After some more experimentation I have come up with the following.  To sum
> up the situation, I want to restrict editing of controls on a tab page to
[quoted text clipped - 144 lines]
> > I will be away until Monday morning, so please don't think me rude if you
> > respond and I don't acknowledge it right away.
BruceM - 15 May 2007 15:25 GMT
Thanks for the reply.  That seems to do the trick.  I just couldn't get my
brain around how to insert the tag into the function, but as soon as I saw
your code I was pretty sure it would work as intended.  Since the code will
only be useful in this one particular form, I put the functions into the
form's code module and used Me instead of frm (If Me.CurUser... etc.).
Also, I used functions instead of subs, but I doubt that matters.  In fact,
I have no basis for choosing one over the other in a context such as this.
Functions (unlike subs, I think) can be used as the event property, which I
do sometimes (navigation buttons, for instance), so I tend to use functions
in general, but as I said I doubt there is much difference between a
function call and a sub call.
Anyhow, that string for the tag was the missing piece, so thanks again.

> Hi Bruce,
>
[quoted text clipped - 240 lines]
>> > you
>> > respond and I don't acknowledge it right away.
SteveS - 15 May 2007 23:29 GMT
I tend to use Subx unless I need to return a value. But I don't think it
matters too much.

Glad you were able to get it to work.  :)

Signature

Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

> Thanks for the reply.  That seems to do the trick.  I just couldn't get my
> brain around how to insert the tag into the function, but as soon as I saw
[quoted text clipped - 253 lines]
> >> > you
> >> > respond and I don't acknowledge it right away.
SteveS - 16 May 2007 11:01 GMT
Oops, that should read "Subs".
Signature

Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

> I tend to use Subx unless I need to return a value. But I don't think it
> matters too much.
[quoted text clipped - 258 lines]
> > >> > you
> > >> > respond and I don't acknowledge it right away.
BruceM - 16 May 2007 12:53 GMT
Now there's a clear distinction between the use of subs and functions.  I
use functions in a standard code module for custom navigation buttons that I
lug around from database to database.  For instance:

Public Function TheFirstRec()

   DoCmd.GoToRecord , , acFirst

End Function

There is error handling in there too.  In the Click event property for the
navigation button:
=TheFirstRec()

I only need error handling in the function.  In the case of a command button
that is used on several forms, it saves some coding.  However, if I need to
do something else in the Click event, I call the function from within the
sub, along with the code for the "something else".  Somewhere along the line
I seem to have turned this into a sort of general approach.

Anyhow, thanks for the suggestions and comments.

> Oops, that should read "Subs".
>
[quoted text clipped - 302 lines]
>> > >> > you
>> > >> > respond and I don't acknowledge it right away.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.