Solved Managing DB Users (1 Viewer)

smtazulislam

Member
Local time
Today, 06:47
Joined
Mar 27, 2020
Messages
806
Hello,
I've developed an HRM database where I aim to establish a comprehensive User Control and Permission System, delineating who can edit and who can only view data.

Currently, I've implemented separate forms for each department, incorporating additional enable/disable functionality from a main form with specific conditions.

Managing this setup has become cumbersome due to the multitude of form and control names, making it challenging to remember each details. I'm seeking advice on a more streamlined approach to control permissions, ideally using TextBoxes to facilitate easier management.
Here is provided still work on with control Dashboard only
Code:
Private Sub Form_Load()
DoCmd.Maximize
''Control User Roles
Dim StrUserRole As Integer
StrUserRole = DLookup("UserRolesID", "Users", "UserName='" & TempVars("UserName") & "'")

Select Case StrUserRole
Case 1 'admin
    Me.lbAddEmployee.Enabled = True
    Me.lbEditEmployee.Enabled = True
    Me.lbSearchEmployee.Enabled = True
    Me.lblViewAll.Enabled = True
    Me.lbHireEmployee.Enabled = True
    Me.lbTransferEmployee.Enabled = True
    Me.lbUpdatePassport.Enabled = True
    Me.lbStopSalary.Enabled = True
    Me.lbAdminPanel.Enabled = True
Case 2 'HR Supervisor
    Me.lbAddEmployee.Enabled = True
    Me.lbEditEmployee.Enabled = False
    Me.lbSearchEmployee.Enabled = True
    Me.lblViewAll.Enabled = True
    Me.lbHireEmployee.Enabled = True
    Me.lbTransferEmployee.Enabled = True
    Me.lbUpdatePassport.Enabled = False
    Me.lbStopSalary.Enabled = True
Case 3 'payroll
    Me.lbAddEmployee.Enabled = False
    Me.lbEditEmployee.Enabled = False
    Me.lbSearchEmployee.Enabled = True
    Me.lblViewAll.Enabled = True
    Me.lbHireEmployee.Enabled = False
    Me.lbTransferEmployee.Enabled = False
    Me.lbUpdatePassport.Enabled = False
    Me.lbStopSalary.Enabled = True
Case 4 'Guest
    Me.lbAddEmployee.Enabled = False
    Me.lbEditEmployee.Enabled = False
    Me.lbSearchEmployee.Enabled = True
    Me.lblViewAll.Enabled = True
    Me.lbHireEmployee.Enabled = False
    Me.lbTransferEmployee.Enabled = False
    Me.lbUpdatePassport.Enabled = False
    Me.lbStopSalary.Enabled = True
Case 5 'HR Admin
    Me.lbAddEmployee.Enabled = False
    Me.lbEditEmployee.Enabled = False
    Me.lbSearchEmployee.Enabled = True
    Me.lblViewAll.Enabled = True
    Me.lbHireEmployee.Enabled = False
    Me.lbTransferEmployee.Enabled = False
    Me.lbUpdatePassport.Enabled = False
    Me.lbStopSalary.Enabled = True
    Me.lbAddVacation.Enabled = False
    Me.lblFinalExit.Enabled = False
    Me.lbReports.Enabled = False
    Me.lbUtilityPanel.Enabled = False
Case 6 'Maint Admin
    Me.lbAddEmployee.Enabled = False
    Me.lbEditEmployee.Enabled = False
    Me.lbSearchEmployee.Enabled = True
    Me.lblViewAll.Enabled = True
    Me.lbHireEmployee.Enabled = False
    Me.lbTransferEmployee.Enabled = False
    Me.lbUpdatePassport.Enabled = False
Case 7 'Project Admin
    Me.lbAddEmployee.Enabled = False
    Me.lbEditEmployee.Enabled = False
    Me.lbSearchEmployee.Enabled = True
    Me.lblViewAll.Enabled = True
    Me.lbHireEmployee.Enabled = False
    Me.lbTransferEmployee.Enabled = False
