MS Access Forum / Forms Programming / May 2007
Restricting entry on tab pages
|
|
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.
|
|
|