Restricting entry on tab pages

  • Thread starter Thread starter BruceM
  • Start date Start date
B

BruceM

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.
 
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.
 
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
 
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.
 
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. :)
 
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.
 
Back
Top