Case 7 'Project Amdin
    Me.lbAddEmployee.Enabled = False
    Me.lbEditEmployee.Enabled = False
    Me.lbSearchEmployee.Enabled = True
    Me.lblViewAll.Enabled = True
    Me.lbHireEmployee.Enabled = False
    Me.lbTransferEmployee.Enabled = False
    Me.lbUpdatePassport.Enabled = False
    Me.lbStopSalary.Enabled = True
Case 8 'Finance Admin
    Me.lbAddEmployee.Enabled = False
    Me.lbEditEmployee.Enabled = False
    Me.lbSearchEmployee.Enabled = True
    Me.lblViewAll.Enabled = True
    Me.lbHireEmployee.Enabled = False
    Me.lbTransferEmployee.Enabled = False
    Me.lbUpdatePassport.Enabled = False
    Me.lbStopSalary.Enabled = True  
Case 9 'Maint SuperVisor
    Me.lbAddEmployee.Enabled = False
    Me.lbEditEmployee.Enabled = True
    Me.lbSearchEmployee.Enabled = True
    Me.lblViewAll.Enabled = True
    Me.lbHireEmployee.Enabled = False
    Me.lbTransferEmployee.Enabled = True
    Me.CmdOrderInbox.Enabled = False
    Me.lbUpdatePassport.Enabled = False
    Me.lbStopSalary.Enabled = True
Case 10 'Finance Supervisor
    Me.lbAddEmployee.Enabled = False
    Me.lbEditEmployee.Enabled = False
    Me.lbSearchEmployee.Enabled = True
    Me.lblViewAll.Enabled = True
    Me.lbHireEmployee.Enabled = False
    Me.lbTransferEmployee.Enabled = False
    Me.lbUpdatePassport.Enabled = False
    Me.lbStopSalary.Enabled = True  
Case 11 'Project Supervisor
    Me.lbAddEmployee.Enabled = False
    Me.lbEditEmployee.Enabled = False
    Me.lbSearchEmployee.Enabled = True
    Me.lblViewAll.Enabled = True
    Me.lbHireEmployee.Enabled = False
    Me.lbTransferEmployee.Enabled = False
    Me.lbUpdatePassport.Enabled = False
    Me.lbStopSalary.Enabled = True
End Select
End Sub
Can I establish a system where permissions for a form (like frmAddEmployee) can Edit/View/Delete/Addition give permission based on the UserRoles. Moreover, can I extend this control to individual textboxes within the form.

I appreciate any guidance or recommendations and simplicity of my user control and permission system.
Thank you
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 22:47
Joined
Feb 28, 2001
Messages
27,488
Can I establish a system where permissions for a form (like frmAddEmployee) can Edit/View/Delete/Addition give permission based on the UserRoles. Moreover, can I extend this control to individual textboxes within the form.

I cannot show you code because it was technically not my property. The project belonged to the U.S. Navy. However, I had a system that did more or less the following things:

1. I had a table of users based on their domain login name. One of the fields in this table was a "role" that went from view-only to limited edits to full-on edits to create-new-records. Nobody had DELETE though the "REMOVE" button would mark something as "obsolete" and appear to delete it. The various role's actual names don't matter here.

When each user launched, the opening menu that was my switchboard would look up the user info and immediately copy some values to public variables in a dedicated "security" general module. Later in the project I added a child table to the user table, listing permissions to grant more selective abilities. The start-up form would load these permissions (which were just Boolean yes/no flags) to public variables as well. Thereafter, any form that was interested could see the flags at any time.

Users could also belong to one or more groups within the department and a very small front-end table listed the groups they were in. Again, easily referenced from any interested form. Before anyone complains, the small FE table was replaced often enough by version updates that bloating was at worst a very minor issue.

In this system, a person could not just immediately open the front-end to act as a guest. They would be blocked from doing so by the opening form, which would do an Application.Quit when needed. BUT the opening form would first ask them questions and send me an e-mail regarding a guest access request.

The back-end file ALSO had an opening form that shut down any attempts to directly open it UNLESS you were the person with role "DBAdmin". It also sent mail on any attempts. If you WERE the right role, you could close the BE's opening form and develop whatever you needed. Then just close the BE and it would be back to normal.

2. Each of the other forms had a Form_Open routine that would look at the role and flags and group membership. Within each form, if any degree of selectivity was required, the form could take action to disallow access completely (since Form_Open can be canceled). If the person got through the _Open tests, the _Load event would enable or disable controls based on determining the user's abilities. Group selectivity was used to build a filter on the form and enable that filter.

To facilitate that, I used the "template" approach of creating ONE FORM that had NO SPECIFICS in it, but it had event routines for _Open, _Load, _BeforeUpdate (validation checks), _Unload, _Close, _Current, and a Form_Error routine to act as an untrapped error catcher. Then when I needed to make a new form, I copied the template and customized each copy based on the intent of the form. This saved me anywhere from 40% for complex forms to probably around 60% for simple forms because the template only needed a little tweaking here and there to make it fully functional. This had a secondary effect in that every form had a similar look-and-feel to it. The forms weren't haphazard in design.

3. Because I had locked down the FE fairly tightly, users couldn't directly see anything. Because action forms dynamically built the SQL for any required action queries, I didn't have to worry TOO much about complexity in stored SQL queries. The action button event code was where the work was done and I'll admit it wasn't trivial. But it worked.

Doing this took over a year of total effort and each major improvement in security was implemented in stages. However, it worked quite well at blocking people from doing bad things to the data and yet gave a lot of flexibility.
 

smtazulislam

Member
Local time
Today, 06:47
Joined
Mar 27, 2020
Messages
806
Thank you for your detailed explanation and the thorough discussion about implementing your system. My requirements align closely with your suggestions. Currently, I don't have a system in place for sending a verification code via email, but I plan to explore that option in the future.

In the current workflow, new users can fill out a form and submit a request for access. Once the admin approves the request, the user gains access to the system based on predefined roles. Alternatively, I sometimes provide users with a username and password initially, and they are prompted to change or reset their passwords. Presently, the application has 13 users.

My objective is to create a visually intuitive form that allows administrators to grant or revoke permissions for users through a selected ComboBox or checkboxes. I want to avoid duplicating the same form for different users.

Your guidance on creating a central form for dynamic permission control, incorporating Role-Based Access Control, and ensuring a user-friendly interface for administrators is highly valuable. I aim to enhance security measures, including password policies.

I'm grateful for your insights and look forward to implementing these strategies in my system.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 11:47
Joined
May 7, 2009
Messages
19,249
here is a demo, but I used Custom Ribbon.
the idea is you put all your Form/Report Names on one table (ObjectT)
Code:
ObjID (autonumber)
ObjName (Short string) the name of main form/report
ObjType (short string) "Report" or "Form"
you already has User table, right (add Autonumber in it if doesn't have one).

next create a junction table (UserRightT table)
Code:
UserID (from user table)
ObjID (from ObjectT)
CanAdd (Yes/No)
CanEdit (Yes/No)
CanDelete (Yes/No)
When you create new user (using a Form With UserRightT as subform), you create an Insert query to UserRightT table so
it has the UserID and all ObjID from ObjectT table.
Code:
Dim sql as string
sql = insert into UserRightT (UserID, ObjID) from select " & Me.UserID & ", ObjID from ObjectT;"
currentdb.execute sql
next, requery the subform.
now you can put check if user can Add, Edit and/or delete a record.

next when opening a form you should check the values of UserRightT and put it to the Form's appropriate property:
Code:
Private Sub Form_Open(Cancel As Integer)
Dim lngObjID As Long
lngObjID = Val(Dlookup("ObjID","ObjectT", "ObjName = '" & Me.Name & "'") & "")
With Me
.AllowAdditions = Val(Dlookup("CanAdd","UserRightT", "UserID = " & [CurrentUserID] & " And ObjectID = " & lngObjID) & "")
.AllowEdits = Val(Dlookup("CanEdit","UserRightT", "UserID = " & [CurrentUserID] & " And ObjectID = " & lngObjID) & "")
.AllowDeletions = Val(Dlookup("CanDelete","UserRightT", "UserID = " & [CurrentUserID] & " And ObjectID = " & lngObjID) & "")
End With
End Sub
on the demo (TR2Materials), when you enter user: common, password: common, the User Manager menu is disabled.
you Log In again (Using Login screen button on the ribbon) and enter

user: arnel
password: puzon

now the menu is enabled and you can add/edit users and their rights.
lot's of good stuff (VBA) inside also.
 

Attachments

  • TR2Materials.accdb
    4.8 MB · Views: 94

smtazulislam

Member
Local time
Today, 06:47
Joined
Mar 27, 2020
Messages
806
I'm glad to see it that I found the initial look appealing. I work on deploying my form, and when it's ready, I can reply to share my progress and ask for further any assistance If I need. Please response. Thank you very much for your kind words.
 

smtazulislam

Member
Local time
Today, 06:47
Joined
Mar 27, 2020
Messages
806
Your form currently allows users to edit, delete, or add records comprehensively. However, if I desire certain users to have the ability to edit records while specifically restricting access to particular fields, how can I achieve this ? Do you have any advice for it ?
I have this app
Form Main - 89 , Duplicate 15 (restricting some fileds)
Report Main - 45 , Duplicate -13
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 11:47
Joined
May 7, 2009
Messages
19,249
i encourage you to "apply" this to all your forms.
aside from the "rights/access" for each form, you can also do it per form.control.
on ObjectT table, you can add to ObjName, the NameOfForm dot NameOfControl, example: Form1.txFirstName
then create a Sub that will walk through all the controls of that form and set the control's Lock property to True.

Code:
Public Function fnEnforceFormRights(ByRef frm As Form)
    Dim lngObjID As Long
    Dim sRights As String
    Dim sName As String, sCtlName As String
    Dim var, ctl As Control
    With frm
        ' form rights
        sName = .Name
        lngObjID = Val(DLookup("ObjID", "ObjectT", "ObjName = '" & sName & "'") & "")
        sRights = DLookup("CanAdd & '/' & CanEdit & '/' & CanDelete", "UserRightT", "UserID = " & [CurrentuserID] & " And ObjID = " & lngObjID)
        
        var = Split(sRights, "/")
        
        .AllowAdditions = Val(var(0) & "")
        .AllowEdits = Val(var(1) & "")
        .AllowDeletions = Val(var(2) & "")
        
        ' loop through each controls
        For Each ctl In .Controls
            sCtlName = ctl.Name
            'check if control can be edited
            lngObjID = Val(DLookup("ObjID", "ObjectT", "ObjName = '" & sName & "." & sCtlName & "'") & "")
            
            ' the the control is found in the table
            ' set it's Locked Property
            '
            If lngObjID <> 0 Then
                ctl.Locked = Not (DLookup("CanEdit", "UserRightT", "UserID = " & [CurrentuserID] & " And ObjID = " & lngObjID))
            End If
            
        Next ctl
    
    End With
End Function

on the Open event of Each Form/subform:
Code:
Private Form_Open(Cancel As Integer)
    Call fnEnforceFormRights(Me)
End Sub
 

smtazulislam

Member
Local time
Today, 06:47
Joined
Mar 27, 2020
Messages
806
I appreciate your gratitude for the time and effort in consolidating the code.
I am optimistic that it will function seamlessly. Once deployed in my system, I will provide you with feedback, how its performance with me.
Thank you once again.
 

smtazulislam

Member
Local time
Today, 06:47
Joined
Mar 27, 2020
Messages
806
I appreciate your understanding regarding the delay in my response.

The issues your encourage, I encountered while applying and running the solution, it's possible that there were mistakes due to changes in table field names, form textboxes names.

Could you please reviews and provide me with the details of the changes I made, along with an example into form & text box? This information will help me to understand the accurate needed.
 

Attachments

  • UserManagement v1.0.accdb
    1.6 MB · Views: 62

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 22:47
Joined
Feb 28, 2001
Messages
27,488
specifically restricting access to particular fields, how can I achieve this ? Do you have any advice for it ?

How you do this will depend on what method you choose to identify that "this particular user cannot see fields x, y, and z" but WHEN you do it is in the form's _OnCurrent event routine. What I did was a SELECT CASE statement with the cases representing certain roles for which the given user would or would not have access. Within any case where a field-selective restriction was involved, I would set .Locked = TRUE for that field. As it happens, I also changed the visible attributes of the field. I had a complex color scheme but that was because it was a complex environment. If I remember correctly, "clean, ordinary" fields were blue on white with a thin black border. "Dirty, ordinary" fields were red with a thin red border and a custom pale-pink background (enforced at _LostFocus events). "Clean, locked" fields were black on a custom background color, pale yellow - and I also set TabStop property to FALSE so you wouldn't tab to it. The color scheme was arbitrary.

Sounds like a lot, but when I used my template scheme, it was easy to have the _Current and _LostFocus infrastructure already in the form waiting to be customized.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 11:47
Joined
May 7, 2009
Messages
19,249
here test this.
 

Attachments

  • UserManagement v1.0.accdb
    3.2 MB · Views: 70

smtazulislam

Member
Local time
Today, 06:47
Joined
Mar 27, 2020
Messages
806
Thank you very much for your help. I appreciate your assistance and the time you dedicated to this thread.

Regarding the issue with the form, if it's enabled but does not allow access to Add, Edit, or Delete functions, and users encounter 'error 94' when trying to access these buttons, the best approach would be to implement conditions within the button or function modules, and this ensures consistent functionality across all form/reports. The attached picture for your clarification. I hopefully, system replace this error sending a message to user based on access permission like "You cant access to this form/report is restricted for you. Please contact your administrator for further assistance".

Another Addtional questions, If I grant permission to access the form, am I able to specifics restrict certain textboxes, Comboboxes, Lists by disabling or hiding them ?
 

Attachments

  • Screenshot 2024-02-07 120940.png
    Screenshot 2024-02-07 120940.png
    38.4 KB · Views: 46

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 11:47
Joined
May 7, 2009
Messages
19,249
if you "Enable" the form, you should also check the AllowAddition, edits, etc. if you want to grant them right to add/edit records, etc.
on your second question, see the tblObjectNames (ObjectName) field, i use FormName + underscore + controlname, and the type of control.
if you specify which one needs to be readonly, etc.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 22:47
Joined
Feb 28, 2001
Messages
27,488
If I grant permission to access the form, am I able to specifics restrict certain textboxes, Comboboxes, Lists by disabling or hiding them ?

Definitely, yes. See my comments in #11, which describes one way of doing exactly what you asked. And rather than disabling something, you have the option of setting .Locked=TRUE so that it remains visible but untouchable, OR you can make it .Locked=TRUE, .Visible=FALSE and truly hide it and protect it. The biggest point is to remember to reset those properties back to normal and thus enable all fields at the start of each Form_Current routine before you starting locking or hiding things in the same routine.
 

smtazulislam

Member
Local time
Today, 06:47
Joined
Mar 27, 2020
Messages
806
if you "Enable" the form, you should also check the AllowAddition, edits, etc. if you want to grant them right to add/edit records, etc.
on your second question, see the tblObjectNames (ObjectName) field, i use FormName + underscore + controlname, and the type of control.
if you specify which one needs to be readonly, etc.
Thank you very much for your time and assistance on this thread.

It's reassuring to know that the functionality with textboxes, buttons, and comboboxes is working perfectly. Now, I'll proceed to test it with sub-forms and list-boxes to ensure comprehensive functionality.
appeciate.
 

smtazulislam

Member
Local time
Today, 06:47
Joined
Mar 27, 2020
Messages
806
Definitely, yes. See my comments in #11, which describes one way of doing exactly what you asked. And rather than disabling something, you have the option of setting .Locked=TRUE so that it remains visible but untouchable, OR you can make it .Locked=TRUE, .Visible=FALSE and truly hide it and protect it. The biggest point is to remember to reset those properties back to normal and thus enable all fields at the start of each Form_Current routine before you starting locking or hiding things in the same routine.
Thank you for sharing this helpful approach.
Setting .Locked=TRUE is indeed a great way to prevent users from editing while keeping the field visible. using .Locked=TRUE and .Visible=FALSE can completely hide and protect the field. I had uesd it in function that provide posts #12.
Code:
Select Case oType
    Case "commandbutton"
        If DLookup("IsEnabled", "tblUserAccess", "ObjID =" & lngObjID & " And UserID = " & TempVars("CurrentUser")) = False Then
            ctl.Enabled = False
        Else
            ctl.Enabled = True
        End If
    Case "textbox"
        If DLookup("IsHide", "tblUserAccess", "ObjID =" & lngObjID & " And UserID = " & TempVars("CurrentUser")) = True Then
            ctl.Visible = True
        Else
            ctl.Visible = False
        End If
        ctl.Locked = Not (DLookup("IsHide", "tblUserAccess", "ObjID =" & lngObjID & " And UserID = " & TempVars("CurrentUser")))

        If DLookup("IsEnabled", "tblUserAccess", "ObjID =" & lngObjID & " And UserID = " & TempVars("CurrentUser")) = False Then
            ctl.Enabled = False
        Else
            ctl.Enabled = True
        End If
End Select
 

smtazulislam

Member
Local time
Today, 06:47
Joined
Mar 27, 2020
Messages
806
ObjNameObjType
frmEmployee​
Form​
frmEmployee_GeneralPage​
tab​
frmEmployee_EmployeeIdentityPage​
tab​
frmEmployee_BankInsPage​
tab​
frmEmployee_HealthInsurancePage​
tab​
frmEmployee_NotesPage​
tab​
frmEmployee_txtEmployeeName​
textbox​
frmEmployee_txtEmployeeNameAr​
textbox​
frmEmployee_DOB​
textbox​
frmEmployee_cboReligion​
Combobox​
frmEmployee_cboDepartment​
Combobox​
frmEmployee_FrmGender​
Frame​
frmEmployee_FrmMarital​
Frame​
frmEmployee_cboCountryID​
Combobox​
from here another FORM
frmAdminHRDashboard_lstNationality​
listbox​
frmAdminHRDashboard_lstVacation​
listbox​
frmAdminHRDashboard_lstIqamaExpire​
listbox​
frmAdminHRDashboard_lstPassportExpire​
listbox​
I require additional assistance in completing this thread.

I have a form named "frmEmployee" that incorporates tabs, each containing various controls such as textboxes, comboboxes, frames, and lists.
My inquiry pertains to how I can reference and interact with these controls within the code.

Further a questions, I written the table is correct ? and if I adding like that can work ?
Case "tab"
or
Case "Frame"
or
Case "List"

Code:
Select Case oType
    Case "commandbutton"
        If DLookup("IsEnabled", "tblUserAccess", "ObjID =" & lngObjID & " And UserID = " & TempVars("CurrentUser")) = False Then
            ctl.Enabled = False
        Else
            ctl.Enabled = True
        End If
    Case "textbox"
        If DLookup("IsHide", "tblUserAccess", "ObjID =" & lngObjID & " And UserID = " & TempVars("CurrentUser")) = True Then
            ctl.Visible = True
        Else
            ctl.Visible = False
        End If
        ctl.Locked = Not (DLookup("IsHide", "tblUserAccess", "ObjID =" & lngObjID & " And UserID = " & TempVars("CurrentUser")))

        If DLookup("IsEnabled", "tblUserAccess", "ObjID =" & lngObjID & " And UserID = " & TempVars("CurrentUser")) = False Then
            ctl.Enabled = False
        Else
            ctl.Enabled = True
        End If
End Select
Your guidance in this matter would be greatly appreciated. Thank you
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 22:47
Joined
Feb 28, 2001
Messages
27,488
Here, you have a lot of ways to choose and I can't tell you which one is right for you. HOWEVER, I can tell you what worked for me.

I had some general subroutines that were called from another sub. Just as there is a concept of "normalization" for databases, there is the concept of "modularization" or perhaps "isolation" where you think about the actions you wanted to perform in preparing a form for use, and you write a bunch of small, simple-minded routines to focus on the isolated steps as elementary actions despite them not actually being elementary. I.e. I built my own "black box" routines to handle things that I did a lot, so as to minimize repeated expression of detailed code. My rule eventually developed that if I was writing code and recognized that I had used it at least twice before, that rather than writing it a third time, I stopped, wrote the subroutine for it, and then went back and changed the two previous uses to call the sub also.

One function was FindUserRole( userid as LONG ) as LONG and it returned a user code based on checking the user's group memberships and special privileges. User roles might be guest, projmgr (project manager), sysadm (system administrator), deptmgr (department manager), DBA (me and my assistant, the database administrators) and a few others I don't recall offhand. But the idea was that each of those persons COULD have different abilities depending on the form in question. So I isolated the logic to decide which role each person had when they logged in.

One sub was SetCtlState( ctlX As Form.Control, statecode as Long ) and the Form_Current routine called it repeatedly, as did the control_LostFocus routines. The idea was you passed the specific control you wanted to set up and you passed in a state code to show that it was ReadWrite, ReadOnly, Hidden, Dirty, etc. Inside, it had a case-ladder like yours that chose what to do based on the type of control.

Some shortcut methods exist that you can use to make the code more responsive and yet more compact. There is a text-oriented property on all controls called .Tag, and you can put multiple things there. I had some three-letter code strings that these loops could test. More than one could apply depending on form purpose. If I wanted the control to be subject to user regulations and user-group regulations I would edit the tag to include ",USR,UGR," - and note that the leading and trailing commas were important, because I could use InStr to look for ",UGR," rather than try to parse each tag. That way, I knew if special rules applied to a particular control when I was setting its state.

Part of the code involved a call to set up the colors associated with the special state, because I colored to foreground, background, and border as part of the state. Enablement or locking or visibility were also possible depending on the input state (e.g. Hidden state was locked andinvisible, color didn't matter.) Usually, I looked at the control's properties (to test whether it was dirty, check the .Tag property, etc.) first and then used the case-ladder based on control type to actually set the color and lock/visible properties.

One sub was ResetForm( frmX as Access.Form ) and it reset each form to a preset "minimal function" state. It used a "FOR EACH ctl IN frmX.Controls" type of loop to step through the controls. It also had a case-ladder based on the control type such as you showed.

The Form_Current routine had different styles depending on form complexity and purity of purpose. For instance, if the form was a user account maintenance form, it had nothing to do with the servers or the patches to be applied, so it could be simple. The patch-update management form was complex because it dealt with servers, persons applying patches, and the patches themselves - a three-way interaction.

Since I didn't own the code and no longer have access to it, I cannot tell you too many details. But I can tell you that the kind of code you showed is NOT unfamiliar to me. I used such coding concepts quite frequently and successfully.
 

smtazulislam

Member
Local time
Today, 06:47
Joined
Mar 27, 2020
Messages
806
Thank you very much for providing additional details. Your explanation is quite clear.

I already applied the suggested code to approximately 12 forms successfully. However, now I encountered a form where unsure how to implement the code inside the "case" statement, specifically when dealing with forms that contain a TabControl, which further contains lists, comboboxes, and subforms.

To assist further with this issue, could you please provide more specific details about the structure code I have of the form in the question? To understanding how the TabControl is organized and how the lists, comboboxes, and subforms are nested within it will help in providing a more tailored solution.

To provide further assistance, it would be helpful to understand the structure of the form in question. Specifically, could you please provide details regarding mine additional question about the two columns is what should I have to write for TabControl is organized and how the lists, comboboxes, subform is work. This information will allow for a more targeted solution to implement the code inside the "case" statement effectively.
ObjNameObjType
??????
Please providing a more accurate response...
 

Users who are viewing this thread

Top Bottom